SQL Joins

A SQL join combines rows from two inputs according to a predicate such as orders.customer_id = customers.id. At the logical level, that sounds simple: compare rows, keep the combinations that satisfy the condition, then shape the result according to the join type. In practice, joins are where data model assumptions, NULL semantics, and query planner mistakes become visible.

An INNER JOIN returns only matching pairs. If one customer has three orders, that customer row appears three times because the result contains one row per match, not one row per customer. A LEFT JOIN keeps every row from the left side and fills right-side columns with NULL when no match exists. RIGHT JOIN does the symmetric version, and FULL OUTER JOIN keeps unmatched rows from both sides. These are logical rules, not implementation details.

The first common mistake is forgetting that joins can multiply rows. Suppose products joins to order_items, and a product appears in one thousand line items. Any aggregate that runs after the join sees one thousand rows unless you group or deduplicate explicitly. That is why counts suddenly jump after adding an extra table. The join is not wrong. The underlying cardinality changed.

The second mistake is filtering outer joins in the wrong place. Consider a left join that keeps all users even when they have no invoices. If you then add WHERE invoices.status = 'paid', the NULL invoice rows fail the filter and disappear, which effectively turns the query back into an inner join. If the intent is "show all users and only their paid invoices", the filter usually belongs in the ON clause instead.

Physical execution is a separate question. Most databases choose between nested loop joins, hash joins, and merge joins. A nested loop join checks matching rows repeatedly and works well when one side is small or when an index makes each lookup cheap. A hash join builds an in-memory hash table from one input and probes it with the other, which is efficient for large equality joins but needs memory and may spill to disk. A merge join walks through two sorted inputs together and is attractive when both sides are already ordered on the join key.

The optimiser decides among these options using statistics. It estimates how many rows each filter will keep, how selective the join key is, and whether useful indexes or sort orders already exist. If those estimates are wrong, the chosen plan can be dramatically wrong as well. A planner that expects ten rows but gets ten million may choose a nested loop that becomes painfully slow. That is why stale statistics and skewed data distributions matter so much for join performance.

Indexes help, but only when they match the access pattern. An index on a foreign key often makes point lookups from parent to child cheap. It does not magically help every join. If you join on an expression, cast types on one side, or compare non-selective columns, the optimiser may ignore the index or use it with limited benefit.

A useful mental model is this: SQL joins first define a relationship between row sets, then the optimiser chooses the cheapest safe way to realise that relationship. Good join design therefore means more than correct syntax. You need to understand cardinality, place filters deliberately, and give the planner data distributions and indexes that match the real query.