SQL UNION

Summary: in this tutorial, you’ll learn how to use the SQL UNION operator to combine the result sets of two SELECT statements into a single result set.

Introduction to SQL UNION operator #

The UNION operator allows you to combine the result sets of two SELECT statements into a single result set.

Here’s the syntax of the UNION operator:

SELECT 
    column1, column2
FROM
    table1 
UNION
SELECT 
    column3, column4
FROM
    table2;Code language: SQL (Structured Query Language) (sql)

In this syntax, you use the UNION operator to connect the first query with the second one.

Here are some rules for using the UNION operator:

• Same number of columns: The SELECT statements must have the same number of columns.
• Compatible data types: The corresponding columns in each SELECT statement must have compatible data types.
• Column names: The column names of the second query will determine the column names of the final result set.
• Execution order: The UNION operator executes the SELECT statements independently and combine the result sets.
• Sorting result set: Use the ORDER BY clause in the second query to sort the rows in the final result set.

The UNION operator removes duplicate rows from the combined result set, to retain the duplicate rows, you can use the UNION ALL operator:

SELECT 
    column1, column2
FROM
    table1 
UNION ALL
SELECT 
    column3, column4
FROM
    table2;Code language: SQL (Structured Query Language) (sql)

SQL UNION operator example #

Suppose we have two tables A and B:

Table A:

Table B:

The following query uses the UNION operator to combine the result sets of the queries that retrieve data from the tables A and B:

SELECT
  id
FROM
  a
UNION
SELECT
  id
FROM
  b
ORDER BY
  id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 id
----
  1
  2
  3Code language: plaintext (plaintext)

SQL UNION ALL operator example #

The following query uses the UNION ALL operator to combine result sets of the queries that retrieve data from the tables A and B:

SELECT
  id
FROM
  a
UNION ALL
SELECT
  id
FROM
  b
ORDER BY
  id;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 id
----
  1
  2
  2
  3Code language: plaintext (plaintext)

The result set includes the duplicate row (2).

The following picture illustrates how the UNION ALL operator works:

Practical UNION operator examples #

Here are the employees and dependents tables from the HR sample database:

The following query uses the UNION operator to create a list that includes the first and last names of employees and their dependents:

SELECT
  first_name,
  last_name
FROM
  employees
UNION
SELECT
  first_name,
  last_name
FROM
  dependents
ORDER BY
  first_name,
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  last_name
-------------+-------------
 Adam        | Fripp
 Alec        | Partners
 Alexander   | Hunold
 Alexander   | Khoo
 Bette       | De Haan
 Bob         | Hartstein
 Britney     | Everett
...Code language: plaintext (plaintext)

The final result set includes the names of both employees and dependents. It would be better to have an additional column indicating the employee or dependent.

Combining data with additional columns #

You can include an additional column in the SELECT statements when using the UNION operator.

For example, the following query adds the role column to distinguish between employees and dependents in the combined result set:

SELECT
  first_name,
  last_name,
  'Employee' AS role
FROM
  employees
UNION
SELECT
  first_name,
  last_name,
  'Dependent' AS role
FROM
  dependents
ORDER BY
  first_name,
  last_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  |  last_name  |   role
-------------+-------------+-----------
 Adam        | Fripp       | Employee
 Alec        | Partners    | Dependent
 Alexander   | Hunold      | Employee
 Alexander   | Khoo        | Employee
 Bette       | De Haan     | Dependent
 Bob         | Hartstein   | Dependent
 Britney     | Everett     | Employee
...Code language: plaintext (plaintext)

Summary #

• Use the UNION operator to combine result sets from two queries into a single result set.
• The UNION operator removes duplicate rows from the final result set.
• Use the UNION ALL operator to retain the duplicate rows.
• Place the ORDER BY in the second query to sort the rows in the final result set.

Quiz #

Databases #

• PostgreSQL UNION Operator
• MySQL UNION Operator
• SQLite UNION Operator
• Db2 UNION Operator
• Oracle UNION Operator
• SQL Server UNION Operator