DISTINCT, UNION and set operations

UNION deduplicates, UNION ALL does not

SELECT email FROM customers
UNION
SELECT email FROM leads;

That returns each email once, across both lists. To get there the engine must compare every row with every other — a sort or a hash over the whole result — and that work happens whether or not any duplicates exist.

SELECT email FROM customers
UNION ALL
SELECT email FROM leads;

This one concatenates and stops. `UNION ALL` is the default worth having: use it unless you specifically need deduplication, and when you do need it, know you are paying for a sort.

Both forms require the two sides to have the same number of columns, in compatible types and in the same order — the column names come from the first SELECT, and the second one's names are ignored entirely.

INTERSECT and EXCEPT

-- In both lists
SELECT email FROM customers
INTERSECT
SELECT email FROM leads;

-- In the first, not the second
SELECT email FROM customers
EXCEPT
SELECT email FROM leads;

EXCEPT is MINUS in Oracle. Both deduplicate by default, like UNION.

These read better than the equivalent NOT EXISTS for simple whole-row comparisons — and unlike NOT IN, they handle NULL sanely, treating two NULLs as the same value for the purpose of matching.

DISTINCT is usually a symptom

SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id;

This works, and it is almost always the wrong fix. The duplicates are not in customers — the join created them, one row per order. DISTINCT then sorts the whole result to remove work that should not have been done:

SELECT c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Same answer, no duplicates to remove, and the engine can stop at the first matching order per customer.

The rule of thumb: when you reach for DISTINCT, ask where the duplicates came from. If a join produced them, the question was "does a match exist" — and that is EXISTS.

DISTINCT applies to the whole row

SELECT DISTINCT country, city FROM customers;

That is distinct pairs, not distinct countries with some city. There is no way to apply it to one column of several — the syntax that looks like it does, DISTINCT(country), city, is just DISTINCT with a redundant pair of parentheses, and it still deduplicates both columns. When you want one row per country with a chosen city, that is GROUP BY country with an aggregate, or a window function ranking the cities.

PostgreSQL has an extension for exactly this:

SELECT DISTINCT ON (country) country, city
FROM customers
ORDER BY country, population DESC;

One row per country — the first one in the given order, so the ORDER BY is doing the choosing and is not optional. Convenient, and PostgreSQL-only; the portable version is ROW_NUMBER() in a subquery.