How to Use the UNION Clause in SQL

The UNION clause in SQL is a keyword used to combine the results of two or more SELECT statements into a single result set. It allows you to retrieve data from multiple tables or views and combine them into a single result set, which can be more convenient than running multiple separate queries.

How the UNION Clause in SQL Works

Here’s how it works:

1. The UNION keyword is used to combine the results of two or more SELECT statements.
2. The number and order of the columns in the SELECT statements must be the same.
3. The data types of the columns in the SELECT statements must be compatible.
4. Each SELECT statement must have the same number of expressions in the SELECT list.
5. UNION combines the results of the SELECT statements and removes any duplicates.

Here’s an example of how to use UNION to combine the results of two SELECT statements:

SELECT column1, column2, column3 FROM table1
UNION
SELECT column1, column2, column3 FROM table2

This will retrieve all rows from table1 and table2 where the values in column1, column2, and column3 are unique across both tables.

Remember that the UNION clause will apply the same logic as the DISTINCT keyword by default, returning unique records across both of your queries. Let’s say that both our customers and users tables have the following information in them.

If we then perform the following query on these tables, we will see only one record for each entry, for a total of two records in the result set:

SELECT first_name, last_name, email_address
FROM customers
UNION
SELECT first_name, last_name, email_address
FROM users;

If we were to add another column that will have a unique value in it, then we would see one record for each unique combination. Often, this will mean including a primary key field. But you can also include a static field in each of your SELECT statements. Here is an example:

SELECT first_name, last_name, email_address, 'customers' AS source_table
FROM customers
UNION
SELECT first_name, last_name, email_address, 'users' AS source_table
FROM users;

Note that we’ve added the same column named source_table to both of these SELECT statements. In this case, you will see four records in the result set. The first two will have a source_table value of “customers” and the second two will have a source_table value of “users”. This is because the static source_table value we have added introduces uniqueness across both SELECT statements.

Using UNION ALL in Queries

You can also use the UNION ALL keyword to include all rows, including duplicates, in the result set.

SELECT column1, column2, column3 FROM table1
UNION ALL
SELECT column1, column2, column3 FROM table2

Adding “UNION ALL” essentially negates the unique requirement for the query without having to introduce a field for uniqueness.

SQL UNION is a powerful tool that allows you to combine data from multiple tables or views and retrieve it in a single result set. It’s an essential part of any SQL developer’s toolkit and is commonly used to extract data from multiple sources for analysis and reporting.