Database Developer Question:

This is one of the tricky SQL Interview Questions. Interviewer may ask you this question in another way as what are the advantages of Union All over Union.

Both Union and Union All concatenate the result of two tables but the way these two queries handle duplicates are different.

Union: It omits duplicate records and returns only distinct result set of two or more select statements.
Union All: It returns all the rows including duplicates in the result set of different select statements.

Performance wise Union All is faster than Union, Since Union All doesn’t remove duplicates. Union query checks the duplicate values which consumes some time to remove the duplicate records.

Assume: Table1 has 10 records, Table2 has 10 records. Last record from both the tables are same.

If you run Union query.

SELECT * FROM Table1
UNION
SELECT * FROM Table2
Output: Total 19 records

If you run Union query.


SELECT * FROM Table1
UNION ALL
SELECT * FROM Table2
Output: Total 20 records

Data type of all the columns in the two tables should be same.