queries & joins
Join Types
Combine rows from multiple tables — INNER for matches only, LEFT for matches plus unmatched-left, FULL for both sides.
Joins are how a normalised database reassembles data that has been deliberately split across tables. Because the data model spreads related facts — a customer here, their orders there — almost every meaningful query joins tables back together, and choosing the right join type is about deciding what to do with rows that have no match on the other side.
INNER = both lists agree. LEFT = "everyone on the invitee list, with their RSVP if any." FULL = "everyone on either list."
Key Concepts
1
An INNER JOIN returns only the rows where the join condition matches in both tables, dropping anything unmatched on either side. A LEFT JOIN (left outer) keeps every row from the left table and fills the right-side columns with NULL where there is no match — the natural choice for "all customers and their orders, including customers with none." A RIGHT JOIN is its mirror, and a FULL OUTER JOIN keeps unmatched rows from both sides. A CROSS JOIN produces the Cartesian product of the two tables, every combination, which is occasionally intentional and frequently an accidental performance disaster. Behind the scenes the database picks a physical strategy — nested loops for small inputs or an indexed inner side, a hash join for large unindexed equality joins, or a merge join when both inputs are already sorted — which is what EXPLAIN shows you.
INNER JOINLEFT JOINNULLRIGHT JOINFULL OUTER JOIN
2
The bugs interviewers like to surface come from NULL handling in outer joins. Putting a condition on the right table in the WHERE clause of a LEFT JOIN silently turns it back into an inner join, because NULL fails the condition and those preserved rows get filtered out — such conditions belong in the ON clause instead. The other classic is fan-out: joining to a table with multiple matching rows multiplies the result and inflates any SUM or COUNT, which is why aggregations over joins often need careful grouping or pre-aggregation.
NULLWHERELEFT JOINONSUM