Description

The SQL UNION operator can be used to combine the results of two or more SELECT queries into a single result.

The UNION operation is different from using JOINS on SELECT statements.

  • The UNION operation combines the results from two or more SELECT queries.
  • The JOINS combines the columns from two or more tables.

In order to use the UNION operation to combine the results, all the involved SELECT queries must follow the below rules, which makes them union-compatible.

  • The number and the order of the columns must be the same across all queries.
  • The data types of the corresponding columns must be compatible.
  • The column names may not be the same.

The column names of the combined result set are taken from the first SELECT query, in case of different column names on the SELECT queries involved in the UNION.

Syntax

Here is the basic syntax of the UNION operator.

  • The column_list1 and column_list2 represent the column list on the respective table.
  • The table1_name and table2_name represent table names.
SELECT column_list1 FROM table1_name
UNION
SELECT column_list2 FROM table2_name;

By default, the UNION operator eliminates the duplicates on the combined result set. However, we can use the UNION ALL operator to allow duplicates.

SELECT column_list1 FROM table1_name
UNION ALL
SELECT column_list2 FROM table2_name;

Table Data

Consider the below data in the employees and customers tables, which have columns of similar data types.

  • The tables contain a common record based on the name 'Yoshi Nagase', which may become a duplicate based on the columns included in the SELECT queries.

Customers Table Data

customer_id customer_name address city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo Japan 100
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. Portland USA 97219

Suppliers Table Data

shipper_id shipper_name contact_name phone address city country postal_code
1 Exotic Liquids Charlotte Cooper (171) 555-2222 49 Gilbert St. London UK EC1 4SD
2 New Orleans Cajun Delights Shelley Burke (100) 555-4822 P.O. Box 78934 New Orleans USA 70117
3 Grandma Kellys Homestead Regina Murphy (313) 555-5735 707 Oxford Rd. Ann Arbor USA 48104
4 Tokyo Traders Yoshi Nagase (03) 3555-5011 9-8 Sekimai Musashino-shi Tokyo Japan 100
5 Mayumis Mayumi Ohno (06) 431-7877 92 Setsuko Chuo-ku Osaka Japan 545

SELECT Query Results

When we execute the below SELECT queries, the results are union-compatible, as they satisfy all the conditions required for a UNION.

SELECT statement on the customers table returns the below data.

SELECT customer_name, address, city
FROM customers;
customer_name address city
Maria Anders Obere Str. 57 Berlin
Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo
Dominique Perrier 25, rue Lauriston Paris
Martin Blank Via Monte Bianco 34 Turin
Thomas Hardy 89 Chiaroscuro Rd. Portland

SELECT statement on the suppliers table returns the below data.

SELECT contact_name, address, city
FROM suppliers;
contact_name address city
Charlotte Cooper 49 Gilbert St. London
Shelley Burke P.O. Box 78934 New Orleans
Regina Murphy 707 Oxford Rd. Ann Arbor
Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo
Mayumi Ohno 92 Setsuko Chuo-ku Osaka

UNION Operation

The below SQL SELECT query uses UNION to combine the results of two SELECT queries, which satisfy the conditions required for UNION.

  • Both have the same number of columns
  • Both have columns with a compatible or similar data type, in the same order.

Run this on IDE

SELECT customer_name, address, city
FROM customers
UNION
SELECT contact_name, address, city
FROM suppliers;

After successful execution, the result has the below data, which doesn't include the duplicates.

  • The result set has nine records out of ten, as one of them is a duplicate.
  • The result set has taken the column names from the first SELECT query, as the column names are different between the queries.
customer_name address city
Maria Anders Obere Str. 57 Berlin
Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo
Dominique Perrier 25, rue Lauriston Paris
Martin Blank Via Monte Bianco 34 Turin
Thomas Hardy 89 Chiaroscuro Rd. Portland
Charlotte Cooper 49 Gilbert St. London
Shelley Burke P.O. Box 78934 New Orleans
Regina Murphy 707 Oxford Rd. Ann Arbor
Mayumi Ohno 92 Setsuko Chuo-ku Osaka

UNION ALL Operation

The below SQL SELECT query uses UNION ALL to combine the results of two SELECT queries, which satisfy the conditions required for UNION.

  • Both have the same number of columns
  • Both have columns with a compatible or similar data type, in the same order.

Run this on IDE

SELECT customer_name, address, city
FROM customers
UNION ALL
SELECT contact_name, address, city
FROM suppliers;

After successful execution, the result has the below data, which included the duplicates.

  • The result set has ten out of ten records, as duplicates are included.
  • The result set has taken the column names from the first SELECT query, as the column names are different between the queries.
customer_name address city
Maria Anders Obere Str. 57 Berlin
Yoshi Nagase 9-8 Sekimai Musashino-shi Tokyo
Dominique Perrier 25, rue Lauriston Paris
Martin Blank Via Monte Bianco 34 Turin
Thomas Hardy 89 Chiaroscuro Rd. Portland
Charlotte Cooper 49 Gilbert St. London
Shelley Burke P.O. Box 78934 New Orleans
Regina Murphy 707 Oxford Rd. Ann Arbor
Mayumi Ohno 92 Setsuko Chuo-ku Osaka

Tips & Recommendations

By default, the UNION operator eliminates the duplicates between the query results it combines.

If we want to leave the duplicates, then we need to use the UNION ALL operator.

Overall

We now understood how to create SQL SELECT statements using UNION and UNION ALL operators.

Related Links