Set operators combine the results of two queries vertically (more rows), unlike joins which combine tables horizontally (more columns).

Operator	Returns	Duplicates
`UNION`	Rows in either query	Removed
`UNION ALL`	Rows in either query	Kept (faster — no de-dup step)
`INTERSECT`	Rows in both queries	Removed
`EXCEPT` (`MINUS` in Oracle)	Rows in the first but not the second	Removed

Rules: both queries must return the same number of columns with compatible types; column names come from the first query; one ORDER BY at the very end sorts the combined result.

UNION vs UNION ALL

SQL

-- cities we have customers or offices in
SELECT city FROM customers WHERE city IS NOT NULL
UNION
SELECT location FROM departments
ORDER BY 1;

Output

city
---------
Bengaluru
Delhi
Hyderabad
Mumbai
Pune

SQL

SELECT city FROM customers WHERE city IS NOT NULL
UNION ALL
SELECT location FROM departments
ORDER BY 1;

Output

city
---------
Bengaluru
Bengaluru
Delhi
Delhi
Hyderabad
Mumbai
Mumbai
Pune

INTERSECT and EXCEPT

SQL

-- cities with both a customer and an office
SELECT city FROM customers
INTERSECT
SELECT location FROM departments
ORDER BY 1;

-- office cities with no customers
SELECT location FROM departments
EXCEPT
SELECT city FROM customers
ORDER BY 1;

Output

city
---------
Bengaluru
Mumbai

location
---------
Hyderabad
Pune

Labelled union: combining different sources

SQL

SELECT 'employee' AS kind, name, dept_id AS ref FROM employees WHERE salary >= 110000
UNION ALL
SELECT 'customer', name, customer_id FROM customers WHERE city = 'Delhi'
ORDER BY kind, name;

Output

kind     | name  | ref
---------+-------+----
customer | Imran | 4
customer | Rahul | 2
employee | Asha  | 1
employee | Priya | 2
employee | Ravi  | 1

Comparing two tables

SQL

CREATE TABLE prices_old (sku TEXT, price INTEGER);
CREATE TABLE prices_new (sku TEXT, price INTEGER);
INSERT INTO prices_old VALUES ('A', 10), ('B', 20), ('C', 30);
INSERT INTO prices_new VALUES ('A', 10), ('B', 25), ('D', 40);

SELECT 'removed/changed' AS diff, * FROM (SELECT * FROM prices_old EXCEPT SELECT * FROM prices_new)
UNION ALL
SELECT 'added/changed', * FROM (SELECT * FROM prices_new EXCEPT SELECT * FROM prices_old)
ORDER BY sku, diff;

Output

diff            | sku | price
----------------+-----+------
added/changed   | B   | 25
removed/changed | B   | 20
removed/changed | C   | 30
added/changed   | D   | 40

What is the difference between UNION and UNION ALL?

What is the difference between UNION and JOIN?