Common mistake

SQL: INNER JOIN Filters Belong in the Right Place

Put table relationship predicates in ON and row filters in WHERE. The query is easier to inspect and harder to break.

The symptom

An INNER JOIN query technically works, but the join condition is buried inWHERE with ordinary filters. When the result looks wrong, it is harder to tell whether the bug is the table relationship or the row filter.

Harder to debug

select c.customer_id, o.order_id
from customers as c
join orders as o
where o.customer_id = c.customer_id
  and o.status = 'shipped'

Why it happens

For INNER JOINs, a key match in WHERE can produce the same rows as the same key match in ON. That does not make it a good habit. Relationship logic and row-filter logic answer different questions, and mixing them makes missing or incomplete join predicates easier to miss.

The fix

Put the relationship in ON. Put filters about which rows you want in WHERE. The result is clearer, and the same habit prevents serious bugs when the query later changes to a LEFT JOIN.

Right: relationship first, filter second

select c.customer_id, o.order_id
from customers as c
join orders as o on o.customer_id = c.customer_id
where o.status = 'shipped'

LEFT JOIN warning

With LEFT JOINs, moving right-table filters between ON andWHERE can change the result. Keeping the habit clear for INNER JOINs makes that difference easier to reason about later.

Learn more

Previous · Why a LEFT JOIN turns into an INNER JOIN

Next · Why COUNT(column) skips NULL values →