Difference between UNION and UNION ALL in SQL.

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

Ø   UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

Ø   UNION ALL does not remove duplicates, and it therefore faster than UNION.

Note: While using this commands all selected columns need to be of the same data type.

Example: If we have two tables, 1) Employee and 2) Customer

1) Employee table data: 

2) Customer table data:

3) UNION Example (It removes all duplicate records):

4) UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):