Session 6 — Joins II: RIGHT, FULL, self-joins, multi-key joins

You will complete the join family. RIGHT JOIN mirrors LEFT (keep all right rows — usually rewritten by flipping the tables); FULL JOIN keeps both sides and NULLs whichever side lacks a match; self-joins join a table to itself with two aliases, powering patterns like employee/manager and author/mentor; and multi-key joins match on (order_id, line_no)-style composite keys with AND in the ON clause.

Plan: Core ideas → worked joins → Quiz.

Time: about 60–70 minutes if you draw the two table copies for every self-join.

Session 6 — Joins II: RIGHT, FULL, self-joins, multi-key joins

The one-paragraph story

INNER and LEFT cover the common cases, but three more tools finish the family. RIGHT JOIN is LEFT mirrored (keep all right-table rows), FULL JOIN keeps both sides and NULLs whichever side lacks a match, and a self-join joins a table to itself — indispensable for rows that reference other rows of the same table, like an employee whose boss is another employee. Finally, multi-key joins match on more than one column when a single key is not enough (a sale line identified by both its order and its line number). Together these cover essentially every pairing a relational schema can express.

---

RIGHT JOIN: the mirror of LEFT

SELECT b.title, a.name
FROM books b
RIGHT JOIN authors a ON b.author_id = a.author_id;

Every right-table row (every author) appears. Authors with matching books pair up; an author with no books at all appears once with title = NULL.

Why learn it? Because flipping the table order makes it rarely necessary — books RIGHT JOIN authors equals authors LEFT JOIN books. Most teams standardise on LEFT and write the "all of" table on the left. Recognise RIGHT when you see it; prefer LEFT when you write it.

---

FULL JOIN: keep both sides

SELECT b.title, a.name
FROM books b
FULL JOIN authors a ON b.author_id = a.author_id;

Every book and every author appears. Matched pairs combine; orphan books appear with name = NULL; authorless authors (no books) appear with title = NULL. FULL JOIN answers "show me everything on both sides, matched where possible" — the complete reconciliation view. (Not every engine supports it, but the big ones do.)

---

Self-join: a table meets itself

Some rows point at other rows of the same table. The classic shape — an employees table where each employee has a manager_id that is another employee's id:

CREATE TABLE staff (
    staff_id   INTEGER PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER REFERENCES staff(staff_id)
);

To show every employee with their manager's name, join staff to itself. The trick is two aliases, so the two copies of the table play different roles:

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

• The copy aliased e ("employee") is the left table — every employee appears.
• The copy aliased m ("manager") is the right table — matched when the employee's manager_id equals the manager's staff_id.
• The LEFT keeps the boss too: the CEO's manager_id is NULL, so she appears with manager = NULL.

Aliases are not optional here — you cannot join a table to itself without naming the two copies distinctly.

The bookshop mirrors this pattern: an author might have a mentor_id pointing at another author. "Each author with their mentor" is the same self-join shape:

SELECT a.name AS author, ment.name AS mentor
FROM authors a
LEFT JOIN authors ment ON a.mentor_id = ment.author_id;

---

Multi-key joins: matching on more than one column

When the schema identifies a row by a pair of columns, the join must match both. Imagine order lines:

CREATE TABLE orders (
    order_id  INTEGER,
    line_no   INTEGER,
    book_id   INTEGER,
    qty       INTEGER,
    PRIMARY KEY (order_id, line_no)
);

An order line is identified by (order_id, line_no), not by either alone. Joining lines to another table that refers to them needs both columns:

SELECT l.*, p.note
FROM order_lines l
JOIN processing_notes p
  ON l.order_id = p.order_id
 AND l.line_no  = p.line_no;

AND-ing the two conditions matches on the pair. Multi-key joins are exactly as common as composite primary keys — and Session 12 shows why composite keys exist in the first place.

---

Choosing a join: the decision table

| Want | Use | |------|-----| | Only matched pairs | INNER JOIN | | All left rows, matched when possible | LEFT JOIN | | All right rows, matched when possible | RIGHT JOIN (or flip the tables and use LEFT) | | Everything on both sides | FULL JOIN | | Rows referencing the same table | self-join (two aliases) | | Rows identified by a composite key | multi-key ON with AND |

---

Pitfalls to watch for

• RIGHT JOIN is rarely needed — swap the tables and write LEFT; pick one style and stick to it.
• Self-joins require two aliases. Writing staff JOIN staff ON ... without them is a syntax error.
• ON conditions chain with AND for composite keys; a single missing condition quietly widens the join into extra matches.
• FULL JOIN output reads oddly at first — NULLs on either side are the point; they mark rows that have no counterpart.

