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

WHY do joins exist?

We'll use two running tables:

Employees

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

Departments

dept_id	dept_name
10	Sales
30	Legal

Note the deliberate mismatches: emp Cara has no department; dept 30 (Legal) has no employee; dept 20 exists in Employees but not in Departments.

The six joins

Definition INNER JOIN

Returns only rows where the condition matches on BOTH sides. Unmatched rows on either side are dropped.

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

Result: only Asha–Sales (dept_id 10 is the sole pair present in both). Bilal (20 not in Dept), Cara (NULL), Legal (no emp) all vanish.

Definition LEFT (OUTER) JOIN

Returns all rows from the LEFT table; matched right columns fill in, unmatched right columns become NULL.

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

Result: Asha–Sales, Bilal–NULL, Cara–NULL. Every employee survives.

Definition CROSS JOIN

The Cartesian product: every left row paired with every right row. No ON condition. If left has $m$ rows and right has $n$ rows, output has $m \times n$ rows.

SELECT e.name, d.dept_name FROM Employees e CROSS JOIN Departments d;

Result: $3 \times 2 = 6$ rows.

Definition SELF JOIN

A table joined to itself using two aliases. Used for hierarchies (employee → manager) or comparing rows within one table.

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

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

HOW joins are built from first principles (Derivation)

Formula Every join = CROSS JOIN then FILTER then PAD

Step 1 — Cartesian product: form all $m \times n$ pairs. $\text{Employees} \times \text{Departments},\quad |{\cdot}| = m \cdot n = 3 \cdot 2 = 6$ Step 2 — Filter by the ON predicate $\theta$ (e.g. e.dept_id = d.dept_id). Surviving set = INNER JOIN: $\text{INNER} = \sigma_{\theta}(\text{E} \times \text{D})$ Step 3 — Pad the unmatched outer rows with NULLs: $\text{LEFT} = \text{INNER} \;\cup\; \big(\text{unmatched left rows}\times\{\text{NULL}\}\big)$ $\text{RIGHT} = \text{INNER} \;\cup\; \big(\{\text{NULL}\}\times\text{unmatched right rows}\big)$ $\text{FULL} = \text{LEFT} \;\cup\; \text{RIGHT}$ Why this works: the database engine literally conceptualizes joins this way (relational algebra). The optimizer then uses indexes/hash tables so it doesn't actually materialize all $m\times n$ pairs — but the meaning is exactly the filtered Cartesian product.

Counting check

For our data: INNER has 1 row. Left-unmatched = {Bilal, Cara} = 2. Right-unmatched = {Legal} = 1. $|\text{LEFT}| = 1+2 = 3,\quad |\text{RIGHT}| = 1+1 = 2,\quad |\text{FULL}| = 1+2+1 = 4.$

Worked examples

Worked example Q1: Find employees with NO department ("anti-join")

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

Why LEFT JOIN? We need to keep employees even when no department matches. Why WHERE d.dept_id IS NULL? Padded rows have NULL in right columns — filtering on that NULL isolates the unmatched ones. Result: Bilal, Cara.

Worked example Q2: Departments with no employees

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

Why this step? Putting Departments on the LEFT keeps all departments; the NULL-test finds the orphans. Result: Legal.

Worked example Q3: Pair employees who share a department (SELF JOIN)

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 you'd get each pair twice (A,B and B,A) and self-pairs (A,A). The strict inequality keeps each unordered pair once.

Worked example Q4: Generate a size chart — every product × every size (CROSS)

SELECT p.name, s.size FROM Products p CROSS JOIN Sizes s;

Why CROSS? We genuinely want every combination — no matching condition exists. Useful for generating grids/calendars.

Common mistakes (Steel-manned)

Common mistake Putting the join condition in WHERE breaks LEFT JOIN

Wrong but tempting:

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

Why it feels right: "I'm just filtering for Sales, and it's still a LEFT JOIN." Why it's wrong: the padded NULL rows have d.dept_name = NULL, and NULL = 'Sales' is not true, so WHERE deletes them — your LEFT JOIN silently degrades into an INNER JOIN. Fix: put right-table conditions in the ON clause: ON e.dept_id = d.dept_id AND d.dept_name = 'Sales'. ON filters before padding; WHERE filters after.

Recall Feynman: explain to a 12-year-old

Imagine two lists. List A: kids and the lunch-table number they sit at. List B: table numbers and what food is on each table. A join matches each kid to their table's food.

