SQL UNION Operator | Combine Separate SQL Statements

The SQL UNION operator combines the result of two or more SQL SELECT queries and returns the combined result of the queries as a union. In this article, we will discuss the SQL Union Clause. Also, we will discuss a few examples of using it.

Syntax

SELECT column1, column2 … columnN FROM table1

UNION

SELECT column1, column2 … columnN FROM table2;

  • column1, column2 … columnN: The list of columns you want to retrieve. Also, all the columns in the query must be in the same order and the same data type in all the SELECT Queries.
  • UNION: The keyword UNION specifies the second query which you want to combine with this query. Also, you can make a UNION of multiple tables.

The UNION Clause selects only distinct values from the table. In order to select the duplicate values also, use the UNION ALL Operator.

Demo Database

Refer to the following tables for the examples in this article:

mysql> select * from users;
+---------+----------+------------------+
| user_id | username | email_id         |
+---------+----------+------------------+
|       1 | john     | [email protected]   |
|       2 | emily    | [email protected]  |
|       3 | monica   | [email protected] |
|       4 | clark    | [email protected]  |
|       5 | ross     | [email protected]   |
+---------+----------+------------------+

mysql> select * from orders;
+----------+---------+-------------+------------+
| order_id | user_id | order_total | order_date |
+----------+---------+-------------+------------+
|        1 |       1 |         500 | 2019-07-01 |
|        2 |       1 |         400 | 2019-06-01 |
|        3 |       4 |        1000 | 2018-07-15 |
|        5 |       3 |         600 | 2019-06-05 |
|        6 |       4 |         100 | 2019-08-01 |
+----------+---------+-------------+------------+

SQL UNION Example

For instance, you want to find all the user_ids in both the tables. You can use the UNION operator and find the combined result of both the tables.

SELECT user_id FROM users 
UNION 
SELECT user_id FROM orders;
+---------+
| user_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
+---------+

The query prints the distinct user_ids from both the tables.

UNION ALL Example

In order to fetch all the rows including the duplicate, use the UNION ALL Operator.

SELECT user_id FROM users 
UNION ALL 
SELECT user_id FROM orders;
+---------+
| user_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       1 |
|       1 |
|       4 |
|       3 |
|       4 |
+---------+

The above query fetches all the values from both the tables.

SQL UNION VS UNION ALL

The UNION Operator selects only the distinct values from both the tables. On the other hand, the UNION ALL Operator fetches all the values including the duplicates.

SQL UNION WHERE

Similarly, you can use a WHERE Condition in both the queries independently.

SELECT user_id FROM users WHERE user_id < 2 
UNION 
SELECT user_id FROM orders WHERE user_id < 2;
+---------+
| user_id |
+---------+
|       1 |
+---------+

The above query fetches all the user_id with the value less than 2 and then produces the combined result.

SQL UNION ORDER BY

Also, you can order the final result set as a whole using the ORDER BY Clause.

SELECT user_id FROM users 
UNION 
SELECT user_id FROM orders 
ORDER by user_id DESC;
+---------+
| user_id |
+---------+
|       5 |
|       4 |
|       3 |
|       2 |
|       1 |
+---------+

The above UNION Query combines the results from both the tables and then orders them in descending order of user_id.

SQL UNION Count

It is not possible to determine the count of the values returned by a UNION Query directly. You should use the UNION query as a sub-query for another Count Query. For example, consider the example below:

SELECT count(user_id) FROM 
(
    SELECT user_id FROM users 
    UNION 
    SELECT user_id FROM orders
) AS t1;
+----------------+
| count(user_id) |
+----------------+
|              5 |
+----------------+

The inner query first fetches the combined list of distinct user_ids from both the tables. The derived table is then used as a data source for the outer query which calculates the count of user_ids from the derived table.

Conclusion

In conclusion, we discussed the SQL UNION Operator. You can read more about it on the Official Microsoft Documentation. Additionally, you can learn more about Basic SQL Queries on Concatly.

Spread the Knowledge

Leave a Reply

Your email address will not be published. Required fields are marked *