SQL - Union Operator

The UNION operator is used to combine result set of two or more SELECT queries. The UNION operator automatically removes duplicate rows from SELECT statement result set.

Syntax:

SELECT column_name1, column_name2,... FROM tables [WHERE Condition] UNION SELECT column_name1, column_name2, ... FROM tables [WHERE Condition];

For the demo purpose, we will use the following tables in all examples.

Consider the following query with the UNION operator.

SELECT * FROM Employee
UNION
    SELECT * from Employee_backup

Above query returns the distinct records in both the tables, as shown below.

Note that both the queries must have equal number of expressions in their SELECT clause. The following query will raise an error.

SELECT * FROM Employee
UNION
    SELECT EmpId, FirstName from Employee_backup

You can use the WHERE clause with any or all queries, as shown below.

SELECT * FROM Employee
WHERE Salary > 18000
UNION
    SELECT * from Employee_backup
WHERE Salary > 18000

Above query returns the distinct records in both the tables, as shown below.