Lesson 4. Sets and Bags

Mission link: UNION and UNION ALL differ by one word and by a sort of the whole result. Knowing which you meant is both a correctness question and, at scale, the difference between a fast query and a slow one.
Primary source: PostgreSQL, 7.4 Combining Queries
Prerequisites: Lesson 3

Warm-up

1. ▢ Why does WHERE country <> 'GB' skip the rows where country is NULL?

2. ▢ SELECT sum(amount) FROM orders WHERE false returns what, and why does it matter?

Know this

Relational theory talks about sets, where every element is distinct. SQL tables are bags, also called multisets: the same row can appear any number of times, and the engine does nothing about it unless you ask.

INSERT INTO customers (id, email) VALUES (3, 'x@example.com');
-- a second row with the same email is rejected only because of the UNIQUE constraint

Without a constraint, nothing prevents duplicates, and no query can tell two identical rows apart. That is the practical reason lesson 6 exists.

DISTINCT removes duplicate output rows

SELECT DISTINCT country FROM customers;
SELECT DISTINCT country, id FROM customers;    -- distinct over BOTH columns

DISTINCT applies to the whole output row, not to the column it happens to sit next to. Adding a unique column to the select list therefore makes DISTINCT do nothing, which is a common way for it to appear to stop working.

Removing duplicates costs a sort or a hash of the entire result, so DISTINCT used to paper over an accidental row multiplication from a join is paying twice: once to produce the extra rows and once to discard them. When DISTINCT appears necessary, check the joins first.

The set operators, and the ALL that skips the deduplication

SELECT country FROM customers
UNION                         -- removes duplicates across both sides
SELECT country FROM archived_customers;

SELECT country FROM customers
UNION ALL                     -- keeps everything, no sort
SELECT country FROM archived_customers;

All three default to removing duplicates and all three have an ALL variant. UNION ALL is the one to reach for by default, because most of the time the sides are already disjoint and the deduplication is pure cost. Use UNION when duplicates are genuinely possible and unwanted, and know you are paying for a full pass.

Two rules the operators impose: both sides must have the same number of columns with compatible types, and the column names come from the first branch. For deduplication, two NULLs count as the same value, which is lesson 3's asymmetry appearing again.

Set operations against joins

EXCEPT and NOT EXISTS often express the same intent, and they are not equivalent:

-- customers with no orders, as a set difference on whole rows
SELECT id FROM customers EXCEPT SELECT customer_id FROM orders;

-- the same question, row by row
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

The first deduplicates its output and compares whole rows, which is fine when you select one column and surprising when you select several. The second keeps every matching customer row as it is. NOT EXISTS is also null-safe, unlike NOT IN from lesson 3.

A result has no order until you ask

ORDER BY is not part of the relation. It is applied last, to the output, which is why:

• A subquery's ORDER BY is not guaranteed to survive into the outer query, and relying on it is one of the most common invalid assumptions in SQL.
• UNION may return rows in any order, even though it had to sort them internally to deduplicate. That sort is an implementation detail, not a promise.
• Only a top-level ORDER BY orders a result, and only a unique sort key makes it deterministic.

Practice

1. ▢ Predict the row counts, given customers has 3 rows with countries 'GB', 'GB', NULL.

   SELECT country FROM customers;
   SELECT DISTINCT country FROM customers;
   SELECT DISTINCT country, id FROM customers;
   

2. ▢ Two tables of 500,000 rows each, known to be disjoint. Which operator, and what does the other one cost?

3. ▢ Which is the safest way to list customers with no orders?

   • a) SELECT id FROM customers EXCEPT SELECT customer_id FROM orders
   • b) SELECT id FROM customers WHERE id NOT IN (SELECT customer_id FROM orders)
   • c) SELECT c.id FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
   • d) SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id

4. ▢ A developer relies on this and it works in testing. Say what is wrong with it.

   SELECT * FROM (SELECT id FROM orders ORDER BY shipped_at DESC) t LIMIT 10;
   

5. ▢ A query returns 4,000 rows instead of the expected 1,000, so a colleague adds DISTINCT and the count is right again. Why is that a bad fix?

Real-world reps

• [ ] Insert two rows with the same value in a non-unique column, then run the three queries from practice 1 and watch the counts.
• [ ] Write the same "customers with no orders" question all four ways from practice 3, including the broken LEFT JOIN, and compare the results.
• [ ] Tomorrow: find a DISTINCT in code you know. Decide whether it is removing genuine duplicates or hiding a join, and check the join to be sure.

Going further

• 7.4 Combining Queries: the operators, the type rules, and the ALL variants
• 7.5 Sorting Rows: why ordering is applied last, which is lesson 5
• Modern SQL: what the standard says about set operators and where engines differ
• Resources

Not landing? Reread the primary source at the top, since this lesson compresses it and compression is where understanding leaks. Check the glossary for any term that felt slippery.

If the lesson itself is unclear rather than the material, that is a defect: open an issue.