Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF: Worked examples

This page is the drill hall for the parent Joins topic. The parent taught you the one question — "when a row has no match, what do I do with it?" Here we walk every case that question can throw at you, one worked example per cell, so you never meet a join scenario you haven't already seen solved.

We reuse the parent's tables. Keep them in view:

Employees

emp_id	name	dept_id	manager_id
1	Asha	10	NULL
2	Bilal	20	1
3	Cara	NULL	1

Departments

dept_id	dept_name
10	Sales
30	Legal

The scenario matrix

Before solving, we list every case-class a join question can be. Each later example is tagged with the cell it covers. If a cell has no example, you'd have a hole — there are none.

#	Case class	What makes it tricky	Covered by
C1	Clean match only (INNER)	the "happy path", overlap only	Ex 1
C2	Keep-left, pad NULL (LEFT)	unmatched left rows survive	Ex 2
C3	Anti-join (LEFT + IS NULL)	isolate the unmatched side	Ex 3
C4	Keep-right / orphan parent	direction flip, RIGHT ≡ LEFT swapped	Ex 4
C5	Keep-both (FULL OUTER)	union of both sides, double padding	Ex 5
C6	NULL key degeneracy	`NULL = NULL` is not true	Ex 6
C7	Cartesian / no condition (CROSS)	size = $m \times n$ , real-world grid	Ex 7
C8	SELF join + dedup inequality	one table, two roles, avoid double/self pairs	Ex 8
C9	ON-vs-WHERE trap (limiting behaviour)	filter before vs after padding	Ex 9
C10	Exam twist: count without running it	reason from the derivation formula	Ex 10

Ex 1 — Cell C1: the clean match (INNER)

Worked example Q: List each employee together with their real department name.

SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

Forecast: How many rows come back — 1, 2, or 3? Guess before reading on.

Take the Cartesian product of the two tables — $3 \times 2 = 6$ candidate pairs. Why this step? Every join starts as all possible pairings; INNER is what survives a filter.
Filter by e.dept_id = d.dept_id. Only Asha (10) meets a Sales (10). Why this step? INNER keeps only the overlap — no padding, no survivors from the losing side.
Bilal's 20 matches nothing; Cara's NULL matches nothing; Legal's 30 has no employee. All three drop. Why this step? INNER's rule is "no match → gone."

Result: one row — Asha, Sales.

Verify: count the overlap of the key sets. Left keys ${10, 20, NULL}$ , right keys ${10, 30}$ . The only value present in both is $10$ → exactly 1 matched row. ✅

Ex 2 — Cell C2: keep every employee (LEFT + padding)

Worked example Q: List

all employees with their department, showing a blank where none exists.

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id;

Forecast: Which employees get a NULL department?

Compute the INNER result first: Asha–Sales (from Ex 1). Why this step? LEFT = INNER plus the leftover left rows.
Find left rows with no match: Bilal (dept 20 not in Departments) and Cara (dept NULL). Why this step? These are the rows LEFT rescues; INNER threw them away.
Pad those with NULL on the right side. Why this step? The right columns have nothing to fill, and NULL means "unknown/absent."

Result: 3 rows — Asha–Sales, Bilal–NULL, Cara–NULL.

Verify: $∣ LEFT ∣ = ∣ INNER ∣ + (unmatched left) = 1 + 2 = 3$ . Matches the table's 3 employees exactly (LEFT can never lose a left row). ✅

Ex 3 — Cell C3: the anti-join (find who has NO department)

Worked example Q: Which employees belong to

no department?

SELECT e.name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

Forecast: Name the employees you expect.

Start from the LEFT-JOIN result of Ex 2 (3 rows). Why this step? We need the padded rows available to filter on.
Keep only rows where the right key came back NULL: WHERE d.dept_id IS NULL. Why this step? A NULL in the right key is the fingerprint of "this left row found no partner." Matched rows have a real d.dept_id, so they're excluded.
We must write IS NULL, never = NULL. Why this step? In SQL, d.dept_id = NULL evaluates to unknown, never true — see NULL semantics in SQL. Only IS NULL tests for NULL.

Result: Bilal, Cara (2 rows).

Verify: these are exactly the unmatched-left rows from Ex 2. Count = 2 = (3 employees − 1 matched). ✅

Ex 4 — Cell C4: keep every department (RIGHT, and the LEFT rewrite)

Worked example Q: List

all departments with an employee, blank where the department is empty.

-- RIGHT form
SELECT e.name, d.dept_name
FROM Employees e
RIGHT JOIN Departments d ON e.dept_id = d.dept_id;
 
-- Equivalent, style-guide-friendly LEFT form
SELECT e.name, d.dept_name
FROM Departments d
LEFT JOIN Employees e ON d.dept_id = e.dept_id;

