Joins

Jr Codex DBMS Notes

Level: Beginner–Intermediate Prerequisites: Chapter 2: Querying with SELECT Time to complete: ~25 minutes

Table of Contents

1. Why Joins Exist
2. INNER JOIN
3. LEFT JOIN and Finding Absences
4. RIGHT, FULL and CROSS
5. Self Joins
6. Multi-Table Joins
7. The Four Join Mistakes
8. Summary & Next Steps

1. Why Joins Exist

Good design splits data across tables so nothing is stored twice (Module 4 makes this rigorous). Joins put it back together on demand.

The Trade
─────────────────────────────────────────
  Storing the student's name on every enrollment
  row would make queries simple and updates
  dangerous — change a name and you must find
  every copy.

  Storing it once and JOINING costs a little at
  read time and eliminates an entire class of
  inconsistency.

  Joins are the price of correctness, and the
  price is low because of indexes (Module 5).
─────────────────────────────────────────

2. INNER JOIN

Returns only rows that match on both sides.

SELECT s.name, c.title, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses    c ON e.course_id = c.id
ORDER BY s.name, c.title;

What Comes Back
─────────────────────────────────────────
  Asha   | Databases  | A
  Asha   | Networks   | B
  Asha   | Statistics | A
  Divya  | Databases  | B
  Divya  | Ethics     | NULL
  Karan  | Networks   | D
  ...

  Sam is ABSENT — no enrollments, so no matching
  rows. This is the defining behaviour of an inner
  join, and the source of most "where did my rows
  go" confusion.
─────────────────────────────────────────

ON vs WHERE
─────────────────────────────────────────
  ON     defines HOW the tables relate
  WHERE  filters the joined result

  For an INNER join the two are interchangeable in
  result. For an OUTER join they are NOT — see
  Section 7.

  Keep the relationship in ON and the filtering in
  WHERE. It reads better and it stays correct when
  someone later changes the join type.
─────────────────────────────────────────

3. LEFT JOIN and Finding Absences

Keeps every row from the left table, padding with NULL where there is no match.

SELECT s.name, c.title, e.grade
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses    c ON e.course_id = c.id
ORDER BY s.name;

Now Sam Appears
─────────────────────────────────────────
  Asha   | Databases  | A
  ...
  Sam    | NULL       | NULL      ← kept, padded
─────────────────────────────────────────

-- The anti-join: rows on the left with NO match on the right.
SELECT s.name
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;        -- only unmatched rows survive

The Anti-Join Pattern
─────────────────────────────────────────
  LEFT JOIN, then WHERE right_column IS NULL.

  This is how you answer every "which X has no Y"
  question:
    customers who never ordered
    products never sold
    users with no login in 90 days

  Test the right table's JOIN KEY for NULL, not
  some other column — a nullable column could be
  NULL in a matched row too, which would silently
  include rows that DO match.
─────────────────────────────────────────

4. RIGHT, FULL and CROSS

-- RIGHT JOIN: keeps all right-hand rows. Identical to a LEFT JOIN with the
-- tables swapped, which is why most codebases just use LEFT consistently.
SELECT c.title, e.student_id
FROM enrollments e
RIGHT JOIN courses c ON e.course_id = c.id;
 
-- FULL OUTER JOIN: keeps unmatched rows from BOTH sides.
SELECT s.name, c.title
FROM students s
FULL OUTER JOIN enrollments e ON s.id = e.student_id
FULL OUTER JOIN courses    c ON e.course_id = c.id;
 
-- CROSS JOIN: every combination. Deliberate here — a grid of all
-- student/course pairs, to find which are missing.
SELECT s.name, c.title
FROM students s
CROSS JOIN courses c;              -- 6 students × 4 courses = 24 rows

