SQL joins and indexes: the parts that quietly change your answer

SQL has an unusual failure mode among the languages a student meets early. A Python bug generally announces itself with a traceback. A SQL bug hands you a result set that looks entirely plausible and is missing four percent of the rows.

This is a note on the specific places that happens: joins that change cardinality, NULLs that do not compare the way you expect, aggregates computed over the wrong grain, and indexes that exist but are not used. These are the ones I see most often, both in tutoring and in my own work.

A join is a filter and a multiplier at the same time

The usual mental model of a join is "gluing tables together side by side." That model is fine until rows do not correspond one to one, and then it produces confident wrong answers.

A join produces every pair of rows that satisfies the condition. If one customer has three orders, joining customers to orders yields three rows for that customer, and the customer's data is repeated in each. This is correct and it is also the source of the most common serious SQL error:

-- WRONG: counts the customer row once per order
SELECT c.region, COUNT(*) AS customers
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

That does not count customers. It counts customer-order pairs, so a customer with twelve orders contributes twelve. It also silently excludes every customer with no orders at all, because an inner join drops unmatched rows.

-- RIGHT: count distinct customers, keep customers with zero orders
SELECT c.region, COUNT(DISTINCT c.id) AS customers
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.region;

The LEFT JOIN that becomes an INNER JOIN

This one is subtle enough that it catches people well past their first database course.

-- Looks like a LEFT JOIN. Behaves like an INNER JOIN.
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';

The LEFT JOIN keeps customers with no orders, filling their o.* columns with NULL. Then the WHERE clause runs, and NULL = 'shipped' is not true — so every one of those preserved rows is removed. The outer join is undone by the filter.

The fix is to put the condition in the join, where it restricts what is matched rather than what survives:

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

The rule generalises cleanly: a condition on the right-hand table of a LEFT JOIN belongs in the ON clause; a condition on the left-hand table belongs in WHERE. Putting a right-table condition in WHERE converts your outer join to an inner join, always.

NULL is not a value, and it breaks NOT IN

NULL means unknown. Comparisons against unknown are unknown, not false, and only rows that evaluate to true are returned. Hence WHERE x = NULL matches nothing ever, and you need IS NULL.

The version that costs real time is NOT IN against a subquery that can produce a NULL:

-- Returns ZERO rows if any customer_id in orders is NULL
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

If the subquery returns (1, 2, NULL), then id NOT IN (1, 2, NULL) asks whether id differs from 1, and from 2, and from an unknown value. That last comparison is unknown, so the whole expression is never true — and you get an empty result from a query that is syntactically perfect. Use NOT EXISTS, which handles this correctly and usually optimises better:

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

Two related NULL behaviours worth memorising: aggregates skip NULLs, so AVG(col) divides by the count of non-null values, not the row count — meaning AVG and SUM(col)/COUNT(*) disagree whenever nulls exist. And COUNT(col) excludes NULLs while COUNT(*) counts rows regardless.

Aggregating at the wrong grain

Once two joins are involved, it is easy to sum a column that has already been duplicated:

-- order_total is repeated once per line item, so this over-counts
SELECT o.id, SUM(o.order_total) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

The order's total is repeated for every line item, so an order with four items reports four times its value. Either aggregate the child table before joining, or sum the column that genuinely lives at the finer grain:

SELECT o.id, SUM(i.line_total) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

The habit that prevents this: before writing an aggregate, say out loud what one row of the pre-aggregation result represents. "One row per order per line item" immediately tells you that summing an order-level column is wrong.

What an index is, and when it is ignored

Without an index, finding rows matching a condition means reading the whole table. An index is a sorted structure — typically a B-tree — that lets the database jump to the matching range instead. The tradeoff is real: indexes consume storage and make every insert, update, and delete slightly slower, because the index must be maintained too.

Reasonable defaults for a coursework or small project schema: index primary keys (usually automatic), index every foreign key column (usually not automatic, and the most common missing index), and index columns you filter or sort by frequently.

The more useful knowledge is when an index you created is not used at all:

• The column is wrapped in a function. WHERE YEAR(created_at) = 2026 cannot use an index on created_at, because the index stores the raw values, not the function's output. Rewrite as a range: WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'.
• A leading wildcard. LIKE '%smith' cannot use a B-tree, since the index is sorted from the left. LIKE 'smith%' can.
• Type coercion. Comparing an indexed integer column to a string may force a cast on every row.
• The table is small. A sequential scan of 400 rows genuinely beats an index lookup, and the planner knows it. This is not a problem.
• Low selectivity. An index on a boolean that is true for 60% of rows will be skipped, because reading most of the table via an index is slower than reading it directly.

Composite indexes follow a left-prefix rule: an index on (customer_id, created_at) serves queries filtering on customer_id, or on both columns, but not queries filtering on created_at alone. Column order is a design decision, not a formality.

Read the plan instead of guessing

Every major database will tell you what it intends to do. In PostgreSQL:

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

EXPLAIN shows the planned strategy; ANALYZE actually runs the query and reports real timings and row counts. Three things to look for, in order of usefulness:

First, a large gap between estimated and actual row counts. The planner chooses its strategy from those estimates, so when they are badly wrong the plan is likely wrong too — often fixed by updating table statistics.

Second, a sequential scan on a large table with a selective filter. That is the signature of a missing or unusable index.

Third, where the time is actually spent. Optimising a node that accounts for two percent of runtime is a common way to spend an afternoon and change nothing.

A short checklist

1. Did the row count change when I added this join, and can I explain why?
2. Is any condition on a LEFT JOIN's right-hand table sitting in the WHERE clause?
3. Can any column in a NOT IN subquery be NULL?
4. What does one row represent immediately before the GROUP BY?
5. Is every foreign key I join on indexed?
6. Is any filtered column wrapped in a function?

Six questions, and they catch the large majority of wrong-but-plausible results. The habit underneath all of them is the same one that makes debugging work in any language: state what you expect before you look, so the mismatch has somewhere to show up. That is the same method I describe for tracing and debugging code, applied to a language where the errors are quieter.