Joins: Inner, Left, Right, Full, and Cross

Relational data is split across tables on purpose — teams here, players there — and joins are how you bring it back together to answer questions that span those tables. A join combines rows from two tables based on a related column, and mastering the different join types is the single biggest step from writing trivial single-table queries to genuinely working with a relational model.

The join type determines what happens to rows that have no match on the other side. An inner join keeps only matching pairs; a left join keeps every left row, filling unmatched right columns with nulls; right and full joins extend that to the other side and both sides. Choosing the wrong type silently drops or invents rows, so the distinction is not academic.

This lesson works through each join type, the role of the join condition, and the subtle interaction between join filters and the WHERE clause. The goal is to reach for the right join deliberately and to read a join's result without surprise — including the classic traps that turn a left join back into an inner join by accident.

Analogy🏏Cricket

🏏 Think of it like cricket: Just as a selection decision can depend on a derived benchmark — 'pick batters whose average exceeds the squad's average', which itself must first be computed — a subquery computes an inner result that the outer query then uses. The insight is that some questions are inherently two-stage: you must establish the benchmark before you can judge against it, and composing queries is how SQL expresses that dependency. Watch the selector actually do it: first he tallies every batter's runs and computes the squad average — that inner computation stands alone, needing nothing from the final decision — and only then does he walk the list judging each player against the number he just derived. That independence is what makes it an uncorrelated subquery: the database can compute the benchmark once, keep it, and reuse it for every row, exactly as the selector does not recompute the squad average per player. The composition also comes in shapes: a benchmark producing one number slots in where a value goes (a scalar subquery in WHERE), while a computed shortlist of qualifying players is itself a table the outer query can select from — a subquery in FROM. Two-stage question, two nested queries, dependency flowing inward-out.