Self Joins & UNION

users.referred_by holds a user_id — of another row in users. The referrer's name is one join away, and the table on the other side of that join is the same table.

Nothing about the syntax is special. What is mandatory is aliases: name the table twice, differently, and from then on u and r behave like two unrelated tables that happen to hold the same rows.

FROM users u
JOIN users r ON r.user_id = u.referred_by

Read it as "u is the person, r is the person who referred them". Without the two aliases there is no way to write the ON at all — users.user_id = users.referred_by asks whether a row referred itself.

Your task: return each referred user's name as referee and their referrer's name as referrer, ordered by the referee's user_id.

The inner join in the last station answered "who referred whom". A different question — "how did each user arrive?" — is about every user, and four of them arrived on their own.

Everything from 3-02 applies unchanged. A self join is still a join, so LEFT JOIN keeps the unmatched rows and pads the right-hand alias with NULL.

COALESCE then turns that NULL into something a reader can act on. A blank cell in a "referred by" column is ambiguous — missing data, or nobody? — and the word direct is neither.

Your task: return every user's name and their referrer's name as referrer, using the text direct where there was no referrer. Order by u.user_id.

The same two aliases, the same one column, read in the opposite direction. Instead of "who referred me", ask "who did I refer" — and the ON flips.

ON u.referred_by = r.user_id   -- r is the referrer, u is their referee

Now r is the row you keep and group by, and each u is somebody they brought in. A LEFT JOIN keeps the users who referred nobody, and — exactly as in 3-02 — COUNT has to count a column, not rows, or everyone who referred nobody reports 1.

Your task: return every user's name and how many people they referred as referred. Order by referred descending, then name.

The other use of a self join: finding pairs of rows that share something. Two users on the same plan, two orders on the same day, two employees in the same office.

The obvious version is wrong twice over. ON a.plan = b.plan matches every user with themselves, and matches each real pair twice — once as (Ada, Grace) and again as (Grace, Ada).

Adding a.user_id <> b.user_id fixes only the first problem. The fix for both at once is to demand an order:

ON a.plan = b.plan AND a.user_id < b.user_id

A row cannot be less than itself, so self-matches go; and of the two orderings of any real pair, exactly one satisfies <. Without it there are 50 rows here. With it, 19.

Your task: return every pair of users on the same plan as person_a, person_b and plan, each pair once, ordered by a.user_id then b.user_id.

Every join so far made results wider — more columns, from more tables. UNION does the other thing: it stacks one result on top of another, making it longer.

SELECT plan FROM users
UNION
SELECT plan FROM subscriptions;

Three rules, all enforced. Both sides need the same number of columns, in the same order, with compatible types — Postgres will say each UNION query must have the same number of columns or UNION types text and integer cannot be matched rather than guessing. Column names come from the first branch.

And the part that surprises people: UNION removes duplicates, across both branches. 22 plan values go in; 3 distinct ones come out.

Your task: return every distinct plan name appearing in either users or subscriptions, as plan, ordered by plan.

UNION deduplicates. That sounds harmless and is not: to remove duplicates the database must sort or hash the entire combined result, and it removes rows that were genuinely distinct records which merely happened to look identical.

UNION ALL concatenates and stops. It is faster, and it does not silently delete data.

Your task: return every plan value from both tables, keeping duplicates, as plan, ordered by plan.

The everyday use of UNION ALL: two tables recording different kinds of thing, wanted as one list in time order. Orders and events have nothing in common structurally — but "what did this user do, in order" needs both.

The trick is a literal column that labels which branch each row came from. It is not stored anywhere; you write it into the SELECT list, and it survives the stacking.

SELECT 'order' AS kind, o.ordered_at AS happened_on FROM orders o
UNION ALL
SELECT 'event', e.event_at FROM events e

Only the first branch's aliases matter — the second branch's column names are ignored entirely, so there is no point writing them. The ORDER BY goes once, at the end, and applies to the whole stack.

Your task: build that timeline for user 1: kind (order or event) and happened_on, ordered by happened_on then kind.

Two more set operators, both properly useful and both rarely taught. They follow the same column rules as UNION, and they deduplicate the same way.

EXCEPT is the readable alternative to the anti-join from 3-02 when you only need the key and not the other table's columns. Some databases spell it MINUS; Postgres uses the standard word.

Your task: return the user_id of every user who has an event but has never placed an order, ordered by user_id.

The constraints are worth meeting deliberately once, so the errors are recognisable later.

Because pairing is positional, two branches can compile perfectly and still be wrong: swap two columns of matching type in the second branch and you get countries under a name heading, silently.

Your task: one list of everyone the business deals with. Return name and country from users, stacked with the product name and the literal 'catalogue' as country from products. Keep duplicates, order by name.