SQL > Advanced SQL > Union All

The purpose of the SQL UNION ALL command is to combine the results of two queries together without removing any duplicates.

Syntax

The syntax for UNION ALL is as follows:

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

The columns selected in [SQL Statement 1] and [SQL Statement 2] need to be of the same data type for UNION ALL to work.

Examples

We use the following tables for our example.

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

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, 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 returns 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 © 2016   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact