Joins — INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF: Exercises

We reuse the two running tables everywhere below. Memorise the three deliberate mismatches — they are the entire point.

Employees

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

Departments

dept_id	dept_name
10	Sales
30	Legal

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

Here "left circle" = Employees, "right circle" = Departments. The overlap is the single matching pair (Asha ↔ Sales, on dept_id 10). Everything else is an unmatched row on one side.

Level 1 — Recognition

Goal: given a described behaviour, name the join. No queries yet — just the vocabulary.

L1.1 A report must list every department, showing employee names where they exist and leaving a blank otherwise. Which single join type does this, with Departments as the driving table?

L1.2 A query pairs every product with every colour to build a size-chart grid — there is no condition connecting them. Name the join.

L1.3 True or false: an INNER JOIN between Employees and Departments on dept_id will include Cara.

Recall Solution L1

L1.1 — A LEFT JOIN with Departments on the left (or equivalently a RIGHT JOIN with Departments on the right). "Keep every row of the driving table, pad the other side with NULL" is exactly the outer-join on the kept side.

L1.2 — A CROSS JOIN (the Cartesian product). No ON condition = every left row paired with every right row.

L1.3 — False. Cara's dept_id is NULL. The predicate e.dept_id = d.dept_id becomes NULL = 10, NULL = 30, … all of which evaluate to unknown, never true. INNER keeps only true rows, so Cara is dropped.

Level 2 — Application

Goal: translate a plain-English requirement into a correct query.

L2.1 Write a query listing each employee's name next to their department name, keeping every employee even those with no department.

L2.2 Write a query returning only employees who have no department (the "anti-join").

L2.3 Write a query returning departments that have no employees.

Recall Solution L2

L2.1 — Keep all employees ⇒ Employees on the LEFT:

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

Output: Asha–Sales, Bilal–NULL, Cara–NULL.

L2.2 — LEFT JOIN keeps the unmatched employees as padded rows; those padded rows have NULL in every right-table column, so test that NULL:

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

Output: Bilal, Cara (2 rows).

L2.3 — Same pattern, Departments on the LEFT:

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

Output: Legal (1 row).

Level 3 — Analysis

Goal: predict the exact row count and contents. Count on your fingers using the Venn picture.

L3.1 How many rows does Employees CROSS JOIN Departments produce, and why?

L3.2 Give the exact number of rows for INNER, LEFT, RIGHT, and FULL OUTER joins on dept_id for our data. Show the arithmetic.

L3.3 Consider this query. How many rows does it return, and which employees?

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';

Recall Solution L3

L3.1 — $m \times n = 3 \times 2 = 6$ rows. CROSS pairs every left row with every right row with no condition to prune anything.

L3.2 — Let the overlap (matched pairs) $= I$ , left-only unmatched $= L_{u}$ , right-only unmatched $= R_{u}$ .

Matches: only Asha↔Sales ⇒ $I = 1$ .
Left-unmatched employees: Bilal (dept 20 absent) and Cara (NULL) ⇒ $L_{u} = 2$ .
Right-unmatched departments: Legal ⇒ $R_{u} = 1$ .

$∣ INNER ∣ = I = 1$ $∣ LEFT ∣ = I + L_{u} = 1 + 2 = 3$ $∣ RIGHT ∣ = I + R_{u} = 1 + 1 = 2$ $∣ FULL ∣ = I + L_{u} + R_{u} = 1 + 2 + 1 = 4$

L3.3 — 1 row: Asha–Sales. Even though the join is LEFT, the WHERE d.dept_name = 'Sales' runs after padding. The padded rows (Bilal, Cara) carry d.dept_name = NULL, and NULL = 'Sales' is unknown (not true), so WHERE deletes them. The LEFT JOIN silently collapses to an INNER JOIN. This is the classic degrade — see the L3 trap.

Common mistake Trap at L3: "WHERE and ON are interchangeable"

Why it feels right: on an INNER JOIN, moving a condition between ON and WHERE gives identical results, so it seems to be pure style. Why it's wrong on outer joins: ON filters before the NULL-padding happens; WHERE filters after. A WHERE clause that references a nullable padded column re-inspects the manufactured NULLs and discards them. Fix: for right-table restrictions in a LEFT JOIN, put them in ON:

... LEFT JOIN Departments d
      ON e.dept_id = d.dept_id AND d.dept_name = 'Sales';

Now non-Sales matches simply fail to match (row survives, padded with NULL) instead of being deleted.

Level 4 — Synthesis

Goal: combine joins with self-joins, aggregation, and set logic.

L4.1 (SELF JOIN) Suppose every employee also has a manager_id pointing at another employee's emp_id. Write a query listing each employee next to their manager's name, including employees who have no manager (top of the hierarchy). State which join you used and why.

L4.2 (SELF JOIN, pairing) Using only the Employees table above, pair up employees who share the same dept_id, listing each unordered pair once. How many pairs result for our three-row data?