INNER: only show kids who sit at a real food-table (skip the kid sitting on the floor and the empty table).
LEFT: show every kid — if a kid has no table, write "no food."
RIGHT: show every table — even empty ones say "nobody here."
FULL: show every kid AND every table, blanks where missing.
CROSS: pretend every kid could sit at every table — list all combos.
SELF: match kids to other kids at the same table, using the one list twice.

Active recall

INNER JOIN returns which rows?

Only rows matching the ON condition on both tables; unmatched rows on either side are dropped.

LEFT JOIN keeps all rows of which table, and fills what for misses?

All rows of the LEFT table; unmatched RIGHT columns become NULL.

How do you rewrite A RIGHT JOIN B using LEFT?

B LEFT JOIN A (same result, swap order).

FULL OUTER JOIN equals the union of which two joins?

LEFT JOIN ∪ RIGHT JOIN.

A CROSS JOIN of m and n rows produces how many rows?

m × n (Cartesian product), with no ON condition.

Every join can be derived as which sequence of operations?

CROSS JOIN (Cartesian product) → FILTER by ON predicate (gives INNER) → PAD unmatched outer rows with NULL.

What is the standard pattern to find rows with NO match (anti-join)?

LEFT JOIN the other table, then WHERE IS NULL.

Why does a filter on the right table in WHERE turn a LEFT JOIN into an INNER JOIN?

Padded NULL rows fail the comparison (NULL = value is not true), so WHERE removes them after padding.

What clause should hold right-table conditions to preserve LEFT JOIN semantics?

The ON clause (filters before padding), not WHERE (filters after).

What is a SELF JOIN used for?

Joining a table to itself via two aliases — e.g. employee→manager hierarchies or comparing rows within one table.

Why does INNER JOIN ON e.dept_id = d.dept_id drop a row with NULL dept_id?

NULL never equals anything (even NULL), so a NULL key matches no row.

In a self-join pairing, why add a.id < b.id?

To avoid duplicate (A,B & B,A) pairs and self-pairs (A,A).

Connections

Relational Algebra — selection $\sigma$ , projection $\pi$ , Cartesian product $\times$ underlie joins.
Normalization — splits data across tables; joins reassemble it.
NULL semantics in SQL — why unmatched outer rows behave the way they do.
Indexes — how the optimizer avoids real $m\times n$ work (hash join, merge join, nested loop).
Foreign Keys — the relationships joins typically follow.
GROUP BY and Aggregation — often combined with joins.

Concept Map

Hinglish (regional understanding)

Intuition Hinglish mein samjho

Dekho, JOIN ka matlab simple hai: do tables ko ek matching condition se jodna. Normalization ki wajah se hum data alag-alag tables mein todte hain (taaki repeat na ho), aur JOIN usi data ko wapas jodta hai. Sabse important sawaal sirf ek hai: jab ek side ki row ka doosri side match nahi milta, tab uska kya karein? Bas yahi pure 6 joins ko define karta hai.

INNER mein sirf wahi rows aati hain jinka dono taraf match ho — baaki gayab. LEFT mein left table ki saari rows rehti hain, right ka match na mile to NULL bhar jata hai. RIGHT ulta hai (right table puri). FULL OUTER mein dono taraf ka sab kuch, jahan match na ho wahan NULL. CROSS mein koi condition hi nahi — har left row har right row ke saath (m × n combinations). SELF mein ek hi table ko do alias se khud se jodte hain, jaise employee aur uska manager same table mein ho.

Andar se har join ek hi formula hai: pehle Cartesian product (saare pairs), phir ON condition se filter (yeh INNER deta hai), phir bachi hui unmatched rows ko NULL se pad karo. Yaad rakho — agar tumhe "no match" wali rows chahiye (anti-join), to LEFT JOIN karo aur WHERE right_key IS NULL lagao.

Ek bada mistake se bacho: LEFT JOIN mein right table ki condition kabhi WHERE mein mat daalo, warna NULL rows hat jaayengi aur tumhara LEFT JOIN chupke se INNER JOIN ban jayega. Right table ki condition hamesha ON clause mein daalo. Yeh ek trick exam aur real coding dono mein paisa banwa deti hai.

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

WHY do joins exist?

The six joins

HOW joins are built from first principles (Derivation)

Counting check

Worked examples

Common mistakes (Steel-manned)

Active recall

Connections

Concept Map

Hinglish (regional understanding)

Go deeper — visual, from zero

Test yourself — Databases

Connections