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

This page assumes nothing. Before you can read the parent note Joins, you need to know exactly what a table, a row, a key, a NULL, a "match", and a handful of symbols mean. We introduce no symbol before defining it — every piece of notation gets a plain-words meaning and a picture in the section where it first appears, in the order the ideas depend on each other.

1. A table = a grid of facts

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

Look at the figure. The whole blue-bordered box is the Employees table. The horizontal strip highlighted in orange is one row — it is Asha, and everything true about Asha. The vertical strip in green is one column — the name property across all employees. Where they cross (the darker square) is one cell, holding the single value Asha.

2. Normalization — why the data is split in the first place

The figure shows the two worlds side by side. On the left (red) is the un-normalized table: "Sales" is copied onto every Sales employee — three copies of the same fact. On the right (green) is the normalized pair of tables: the word "Sales" appears once, and each employee stores only a tiny reference to it.

3. Keys — the values that let rows point at each other

In the figure, the arrow is the whole point. Employees.dept_id = 10 is a foreign key: it is not the department's name, it is a pointer to the department whose primary key dept_id = 10. The name "Sales" lives once, in Departments — exactly the normalization idea from section 2, now made concrete by the id.

4. NULL — the value that means "no value"

The single most surprising rule, which controls almost every join subtlety:

5. Two tables, three kinds of rows

Before any symbols, internalise this picture — it is the whole topic.

Two overlapping circles (a Venn diagram). The left circle is all Employees rows; the right is all Departments rows. They overlap where a foreign key finds its primary key.

Overlap (purple): rows that match — Asha ↔ Sales. These are the only rows an INNER JOIN keeps.
Left-only (blue): left rows with no partner — Bilal (dept 20 missing), Cara (dept NULL).
Right-only (orange): right rows with no partner — Legal (no employee).

Every join in the parent note is just a choice of which coloured regions to keep. Memorise the three regions and the six joins become trivial.

6. The symbols the derivation uses

The parent note's derivation writes joins in the language of Relational Algebra. Here is every symbol, earned one at a time, and then all six joins written in that language.

6a. $\times$ — the Cartesian product (this IS the CROSS JOIN)

The grid in the figure is the product. Down the side: the 3 employees. Across the top: the 2 departments. Each of the $3 \times 2 = 6$ inner cells is one pair — one possible combined row. This is the raw material a join filters. Notice: nothing has matched yet — every combination exists, even nonsensical ones like "Asha paired with Legal".

6b. $θ$ — the join condition (predicate)

Why a symbol instead of the words? Because the shape of the join is the same whatever the condition is — equality, less-than, or a compound AND. Writing $θ$ lets us describe all of them at once.

6c. $σ_{θ}$ — selection (filtering)

Now the INNER join reads in plain English: $INNER = σ_{θ} (A \times B)$

"Take every employee–department pair ( $A \times B$ ), then keep only the pairs where the ids match ( $σ_{θ}$ )." That surviving set — the purple overlap in figure s03 — is the INNER JOIN. Nothing mysterious: product, then filter.

6d. Set literals ${NULL}$ and union $\cup$ — padding the lonely rows

With these, every outer join is INNER plus the lonely rows padded with ${NULL}$ :

The figure shades each join's kept regions on the same Venn picture so you can see that RIGHT is the exact mirror of LEFT (swap blue for orange), and FULL is simply both outer regions added to the INNER core.

6e. $∣ \cdot ∣$ — cardinality (counting rows)

Now the parent's counting check is fully readable. With INNER $= 1$ , $L_{u} = {Bilal, Cara}$ so $∣ L_{u} ∣ = 2$ , and $R_{u} = {Legal}$ so $∣ R_{u} ∣ = 1$ : $∣ LEFT ∣ = 1 + 2 = 3, ∣ RIGHT ∣ = 1 + 1 = 2, ∣ FULL ∣ = 1 + 2 + 1 = 4.$

7. Aliases, dotted names, and the SELF JOIN

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

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

Here $θ$ is a.dept_id = b.dept_id AND a.emp_id < b.emp_id. Why a.emp_id < b.emp_id? Without it every unordered pair appears twice (A,B and B,A) and each row pairs with itself (A,A). The strict inequality keeps each pair once and removes self-pairs. Result on our data: with only Asha in dept 10 (Bilal is dept 20, Cara is NULL, and NULL never matches), no two employees share a real department — the result is empty. That empty result is itself informative: it proves the NULL rule and the missing dept-20 both bite here.

Prerequisite map

Related deeper topics once you're comfortable: Indexes (how the engine avoids materialising the full product), GROUP BY and Aggregation (what you often do after joining), and Foreign Keys (which guarantee matches exist).

Equipment checklist

Cover the right side; can you answer each before reading on?

What is a row versus a column?

A row is one thing (all facts about one entity); a column is one property shared across all things.

What does normalization do, and why does it force joins to exist?

It stores each fact in one place; the resulting split means related data lives in separate tables, so a join is needed to reconnect it.

What is a foreign key?

A column that stores a primary-key value belonging to another table — a pointer to a row elsewhere.

Is NULL the same as 0 or ""?

No — NULL means absent/unknown; it is a distinct marker, not a value.

What does NULL = NULL evaluate to?

UNKNOWN — not true. You must use IS NULL to test for it.

Why does an INNER JOIN drop a row whose key is NULL?

The match condition uses =, and NULL = anything is never true, so the row finds no partner.

What does

A \times B

(Cartesian product) produce and how many rows?

Every pairing of a row from

A

with a row from

B

;

m \cdot n

rows.

Which join is exactly

A \times B

with no filter?

CROSS JOIN.

σ_{θ} (R)

, what do

σ

and

θ

mean?

σ

= selection (keep rows passing the test);

θ

= the matching predicate/condition.

Write INNER JOIN in relational-algebra words.

σ_{θ} (A \times B)

— form all pairs, then keep only those satisfying the condition.

What do the braces in

{NULL}

mean?

A set literal — the one-row set containing a single all-NULL stand-in row, used to pad the missing side.

Write RIGHT and FULL in terms of INNER,

L_{u}

R_{u}

RIGHT = INNER \cup ({NULL} \times R_{u})

;

FULL = INNER \cup (L_{u} \times {NULL}) \cup ({NULL} \times R_{u})

What does

\cup

do in the outer-join formulas?

Union — combines the matched pairs with the padded lonely rows into one result set.

What does

∣ R ∣

mean?

The number of rows (cardinality) in

R

How is a SELF JOIN written in relational algebra?

σ_{θ} (E as A \times E as B)

— the same table crossed with a relabelled copy of itself, then filtered.

Why are table aliases essential in a self join?

They give the single table two distinct names so a.col and b.col refer to different copies unambiguously.