SQL unions

The UNION operator combines the result sets of two or more SELECT statements, removing duplicates by default. It works by concatenating rows from different queries, as long as they have the same number of columns and matching data types.

To include duplicates, use UNION ALL.

SELECT column_name(s) FROM table1  
UNION  
SELECT column_name(s) FROM table2;

Key rules:

• Both SELECT statements must have the same number of columns.
• The data types of corresponding columns must be compatible.
• By default, duplicate rows are removed (UNION), but UNION ALL keeps them.

Example

Customers_US

id	name	country
1	Alice	USA
2	Bob	USA
3	Charlie	USA

Customers_Canada

id	name	country
4	David	Canada
5	Alice	Canada

Using UNION:

SELECT name, country FROM Customers_US  
UNION  
SELECT name, country FROM Customers_Canada;

Result:

name	country
Alice	USA
Bob	USA
Charlie	USA
David	Canada
Alice	Canada

• The two tables are combined into one result set.
• Since Alice appears in both tables but with different country values, both entries are kept.
• If there had been an exact duplicate row (same name and country), UNION would have removed it.

Using UNION ALL (Keeping Duplicates)

To retain duplicate rows, use UNION ALL:

SELECT name, country FROM Customers_US  
UNION ALL  
SELECT name, country FROM Customers_Canada;

This would include any exact duplicates from both tables.

Key Differences from Other Set Operations

Operator	Removes Duplicates?	Combines Rows?
UNION	✅ Yes	✅ Yes
UNION ALL	❌ No	✅ Yes
INTERSECT	✅ Yes	❌ No (Only common rows)
EXCEPT	✅ Yes	❌ No (Only rows in first set, not in second)