L4.3 (JOIN + aggregation) Write a query giving, for every department, the count of its employees — showing 0 for departments with none. Which join and which counting trick avoids counting a NULL as an employee? Predict the output.

Recall Solution L4

L4.1 — To keep managerless employees we must not drop the row when the manager lookup fails ⇒ LEFT (SELF) JOIN, joining Employees to a second alias of itself:

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

A plain INNER self-join would silently drop the CEO (whose manager_id is NULL). LEFT preserves them with manager = NULL.

L4.2 — Self-join with the strict inequality that keeps each unordered pair exactly once:

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;

Count the pairs: Asha(10), Bilal(20), Cara(NULL). No two employees share a non-NULL dept_id, and NULL never equals NULL, so 0 pairs. (If Bilal's dept were 10, we'd get exactly 1 pair: Asha–Bilal.)

L4.3 — Keep every department ⇒ Departments on the LEFT. Then use GROUP BY with COUNT(e.emp_id) — counting a non-null column of the employee side so padded NULLs count as 0:

SELECT d.dept_name, COUNT(e.emp_id) AS n_emps
FROM Departments d
LEFT JOIN Employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

Output: Sales → 1 (Asha), Legal → 0 (no employees).

Level 5 — Mastery

Goal: reason about equivalences, edge cases, and rebuild a join from primitives.

L5.1 (Equivalence) Rewrite Departments RIGHT JOIN Employees ON d.dept_id = e.dept_id as a LEFT JOIN, and confirm the two produce the same multiset of rows. How many rows is that here?

L5.2 (FULL OUTER from primitives) Some databases (e.g. older MySQL) have no FULL OUTER JOIN. Reconstruct it using a LEFT JOIN, a RIGHT JOIN, and UNION. Predict the exact 4 output rows for our data.

L5.3 (Derivation) Show that INNER JOIN equals a filtered CROSS JOIN. Using $σ$ for "filter/select rows where a condition holds" from Relational Algebra, write the identity and confirm the row count matches Step-by-step against the Cartesian product of 6 rows.

L5.4 (Edge case) In a CROSS JOIN, what happens to the output row count if one of the two tables is empty (0 rows)? Explain via the multiplication.

Recall Solution L5

L5.1 — A RIGHT JOIN B keeps all of B; the same set of kept rows is B LEFT JOIN A:

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

"Keep all Employees" (the right side of the original) becomes "Employees on the left." Rows: $I + L_{u} = 1 + 2 = 3$ . Identical multiset.

L5.2 — FULL = LEFT ∪ RIGHT. Emulate:

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

The LEFT half gives {Asha–Sales, Bilal–NULL, Cara–NULL}; the RIGHT half gives {Asha–Sales, NULL–Legal}. UNION removes the duplicate Asha–Sales. Final 4 rows: Asha–Sales, Bilal–NULL, Cara–NULL, NULL–Legal. This matches $∣ FULL ∣ = I + L_{u} + R_{u} = 4$ . (Caveat: UNION dedupes; if two genuinely distinct rows happened to be identical you'd lose one. Robust emulations use UNION ALL with an anti-join guard — but for these keys UNION is exact.)

L5.3 — The identity, with $θ$ = the ON predicate e.dept_id = d.dept_id: $INNER_{θ} (E, D) = σ_{θ} (E \times D)$ The Cartesian product $E \times D$ has $3 \times 2 = 6$ rows. Apply $θ$ to each pair; only the pair (Asha, Sales) satisfies 10 = 10. Every other pair fails (Bilal's 20 matches neither 10 nor 30; Cara's NULL matches nothing). Surviving rows $= 1 = ∣ INNER ∣$ . ✔

L5.4 — Output rows $= m \times n$ . If either table is empty, that factor is 0, so the product is $0$ : a CROSS JOIN with an empty table yields zero rows. There is nothing to pair with, so the grid is empty.

Recall One-question self-test

Why does moving a right-table condition from WHERE to ON change a LEFT JOIN's result but not an INNER JOIN's? ::: ON filters before NULL-padding, WHERE after. On INNER there is no padding, so timing is irrelevant; on LEFT the padded rows are deleted by a WHERE that inspects their manufactured NULLs.

Active recall

Row count of INNER join on our data

1 (Asha–Sales only).

Row count of LEFT join (Employees left) on our data

3 (Asha–Sales, Bilal–NULL, Cara–NULL).

Row count of RIGHT join on our data

2 (Asha–Sales, NULL–Legal).

Row count of FULL OUTER on our data

Rows from CROSS JOIN of 3 employees and 2 departments

CROSS JOIN when one table is empty

0 rows (m × n with a zero factor).

Why does WHERE d.col = x degrade a LEFT JOIN to INNER?

Padded rows have NULL columns; NULL = x is not true, so WHERE deletes them (WHERE runs after padding).

To count 0 employees for empty departments, count which column?

COUNT(e.emp_id) — a non-null employee column, not COUNT(*).