Common Mistakes in SQL Joins (and How to Fix Them)

Common Mistakes in SQL Joins (and How to Fix Them)

Most reporting bugs start with a join that looked perfectly reasonable at the time. The query runs, the numbers come back, and nobody questions them until someone notices the monthly total is roughly double what it should be. Joins are where a small conceptual slip turns into a plausible-looking wrong answer, which is far more dangerous than a syntax error.

These are the mistakes that come up again and again, and the fixes that hold up.

Inner joins and left joins are not interchangeable

An INNER JOIN returns only rows that match on both sides. A LEFT JOIN keeps every row from the left table and fills in NULLs where there is no match. Swap one for the other and you change who you are counting, not just how many columns come back.

Take a customers table and an orders table. A LEFT JOIN from customers gives you every customer, including those who have never ordered. Switch it to an INNER JOIN and those customers vanish from the result. If you are counting customers, the figure is now wrong — quietly, with no warning.

It happens the other way round too. Joining orders to customers with an INNER JOIN drops any order whose customer record is missing, which is exactly the kind of row an audit wants to see.

So the question is not which join is better. The question is what you want the result to represent. If you want all customers, keep the LEFT JOIN and count a column from customers, not from orders. If you want only customers with orders, an INNER JOIN is correct. The mistake is choosing one without deciding which question the result should answer.

Filters on the right-hand table

This one catches experienced people. You write a LEFT JOIN, then add a filter on the right-hand table in the WHERE clause:

SELECT c.customer_id, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.status = 'shipped';

NULL fails a comparison with 'shipped', so every customer without a shipped order is filtered out. The LEFT JOIN has quietly become an INNER JOIN. Move the condition into the ON clause and the join keeps its behaviour:

SELECT c.customer_id, o.order_id FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped';

The rule of thumb: conditions that restrict the right-hand table belong in ON; conditions that restrict the final result belong in WHERE. A useful diagnostic: if a LEFT JOIN returns exactly the same row count as an INNER JOIN would, check whether a WHERE clause on the right table is doing the work of an INNER JOIN.

Duplicate rows and inflated totals

Joining a table to a one-to-many child multiplies rows. Sum order totals after joining orders to order_items and each order total is counted once per line item, so an order with five items contributes five times. The aggregate is too high, and nothing in the query tells you so.

This is sometimes called fan-out. One row on the left matches many rows on the right, and every aggregate that follows is computed over the multiplied set. Suppose orders has one row per order with total = 100. order_items has five rows for that order, each with line_total = 20. Joining orders to order_items and running SUM(o.total) returns 500, not 100. Running SUM(oi.line_total) returns 100, which is correct for the items but not for the order-level total. The grain changed halfway through the query.

Three ways to deal with it:

• Aggregate first, then join. Roll order_items up to one row per order in a CTE or subquery, then join that.
• Use COUNT(DISTINCT order_id) rather than COUNT(*). That fixes counts but not SUMs, so use it knowingly.
• Check the grain before joining. If you cannot describe what one row of the result represents in a single sentence, the query is not ready.

A quick check: run COUNT(*) on each input table, then on the joined result. If the join returns far more rows than the largest input and you did not expect that, something is multiplying. For tables that hold several rows per key — addresses, for example — pick one deliberately with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) and filter to the first.

NULLs break comparisons in ways that are easy to miss

NULL means unknown, not empty and not zero. Nothing equals NULL, not even another NULL, so ON a.key = b.key never matches a NULL key. Rows with missing keys drop out of the join without complaint.

The sharper edge is NOT IN. If the subquery returns a single NULL, a filter such as WHERE id NOT IN (SELECT customer_id FROM orders) can return no rows at all, because the comparison is unknown for every row. NOT EXISTS does not have that problem:

SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

On a LEFT JOIN, remember that SUM and AVG return NULL when nothing matched, and NULL then spreads through anything that uses that value. COALESCE(SUM(o.total), 0) keeps the rest of the report working.

Another common bug: COUNT(o.order_id) counts only non-NULL matches, while COUNT(*) counts every row including the NULL-extended ones. If you want the number of customers, count c.customer_id. If you want the number of orders, count o.order_id. Mixing them up gives a number that is wrong for both.

Performance traps

Wrong answers matter most, but slow queries waste just as much time. Joins are often where performance goes wrong because they combine row multiplication, missing indexes, and filters that cannot be pushed down.

• Functions on the joined column. A predicate such as ON DATE(o.created_at) = d.calendar_date can prevent an index on created_at from being used. If the column is a timestamp and the other side is a date, compare ranges instead: o.created_at >= d.calendar_date AND o.created_at is less than d.calendar_date + INTERVAL '1 day'.
• Mismatched data types. Joining a VARCHAR customer_id to an INTEGER customer_id forces implicit conversion and can make an index unusable. Fix the model or cast explicitly on the side that does not affect the index.
• Joining on non-indexed columns. A join on a column with no index can turn a quick lookup into a full scan for every row. Check the execution plan before assuming the query is the problem.
• Filtering after the join instead of before. If you only need recent orders, filter orders to the recent range in a subquery or CTE before joining. Joining millions of rows and then discarding most of them is wasted work.
• Too many joins in one query. Deep join chains are hard to reason about and easy to fan out. Break the query into steps with CTEs, each with a clear grain, and join the summarized results.

The accidental cartesian product

A CROSS JOIN returns every combination of rows. That is sometimes intentional, but it usually appears by accident when the ON clause is missing or incomplete. If a query that should return thousands of rows returns millions, check for a join without a proper key relationship.

For example, joining customers to orders on a non-unique column such as status, or forgetting the join condition entirely, produces a cartesian product. The result may still run, and if you aggregate it, the numbers can look plausible at first. Always verify that every join has a condition that ties the two tables together at the expected grain.

Join keys that do not mean the same thing

Two columns can share a name and still represent different things. A customer_id in an orders table might be the billing customer, while a customer_id in a shipments table might be the recipient. Joining them without checking the meaning returns matches that are technically valid but semantically wrong.

This is common in systems that have grown over time: legacy IDs, soft-deleted records, or status flags that change what a key refers to. Before joining, read the column comments, check the data dictionary, or sample a few rows. If the key has a status, make sure you are joining to the version of the row you actually want.

Also watch for many-to-many relationships. If both sides of a join can have multiple rows per key, the result is not one row per entity; it is one row per pair. Decide whether you need a bridge table, a pre-aggregation, or a different grain entirely.

How to review a join before you trust it

A short checklist catches most of these mistakes before the report goes out.

1. State the grain. Write one sentence describing what a single row of the result represents. If you cannot, stop and fix the query.
2. Check row counts at each step. Compare COUNT(*) before and after each join. A jump larger than expected means fan-out or a cartesian product.
3. Test with a small sample. Pick one customer, one order, or one date range and verify the numbers by hand.
4. Look for NULLs in join keys. If either side has NULL keys, decide how they should be handled instead of letting the join drop them silently.
5. Read the ON clause out loud. Every condition should either match rows or intentionally filter one side. If a condition restricts the final result, it may belong in WHERE.

Joins are not hard because the syntax is complicated. They are hard because they encode assumptions about relationships, grain, and missing data. Make those assumptions explicit, and the query becomes much easier to defend.

Photo: RDNE Stock project / Pexels