Forecast: Which department gets a NULL employee?

INNER part: Asha–Sales. Why this step? Same overlap as always.
Unmatched right rows: Legal (dept 30, no employee). Why this step? RIGHT rescues right rows; Legal is the orphan parent — a department that exists in the parent table but is unreferenced.
Pad Legal's employee column with NULL. Rewrite A RIGHT JOIN B as B LEFT JOIN A. Why this step? Both produce identical rows; many teams ban RIGHT because reading order then matches the kept table.

Result: 2 rows — Asha–Sales, NULL–Legal.

Verify: $∣ RIGHT ∣ = ∣ INNER ∣ + (unmatched right) = 1 + 1 = 2$ = number of departments. ✅

Ex 5 — Cell C5: keep everyone (FULL OUTER)

Worked example Q: Show every employee

and every department, blanks wherever a partner is missing.

SELECT e.name, d.dept_name
FROM Employees e
FULL OUTER JOIN Departments d ON e.dept_id = d.dept_id;

Forecast: Total rows: 3, 4, or 6?

Take LEFT (Ex 2 = 3 rows) and RIGHT (Ex 4 = 2 rows). Why this step? FULL = LEFT ∪ RIGHT.
Union, but the matched row Asha–Sales appears in both — count it once. Why this step? A union of sets does not duplicate the shared element.
So total = (matched 1) + (left-only 2) + (right-only 1). Why this step? This is the inclusion pattern: shared + left-orphans + right-orphans.

Result: 4 rows — Asha–Sales, Bilal–NULL, Cara–NULL, NULL–Legal.

Verify: $∣ FULL ∣ = 1 + 2 + 1 = 4$ . Also $∣ LEFT ∣ + ∣ RIGHT ∣ - ∣ INNER ∣ = 3 + 2 - 1 = 4$ . Both agree. ✅

The four join outcomes as regions of a Venn picture:

Figure — Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF

Ex 6 — Cell C6: the NULL-key degeneracy

Worked example Q: A junior "fixes" Cara by adding a NULL-department row to

Departments and re-runs the INNER join, expecting Cara to appear. Does she?

-- Departments now also has: (NULL, 'Unassigned')
SELECT e.name, d.dept_name
FROM Employees e
INNER JOIN Departments d ON e.dept_id = d.dept_id;

Forecast: Does Cara (dept_id NULL) now match the new (NULL, 'Unassigned') row?

The ON predicate is e.dept_id = d.dept_id, i.e. NULL = NULL. Why this step? Every join match is decided by this comparison.
In SQL, NULL = NULL is unknown, not true. Why this step? NULL means "value unknown"; two unknowns aren't provably equal. See NULL semantics in SQL.
Because the predicate is never true, Cara still finds no partner and is dropped. Why this step? INNER keeps only rows where the predicate is true.

Result: Cara still does not appear. The result is unchanged: just Asha–Sales.

Verify: the matched set is still keys-present-in-both counting only real equal values. NULL = NULL contributes 0 matches → still 1 row. To include NULL-key employees you must use LEFT (Ex 2), not equality on NULL. ✅

Ex 7 — Cell C7: real-world CROSS (build a grid)

Worked example Q: A shop sells 3 shirt styles in 4 sizes. Build every style–size combination to seed an inventory table.

SELECT st.style, sz.size
FROM Styles st
CROSS JOIN Sizes sz;   -- no ON condition

Forecast: How many rows — and why is there no ON clause?

CROSS pairs every left row with every right row: $m \times n$ . Why this step? We genuinely want all combinations; there is no "matching" idea here.
With $m = 3$ styles and $n = 4$ sizes: $3 \times 4 = 12$ rows. Why this step? That's the definition of the Cartesian product.
There is deliberately no ON. Why this step? A condition would remove combinations, but a full grid wants them all — this is the pure product $Styles \times Sizes$ .

Result: 12 rows (one per style-size cell).

Verify: $3 \times 4 = 12$ . Applying our running tables instead ( $3$ employees $\times$ $2$ departments) would give $3 \times 2 = 6$ — the parent's stated CROSS count. ✅

Ex 8 — Cell C8: SELF join with de-duplication

Worked example Q: List each employee next to their

manager's name, using only the Employees table.

SELECT e.name AS employee, m.name AS manager
FROM Employees e
LEFT JOIN Employees m ON e.manager_id = m.emp_id;

Forecast: How many rows, and who has a NULL manager?

Use two aliases of the same table: e (the worker) and m (the boss). Why this step? A SELF join gives one table two roles; aliases keep the columns distinguishable.
Match e.manager_id = m.emp_id: Bilal→1(Asha), Cara→1(Asha). Why this step? The foreign key manager_id points back into the same table's emp_id.
Use LEFT so Asha (manager_id NULL) still appears with a NULL manager. Why this step? The CEO has no boss; INNER would silently drop her (the C6 lesson again).

