SQL Basics | DML: Data Manipulation Language | UNION, UNION ALL
The UNION operator is used to combine the result sets of two or more SELECT statements.
UNION combines the results of two SQL statements. In that sense, UNION is somewhat similar to JOIN because both commands can retrieve data from multiple tables. However, UNION has a limitation: the fields produced by the two SQL statements must use the same data types. Also, when UNION is used, duplicate data is not output, similar to SELECT DISTINCT.
Conditions for UNION
- Each SELECT statement in a UNION must have the same number of columns.
- The columns must have similar data types.
- The columns in each SELECT statement must also be in the same order.
UNION ALL is also used to combine the results of two SQL statements. The difference from UNION is that UNION ALL displays all data that matches the condition even when data values are duplicated.
UNION, UNION ALL syntax
UNION is used as follows.
UNION syntax
[SQL statement 1]
UNION
[SQL statement 2];
Two SQL statements are entered here.
UNION ALL is used as follows.
UNION ALL syntax
[SQL statement 1]
UNION ALL
[SQL statement 2];
Except for adding ALL, it is the same as UNION.
UNION example
Suppose you have the following two tables.
store_information table
| store_name | sales | txn_date |
|---|---|---|
| Los Angeles | 1500 | Jan-05-2018 |
| San Diego | 250 | Jan-07-2018 |
| Los Angeles | 300 | Jan-08-2018 |
| Boston | 700 | Jan-08-2018 |
internet_sales table
| txn_date | sales |
|---|---|
| Jan-07-2018 | 250 |
| Jan-10-2018 | 535 |
| Jan-11-2018 | 320 |
| Jan-12-2018 | 750 |
To check all sales dates, use the following SQL statement.
SELECT txn_date FROM store_information
UNION
SELECT txn_date FROM internet_sales;
The execution result is as follows.
| txn_date |
|---|
| Jan-05-2018 |
| Jan-07-2018 |
| Jan-08-2018 |
| Jan-10-2018 |
| Jan-11-2018 |
| Jan-12-2018 |
One thing to note is that using SELECT DISTINCT txn_date in the SQL statement, or in both clauses where applicable, produces the same result.
UNION ALL example
Now retrieve the dates that have store sales and internet sales. In this case, use the following SQL statement.
SELECT txn_date FROM store_information
UNION ALL
SELECT txn_date FROM internet_sales;
The result is as follows.
| txn_date |
|---|
| Jan-05-2018 |
| Jan-07-2018 |
| Jan-08-2018 |
| Jan-08-2018 |
| Jan-07-2018 |
| Jan-10-2018 |
| Jan-11-2018 |
| Jan-12-2018 |