Session 6 — Worked examples

The bookshop data plus the staff table from the concept:

authors: (1, Alina Voss, Germany), (2, Mateo Rios, Argentina), (3, Priya Anand, India), (4, Jonas Weber, Germany)

books: (10, The Glass Harbour, 1, fiction, 24.99, 2019), (11, Static Echoes, 1, fiction, 19.50, 2021), (12, River Clock, 2, mystery, 22.00, 2018), (13, Salt Roads, 3, history, 31.00, 2020), (14, North of Noon, 3, history, 27.50, 2017), (15, Prairie Circuit, 4, mystery, 18.00, 2022)

staff: (1, Mira Chen, NULL), (2, Leo Park, 1), (3, Ava Stone, 2), (4, Tom Hale, 2)

Example A — RIGHT JOIN

SELECT b.title, a.name
FROM books b
RIGHT JOIN authors a ON b.author_id = a.author_id
ORDER BY a.author_id;

Every author appears; each is paired with her matching books:

| title | name | |-------|------| | The Glass Harbour | Alina Voss | | Static Echoes | Alina Voss | | River Clock | Mateo Rios | | Salt Roads | Priya Anand | | North of Noon | Priya Anand | | Prairie Circuit | Jonas Weber |

All four authors have books, so no NULL rows. If we had a fifth author with no books, she would appear as (NULL, <her name>). Same output as the flipped authors LEFT JOIN books — which is the recommended way to write it.

---

Example B — FULL JOIN

SELECT b.title, a.name
FROM books b
FULL JOIN authors a ON b.author_id = a.author_id;

Imagine book 16 with author_id = 99 (orphan) and author 5 with no books:

| title | name | |-------|------| | The Glass Harbour | Alina Voss | | Static Echoes | Alina Voss | | River Clock | Mateo Rios | | Salt Roads | Priya Anand | | North of Noon | Priya Anand | | Prairie Circuit | Jonas Weber | | Book 16 | NULL | | NULL | Author 5 |

The two NULL rows are the whole point: one side's rows with no counterpart on the other. With INNER, both would vanish; with LEFT, only Book 16 would show.

---

Example C — Self-join: staff and their managers

SELECT e.name AS employee, m.name AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.staff_id
ORDER BY e.staff_id;

Pair each staff row (as e) with the staff row whose staff_id equals e.manager_id:

| employee | manager | |----------|---------| | Mira Chen | NULL | | Leo Park | Mira Chen | | Ava Stone | Leo Park | | Tom Hale | Leo Park |

• Ava's manager_id = 2 → Leo Park (staff_id = 2) ✓.
• Mira's manager_id is NULL → no match → NULL (she is the top of the tree).

Trace discipline: draw the two copies of the table with different shading — the e copy as the row you are reading, the m copy as the lookup table — then match manager_id → staff_id.

---

Example D — Self-join on the bookshop: author mentors

Add a mentor_id to authors: author 2's mentor is author 1, author 3's mentor is author 1.

SELECT a.name AS author, ment.name AS mentor
FROM authors a
LEFT JOIN authors ment ON a.mentor_id = ment.author_id;

| author | mentor | |--------|--------| | Alina Voss | NULL | | Mateo Rios | Alina Voss | | Priya Anand | Alina Voss | | Jonas Weber | NULL |

Same shape as Example C: the self-reference (one author pointing at another) is a self-join.

---

Example E — Multi-key join

Order lines identified by (order_id, line_no):

| order_id | line_no | book_id | qty | |----------|---------|---------|-----| | 200 | 1 | 10 | 2 | | 200 | 2 | 13 | 1 | | 201 | 1 | 15 | 3 |

Processing notes keyed the same way:

| order_id | line_no | note | |----------|---------|------| | 200 | 1 | 'packed' | | 200 | 2 | 'shipped' | | 201 | 1 | 'packed' |

SELECT l.order_id, l.line_no, l.qty, p.note
FROM order_lines l
JOIN processing_notes p
  ON l.order_id = p.order_id
 AND l.line_no  = p.line_no
ORDER BY l.order_id, l.line_no;

Every pair matches on both columns — (200,1), (200,2), (201,1). If the join had used only order_id, each order_id value would pair with every note of that order (fan-out ×2 for order 200) — a subtle corruption that the AND prevents.