Solve “Deduplicate events with ROW_NUMBER”

Free. Runs in your browser, no install.

SQL · Basics & Joins

ON vs WHERE in a JOIN

Easyonwhereouter join

Interview question

What is the difference between ON and WHERE in a JOIN?

How to answer

The short answer

ON specifies how rows are matched.

WHERE filters the result after the join logically occurs.

Example:

SQL
SELECT *
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
WHERE o.amount > 100;

That WHERE can drop customers who have no matching orders (the LEFT JOIN starts acting like an INNER JOIN).

Compare:

SQL
SELECT *
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
   AND o.amount > 100;

Now customers without qualifying orders can still appear.

Gotcha:

Moving conditions between ON and WHERE is not always the same, especially with LEFT, RIGHT, or FULL OUTER JOIN.

Lock it in with a real drill

You remember an answer longer once you have used it. Try a hands-on problem on this concept.

Open related drill