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.

Employee
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
Employee_backup
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
6 'Abdul' 'K' '[email protected]' 25000 2020-07-14
7 'Swati' 'Karia' '[email protected]' 22000 2020-09-18

Consider the following query with the UNION operator.

SQL Script: UNION Operator
SELECT * FROM Employee
UNION
    SELECT * from Employee_backup

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

EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
2 'James' 'Bond' 2018-07-29
3 'Neena' 'Kochhar' '[email protected]' 17000 2018-08-22
4 'Lex' 'De Haan' '[email protected]' 15000 2018-09-8
5 'Amit' 'Patel' 18000 2019-01-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
6 'Abdul' 'K' '[email protected]' 25000 2020-07-14
7 'Swati' 'Karia' '[email protected]' 22000 2020-09-18

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

SQL Script: Error with UNION
SELECT * FROM Employee
UNION
    SELECT EmpId, FirstName from Employee_backup

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

SQL Script: UNION Query
SELECT * FROM Employee
WHERE Salary > 18000
UNION
    SELECT * from Employee_backup
    WHERE Salary > 18000
EmpId FirstName LastName Email Salary HireDate
1 'John' 'King' '[email protected]' 33000 2018-07-25
6 'Abdul' 'Kalam' '[email protected]' 25000 2020-07-14
6 'Abdul' 'K' '[email protected]' 25000 2020-07-14
7 'Swati' 'Karia' '[email protected]' 22000 2020-09-18