Practical Notes
─────────────────────────────────────────
  RIGHT JOIN is rarely used. Prefer LEFT and order
  your tables accordingly — mixing directions in
  one query is hard to read.

  FULL OUTER JOIN is genuinely useful for
  reconciliation ("what is in A but not B, and B
  but not A"). SQLite gained it recently; MySQL
  still lacks it and needs a UNION of two outer
  joins.

  CROSS JOIN is right when you want a complete
  grid — report scaffolding, date spines. An
  ACCIDENTAL cross join is Section 7's first
  mistake.
─────────────────────────────────────────

5. Self Joins

A table joined to itself, which requires aliasing — the ρ rename from Module 2, Chapter 3.

-- Pairs of students in the same city.
SELECT a.name AS student_a, b.name AS student_b, a.city
FROM students a
JOIN students b ON a.city = b.city
                AND a.id < b.id            -- avoids self-pairs AND mirrored duplicates
ORDER BY a.city;

Why a.id < b.id Rather Than a.id <> b.id
─────────────────────────────────────────
  With <>, you get BOTH (Asha, Meera) and
  (Meera, Asha) — each pair twice.

  With <, each unordered pair appears exactly once,
  and self-pairing is excluded for free.

  A small idiom worth remembering; it comes up
  whenever you enumerate pairs.
─────────────────────────────────────────

-- Self join on a hierarchy: employees and their managers.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;    -- LEFT keeps the CEO, who has no manager

6. Multi-Table Joins

-- CS-department courses taken by Pune students, graded A or B.
SELECT s.name, c.title, c.dept, e.grade
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses    c ON e.course_id = c.id
WHERE s.city = 'Pune'
  AND c.dept = 'CS'
  AND e.grade IN ('A','B')
ORDER BY s.name, c.title;

You Write the Order; the Optimiser Ignores It
─────────────────────────────────────────
  The join order in your SQL is a suggestion at
  most. The optimiser reorders joins freely,
  because relational algebra guarantees the result
  is the same (Module 2, Chapter 3).

  So write joins in the order that READS clearly —
  usually following the relationships outward from
  the main entity. Optimising by hand-ordering
  joins is wasted effort; Module 6 covers what
  actually influences the plan.
─────────────────────────────────────────

7. The Four Join Mistakes

1. THE ACCIDENTAL CROSS JOIN
─────────────────────────────────────────
  FROM students s, enrollments e     -- no ON!
  WHERE s.city = 'Pune'

  Every student paired with every enrollment.
  6 × 12 = 72 rows instead of the intended handful,
  and it grows multiplicatively with the data.

  Symptom: far too many rows, or a query that
  never finishes.

2. WHERE ON THE OUTER SIDE
─────────────────────────────────────────
  SELECT s.name, e.grade
  FROM students s
  LEFT JOIN enrollments e ON s.id = e.student_id
  WHERE e.grade = 'A';          -- ← silently kills the LEFT JOIN

  Sam's padded row has grade NULL, and NULL = 'A'
  is UNKNOWN, so the WHERE drops him. You now have
  an INNER join with extra steps.

  FIX: put the condition in ON.
    LEFT JOIN enrollments e
      ON s.id = e.student_id AND e.grade = 'A'

3. FAN-OUT INFLATING AGGREGATES
─────────────────────────────────────────
  Joining a one-to-many relationship MULTIPLIES
  rows on the "one" side.

  Join students to their 3 enrollments and each
  student's row appears 3 times — so SUM(s.marks)
  now triple-counts.

  FIX: aggregate the many-side FIRST in a subquery
  (Chapter 5), then join the result.

4. COUNT(*) IN AN OUTER JOIN
─────────────────────────────────────────
  COUNT(*) counts ROWS, including the NULL-padded
  ones — so Sam counts as 1 enrollment.

  COUNT(e.course_id) counts NON-NULL values, so
  Sam correctly counts as 0.

  In an outer join, count a COLUMN from the outer
  side, never *.
─────────────────────────────────────────

8. Summary & Next Steps

Key Takeaways

• Inner joins drop rows without a match on either side; left joins keep the left rows padded with NULL, which is how absences become visible.
• The anti-join — LEFT JOIN then WHERE right.key IS NULL — answers every "which X has no Y" question, and must test the join key rather than any nullable column.
• A filter on the outer table placed in WHERE rather than ON silently converts a left join into an inner join.
• Joining a one-to-many relationship multiplies rows, so aggregates over the "one" side over-count unless the many side is aggregated first.

Concept Check

1. Sam disappears from an inner join but appears in a left join. Explain both, in one sentence each.
2. Why must an anti-join test the right table's join key for NULL rather than any convenient column?
3. In a left join, why does COUNT(*) give the wrong answer where COUNT(e.course_id) gives the right one?

Next Chapter

→ Chapter 4: Aggregation and Grouping

Jr Codex — 1-on-1 Personalized Coaching | Back to Module Index | Back to DBMS Index