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

Employees (E) — the left table

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

Departments (D) — the right table

dept_id	dept_name
10	Sales
30	Legal

---

Step 1 — Lay out every possible pair (the Cartesian product)

WHAT. We ignore all conditions for a moment and build the full grid: each of the 3 employees glued to each of the 2 departments.

WHY. Because a join's job is to decide which pairings are valid. You cannot decide until you can see all candidate pairings. Starting from "everything" is honest — we throw things away later, never sneak things in. This is exactly the Relational Algebra definition of a join.

PICTURE. Rows = employees, columns = departments. Each cell is one glued pair. The count is $\underset{\text{3 rows}}{\underbrace{|E|}}\times \underset{\text{2 rows}}{\underbrace{|D|}}=\underset{\text{cells in the grid}}{\underbrace{6}}.$ Here $|E|$ means "how many rows in E", $\times$ is ordinary multiplication (every left row meets every right row), and $6$ is the number of cells you must inspect.

---

Step 2 — Ask the matching question (apply the predicate $\theta$)

WHAT. We now walk every one of the 6 cells and ask the predicate $\theta :e.\text{dept\_id}=d.\text{dept\_id}.$ Here the left side $e.\text{dept\_id}$ is the department number written on the employee row; the right side $d.\text{dept\_id}$ is the key of the department row. The = asks "are these the same number?"

WHY. This is the whole point of a join: reconnect data that Normalization deliberately split apart (the employee stores only a dept_id, the name lives once in Departments). The predicate is the glue that says which employee belongs to which department. This is where Foreign Keys and Indexes earn their keep — the engine uses them to answer $\theta$ fast.

PICTURE. We shade each cell green = passes, grey = fails. Watch Cara's whole row: her dept_id is NULL, and NULL = 10 and NULL = 30 are both not-true, so every cell in her row fails — a NULL key can never match, not even another NULL.

Only one cell survives: (Asha, 10) meets (Sales, 10). That's $\theta$ = true exactly once.

---

Step 3 — Keep the survivors (this set is the INNER JOIN)

WHAT. Collect only the green cells. In relational-algebra notation: $\text{INNER}={\sigma }_{\theta }(E\times D).$ Reading it left to right: $\sigma$ (Greek sigma) is the selection operator — "keep only rows where…"; the subscript $\theta$ is the question it keeps by; and $E\times D$ is our grid from Step 1. So the formula literally says "from the full grid, keep the pairs that pass $\theta$."

WHY. An INNER JOIN's promise is "show me only pairs that truly match." That is exactly the green set — nothing added, nothing padded.

PICTURE. The grid fades except the single surviving pair, which becomes one clean output row: Asha — Sales.

Recall Why did Bilal and Legal vanish here?

Bilal's dept_id 20 has no matching department row; Legal (dept 30) has no matching employee. Neither ever produced a green cell, so INNER drops them. ::: INNER keeps only matched pairs; any row without a partner is discarded.

---

Step 4 — Find the lonely rows on each side

WHAT. Before we can "keep everyone," we must name who got left out. Scan Step 2's grid by whole rows and whole columns:

• Left-lonely (employees with no green cell in their row): Bilal, Cara → $2$ rows.
• Right-lonely (departments with no green cell in their column): Legal → $1$ row.

WHY. The outer joins (LEFT, RIGHT, FULL) are defined by rescuing these lonely rows. So we must identify them precisely — this is the bookkeeping that makes the counting come out right.

PICTURE. The grid with an orange bracket down the side marking lonely employee-rows, and a plum bracket across the top marking lonely department-columns.

---

Step 5 — Rescue the left-lonely rows with NULL padding → LEFT JOIN

WHAT. Take the INNER survivor, then add back each left-lonely employee — but they have no department, so we fill the department columns with NULL: $\text{LEFT}=\underset{\text{1 matched row}}{\underbrace{\text{INNER}}}\cup (\underset{\text{Bilal, Cara}}{\underbrace{\text{left-lonely}}}\times \{\text{NULL}\}).$ The $\cup$ is set-union ("throw both collections together"). The $\times \{\text{NULL}\}$ means "glue a row of NULLs where the department should be."

WHY. A LEFT JOIN's promise: "never lose a left row." Padding with NULL is the honest way to keep Bilal and Cara on screen while admitting "we found no department for them."