Result: 3 rows — Asha–NULL, Bilal–Asha, Cara–Asha.

Now the classic pairing variant (the parent's Q3):

SELECT a.name, b.name
FROM Employees a
JOIN Employees b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;

Why a.emp_id < b.emp_id? Without it, each unordered pair appears twice (A,B) and (B,A), plus self-pairs (A,A). The strict inequality keeps each pair once and forbids self-matches.

Verify (pairing): who shares a department? Asha(10), Bilal(20), Cara(NULL) — all distinct real depts, and NULL never matches. So 0 pairs. If instead Asha and a new Dev(dept 10) existed, exactly 1 pair (Asha, Dev) would survive emp_id < emp_id. ✅

Ex 9 — Cell C9: ON vs WHERE (the limiting/degrade trap)

Worked example Q: "List all employees, but only show a department name if it's Sales; keep everyone else with a blank." A beginner writes:

-- BROKEN
SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Sales';

Forecast: How many rows does the broken query return — 3 or 1?

The LEFT join first produces 3 rows: Asha–Sales, Bilal–NULL, Cara–NULL. Why this step? Padding happens during the join, before WHERE.
WHERE d.dept_name = 'Sales' then runs. For Bilal/Cara, d.dept_name is NULL, and NULL = 'Sales' is unknown → those rows are deleted. Why this step? WHERE keeps only rows where the predicate is true; unknown ≠ true.
Result silently collapses to 1 row (just Asha) — the LEFT JOIN degraded to an INNER JOIN. Why this step? This is the limiting failure: a right-column WHERE test discards every padded NULL.

Fix — move the condition into ON so it filters before padding:

SELECT e.name, d.dept_name
FROM Employees e
LEFT JOIN Departments d
  ON e.dept_id = d.dept_id AND d.dept_name = 'Sales';

Result: broken → 1 row; fixed → 3 rows (Asha–Sales, Bilal–NULL, Cara–NULL).

Verify: ON filters before padding (all 3 employees kept, only Sales linked); WHERE filters after padding (padded NULLs killed → 1). The row counts 3 vs 1 confirm the degrade. ✅

Ex 10 — Cell C10: exam twist — count without running it

Worked example Q: Tables

$R$ has $m = 500$ rows, $S$ has $n = 300$ rows. Exactly $120$ rows of $R$ match some row of $S$ on the key (each to exactly one $S$ row), and $90$ rows of $S$ are matched. Give $∣ INNER ∣$ , $∣ LEFT ∣$ , $∣ RIGHT ∣$ , $∣ FULL ∣$ . Forecast: Try all four before reading.

INNER = number of matched pairs. Each of the 120 matched $R$ rows maps to exactly one $S$ row, so $∣ INNER ∣ = 120$ . Why this step? INNER counts surviving pairs, not tables.
LEFT = INNER + unmatched-left = $120 + (500 - 120) = 500$ . Why this step? LEFT keeps all left rows; count can't exceed $m$ when the match is one-to-one.
RIGHT = INNER + unmatched-right = $120 + (300 - 90) = 330$ . Why this step? Wait — INNER pairs use 90 distinct $S$ rows but 120 $R$ rows; so matched pairs = 120, matched-right rows = 90, unmatched-right = $300 - 90 = 210$ , giving $∣ RIGHT ∣ = 120 + 210 = 330$ .
FULL = INNER + unmatched-left + unmatched-right = $120 + 380 + 210 = 710$ . Why this step? FULL = shared pairs + both orphan sets.

Result: INNER $= 120$ , LEFT $= 500$ , RIGHT $= 330$ , FULL $= 710$ .

Verify: cross-check FULL two ways: $∣ LEFT ∣ + ∣ RIGHT ∣ - ∣ INNER ∣ = 500 + 330 - 120 = 710$ . ✅ (Note this identity assumes a one-to-one match; with fan-out the counts multiply — see GROUP BY and Aggregation and Indexes for how engines cost this.)

Wrap-up: the matrix is complete

Recall Which cell did each example cover?

C1 INNER ::: Ex 1 C2 LEFT + padding ::: Ex 2 C3 anti-join (IS NULL) ::: Ex 3 C4 RIGHT / orphan parent ::: Ex 4 C5 FULL OUTER union ::: Ex 5 C6 NULL-key degeneracy ::: Ex 6 C7 CROSS grid, size m×n ::: Ex 7 C8 SELF join + dedup ::: Ex 8 C9 ON-vs-WHERE trap ::: Ex 9 C10 count from the formula ::: Ex 10

Back to the parent topic · related: Normalization, Foreign Keys, NULL semantics in SQL, Relational Algebra.