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.

Consider the following query with the 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.

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 ALL
    SELECT EmpId, FirstName from Employee_backup

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

SELECT EmpId, FirstName, LastName, Salary FROM Employee
WHERE Salary > 18000
UNION ALL
    SELECT EmpId, FirstName, LastName, Salary from Employee_backup
    WHERE Salary > 18000