PICTURE. Three output rows: Asha–Sales (real), Bilal–NULL, Cara–NULL. The NULL cells are drawn hollow/dashed so you can see they are placeholders, not data.

$|\text{LEFT}|=\underset{\text{matched}}{\underbrace{1}}+\underset{\text{left-lonely}}{\underbrace{2}}=3.$

---

Step 6 — Mirror it: rescue right-lonely rows → RIGHT JOIN

WHAT. Same move, other side. Keep the INNER survivor, then add each right-lonely department with NULL employee columns: $\text{RIGHT}=\text{INNER}\cup (\{\text{NULL}\}\times \underset{\text{Legal}}{\underbrace{\text{right-lonely}}}).$

WHY. A RIGHT JOIN's promise: "never lose a right row." Legal has no employee, so it survives as NULL–Legal.

PICTURE. Two output rows: Asha–Sales, NULL–Legal. Notice it is the left-side cells that are now hollow.

$|\text{RIGHT}|=\underset{\text{matched}}{\underbrace{1}}+\underset{\text{right-lonely}}{\underbrace{1}}=2.$

---

Step 7 — Rescue both piles → FULL OUTER JOIN

WHAT. Keep the match and both lonely piles at once: $\text{FULL}=\text{LEFT}\cup \text{RIGHT}=\text{INNER}\cup \text{left-lonely}\cup \text{right-lonely}.$ The single shared match (Asha–Sales) is counted once — that's what the union $\cup$ guarantees (no duplicates).

WHY. FULL's promise: "lose nobody, from either table." It is the most generous join — every employee and every department appears, with NULLs wherever a partner is missing.

PICTURE. Four output rows stacked: Asha–Sales, Bilal–NULL, Cara–NULL, NULL–Legal. Real cells solid, missing cells hollow.

$|\text{FULL}|=\underset{\text{matched}}{\underbrace{1}}+\underset{\text{left-lonely}}{\underbrace{2}}+\underset{\text{right-lonely}}{\underbrace{1}}=4.$

---

Step 8 — The degenerate corners (never let the reader hit an unshown case)

WHAT & WHY. Real data throws odd inputs. Here is what the same machine does at every extreme, so nothing surprises you.

Input situation	What the grid does	Result
One table is empty ($	D	= 0$)
Every row matches	Every cell in each row green	LEFT = RIGHT = FULL = INNER (no lonely rows exist)
Nothing matches ($\theta$ always false)	Whole grid grey	INNER empty; LEFT = all left+NULL, RIGHT = all right+NULL, FULL = both
Join key is NULL (Cara)	That row's cells all fail ($\text{NULL}=x$ never true)	Dropped by INNER; kept-with-NULL by LEFT/FULL
CROSS on big tables	Grid is fully kept, $m\times n$ cells	10k × 10k = 100 million rows — the "accidental Cartesian explosion"

PICTURE. Two mini-grids side by side: an empty-right table (0 columns of cells → LEFT still saves its rows via padding) and an all-match table (every cell green → all joins coincide).

---

The one-picture summary

Everything above is a single pipeline with a fork at the end. Read it top to bottom: build the grid, ask $\theta$, then choose what to do with the lonely rows.

Recall Feynman retelling — the whole walkthrough in plain words

Picture a giant seating grid: every kid down the side, every lunch-table across the top, one square for each kid-meets-table combo. That grid of all squares is the CROSS JOIN — every combo, no questions asked. Now ask one question at each square: "is this the kid's actual table?" Colour the yes-squares green. Cara has no table written down (a blank/NULL), so none of her squares can be green — a blank never matches anything. Sweep up only the green squares: that's the INNER JOIN — just the true kid-and-their-table pairs (only Asha–Sales here). Then look for the lonely ones. Some kids never got a green square (Bilal, Cara). Some tables had no kid (Legal). To rescue kids, list each lonely kid and write "no table" beside them — that's the LEFT JOIN. To rescue tables, list each empty table with "nobody" beside it — that's the RIGHT JOIN. Do both at once and you get the FULL OUTER JOIN. And if you only had one list but wanted to compare kids to other kids at the same table, you'd use the list twice — the SELF JOIN. One grid, one question, and a choice about the lonely rows: that is every join there is.