SQL - Union All Operator

UNION ALL Operator is used to combine result set of two or more SELECT queries. The UNION ALL operator does not remove duplicate rows from SELECT statement result set.

UNION and UNION ALL operators works same. Only difference is UNION operator exclude duplicate rows from result set. UNION ALL does not remove duplicate rows from query result set.

Syntax:

SELECT column_name1, column_name2,...
FROM tables
[WHERE Condition]
UNION ALL
    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 ALL operator.

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

Above query returns all the records in both the tables even if they are duplicate records, 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
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

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 ALL
SELECT * FROM Employee
UNION ALL
    SELECT EmpId, FirstName from Employee_backup

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

SQL Script: UNION ALL Query
SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE Salary > 18000
UNION ALL
    SELECT EmpId, FirstName, LastName, Salary from Employee_backup
    WHERE Salary > 18000
EmpId FirstName LastName Salary
1 'John' 'King' 33000
6 'Abdul' 'Kalam' 25000
1 'John' 'King' 33000
6 'Abdul' 'K' 25000
7 'Swati' 'Karia' 22000