Description

The SQL keyword UNION is 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 data from columns from two or more tables into a single result set.

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.

UNION

The below SQL uses UNION to combine the results of two SELECT queries, and eliminates the duplicates.

Run this on IDE

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

UNION ALL

The below SQL uses UNION to combine the results of two SELECT queries, and includes the duplicates (in case of any) as well.

Run this on IDE

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

UNION Vs UNION ALL

The only difference between the UNION and UNION ALL operations is that UNION eliminates duplicates in case of any and UNION ALL includes duplicates.

So, when we want to include the duplicates in the result set, then use UNION ALL, other use UNION.

Related Links