SQL UNION Operator

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set.

The result set is made up of all the rows from each SELECT statement that do not have duplicates.

The number of columns and the data types of the columns in the SELECT statements must be the same.

Syntax

The basic syntax for the UNION operator is as follows:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

You can also use the UNION ALL operator, which combines the result sets of two or more SELECT statements into a single result set and includes duplicate rows:

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

Examples

Combining two tables

Suppose we have two tables, 'employees' and 'employees_backup' with the same structure.

We can use the UNION operator to combine the data from both tables into a single result set.

SELECT * FROM employees
UNION
SELECT * FROM employees_backup;

Filtering duplicate records

If we want to get unique records from both tables, we can use UNION operator.

SELECT name, salary FROM employees
UNION
SELECT name, salary FROM employees_backup;
Using UNION ALL operator

If we want to get all records from both tables including duplicates, we can use UNION ALL operator.

SELECT name, salary FROM employees
UNION ALL
SELECT name, salary FROM employees_backup;

Using UNION operator with ORDER BY

We can use the ORDER BY clause with the UNION operator to sort the result set by one or more columns.

SELECT name, salary FROM employees
UNION
SELECT name, salary FROM employees_backup
ORDER BY salary DESC;

Note:

  • The column names in the SELECT statements do not need to be the same, but the data types of the columns must be compatible.
  • You can use the UNION operator with more than two SELECT statements.
  • You should use parentheses when using the UNION operator with other operators and clauses.

Conclusion

The UNION operator is very useful when working with multiple tables or when you need to filter duplicate records.

It's important to remember that the columns and data types of the SELECT statements must be the same, and that you can use the UNION ALL operator to include duplicate rows in the result set.

SQL Basics