Self-Joins and RIGHT/FULL OUTER

Why relate a table to itself

Org charts, threaded comments, category trees, "customers referred by other customers": all of these store a relationship between two rows of the same table. The table has a column that points back at its own primary key (manager_id referencing employee_id). To read the pair as one row ("Grace reports to Ada"), you join the table to itself. Interviewers love this because it proves you understand that a join operates on aliases, not table names.

A self-join is an ordinary join with two aliases

There is no special SELF JOIN keyword. You list the same table twice, give each appearance a different alias, and join them like any two tables. Picture two independent copies of the rows: e is "the employee I am describing" and m is "the row that happens to be that employee's manager."

SELECT
  e.employee_name AS employee,
  m.employee_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON e.manager_id = m.employee_id;

The ON clause matches each employee's manager_id to some row's employee_id. Using LEFT JOIN (not INNER) keeps top-level people whose manager_id is NULL; they survive with a NULL manager instead of vanishing. Against the seed data you get:

employee   | manager
-----------+--------
Ada        | NULL
Grace      | Ada
Alan       | Ada
Katherine  | Grace

Add ORDER BY e.employee_name when output order matters. Row order is not guaranteed otherwise.

Outer joins for reconciling two sources

Now the two inputs are different tables: yesterday's snapshot and today's. To see what was added, dropped, or changed, you need every key from both sides. That is a FULL OUTER JOIN: keep all left rows, all right rows, and NULL-pad wherever a match is missing. A RIGHT JOIN is simply a LEFT JOIN with the operands swapped (keep every right-side row).

yesterday · FULL JOIN · today ON customer_id = customer_id

Step 1 / 5

customer_id	tier
1	gold
2	silver
3	bronze
4	silver

customer_id	tier
matched: 1	gold
2	gold
4	silver
5	bronze

customer_id=1 matches 1 today row.

Result (1 row)

yesterday.customer_id	yesterday.tier	today.customer_id	today.tier
1	gold	1	gold

Because an unmatched key is NULL on the missing side, recover one clean key with COALESCE(y.customer_id, t.customer_id), then put yesterday's and today's values side by side and let the NULLs tell the story:

SELECT
  COALESCE(y.customer_id, t.customer_id) AS customer_id,
  y.tier AS tier_yesterday,
  t.tier AS tier_today
FROM yesterday AS y
FULL OUTER JOIN today AS t
  ON y.customer_id = t.customer_id;

Read the result a row at a time: a NULL tier_yesterday means the customer is new today (added); a NULL tier_today means they vanished (dropped); two different non-NULL tiers means the tier changed. Collapsing those three tests into a single change_type label is a job for CASE, which you meet later this level; for now the two columns carry the full diff.

RIGHT and FULL OUTER JOIN only arrived in SQLite 3.39 (2022); Postgres, Snowflake, BigQuery, and SQL Server have had them for years. Older engines emulate FULL OUTER as a LEFT JOIN unioned with the reversed LEFT JOIN.

Interview nuance: SQL uses three-valued logic. Any comparison involving NULL, including NULL = NULL and NULL <> NULL, returns unknown rather than true or false, and only true passes a WHERE or ON. This one rule explains why an added customer's NULL-padded tier never compares equal to today's, why col NOT IN (subquery_with_a_null) returns zero rows, and why you match missing keys with IS NULL instead of = NULL.