SQL provides us with the UNION operator that allows us to combine the result sets of two or more SELECT statements into a single result set. There are three main types of UNION operators in SQL: UNION, UNION ALL, and UNION DISTINCT.
This tutorial explores these three types of UNIONS and provides real-world and practical examples of working with them.
NOTE: In this example, we will use the sample Sakila database for demonstration purposes. Ensure that you have it installed before executing these commands. You can check out our tutorial on that to learn more.
SQL UNION Operator
As mentioned, the UNION operator allows us to combine the result set of two or more select statements and remove the duplicate values. The syntax of the UNION operator is as follows:
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;
In the previous example syntax, we use the SELECT statements to retrieve the data from the specified tables. The UNION operator then combines the two result sets into one set. Let us take an example on how to do this using the actor table that is defined in the Sakila database.
Consider the following example query that retrieves the first name and the last name of the actors from the actor table and the customer’s table:
FROM actor
UNION
SELECT first_name, last_name
FROM customer; SELECT first_name, last_name
FROM actor
UNION
SELECT first_name, last_name
FROM customer;
The previous query fecthes the first and last names from both the actor and customer tables and returns the values as a single result.
An example output is as follows:
SQL UNION ALL Operator
Unlike the UNION operator which removes the duplicate values from the result set, the UNION operator returns all the tables’ rows including the duplicates.
The syntax is as follows:
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;
The following example selects the first and last name from the actor and customer tables in the Sakila database:
FROM actor
UNION ALL
SELECT first_name, last_name
FROM customer;
An example output is as follows:
SQL UNION DISTINCT Operator
The other type of union operator is the UNION DISTINCT. This operator is simply a duplicate of the UNION operator which performs a similar action.
The syntax is as follows:
FROM table1
UNION DISTINCT
SELECT column1, column2, ...
FROM table2;
The SELECT statements retrieve the data from the specified tables, and the UNION DISTINCT operator combines the result sets into a single result set that includes the unique rows.
In the example of the Sakila database, we can run the following command:
FROM actor
UNION DISTINCT
SELECT first_name, last_name
FROM customer;
This should return a similar result as the UNION operator.
Conclusion
We learned how to work with the various types of UNIONS in SQL. The UNION operator combines the result set of two or more select statements and removes the duplicate records. The UNION ALL performs a similar action but includes any duplicate rows. Finally, the UNION DISTINCT is identical to a native UNION operator.
