UNION

UNION

UNION combines the results of two or more SELECT statements into one result set.

Basic rules

• Each SELECT must have the same number of columns.
• The columns must be in the same order.
• The data types should match.
• UNION removes duplicate rows.

Example

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

Returns all cities from both tables, each city once.

UNION ALL

UNION ALL keeps duplicates:

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers;

A city present in both tables appears twice.

UNION vs UNION ALL

UNION ALL is usually faster because it doesn't have to check for duplicates.

ORDER BY placement

ORDER BY applies to the whole combined result, so it goes at the very end.

TL;DR

• UNION stacks the results of two queries.
• Same number of columns, in the same order.
• UNION removes duplicates; UNION ALL keeps them.
• ORDER BY goes at the very end.