SQL UNION ALL

SQL > Advanced SQL > Union All

The purpose of the SQL UNION ALL command is to combine the results of two queries together. The syntax for UNION ALL is as follows:

[SQL Statement 1]
UNION ALL
[SQL Statement 2];

Assume that we have the following two tables,

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
Los Angeles	300	Jan-08-1999
Boston	700	Jan-08-1999

Table Internet_Sales

Txn_Date	Sales
Jan-07-1999	250
Jan-10-1999	535
Jan-11-1999	320
Jan-12-1999	750

and we want to find out all the dates where there is a sales transaction at a store as well as all the dates where there is a sale over the internet. To do so, we use the following SQL statement:

SELECT Txn_Date FROM Store_Information
UNION ALL
SELECT Txn_Date FROM Internet_Sales;

Result:

Txn_Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-08-1999

Jan-07-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

UNION vs UNION ALL

UNION and UNION ALL both combine the results of two SQL queries. The difference is that, while UNION only selects distinct values, UNION ALL selects all values. If we use UNION in the above example,

SELECT Txn_Date FROM Store_Information
UNION
SELECT Txn_Date FROM Internet_Sales;

the result becomes,

Txn_Date

Jan-05-1999

Jan-07-1999

Jan-08-1999

Jan-10-1999

Jan-11-1999

Jan-12-1999

Notice that while the UNION ALL query returns "Jan-07-1999" and "Jan-08-1999" twice, theUNION query returns each value only once.