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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
Los Angeles300Jan-08-1999
Boston700Jan-08-1999

Table Internet_Sales

Txn_DateSales
Jan-07-1999250
Jan-10-1999535
Jan-11-1999320
Jan-12-1999750

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, the UNION query returns each value only once.

Next: SQL Inline View




Copyright © 2014   1keydata.com   All Rights Reserved.     Privacy Policy     About   Contact