What is the difference between putting a condition in ON and in WHERE for a LEFT JOIN?

SQL joins · Freshers and experienced

Model answer

A condition on the right table in WHERE removes the NULL rows, turning the LEFT JOIN into an inner join effectively. Putting it in ON filters the right table while keeping all left rows. This is a common source of bugs and interview questions.

Learn this topic

SQL JOINs: INNER JOIN and LEFT JOIN Explained in the course SQL for Beginners: Learn Databases Step by Step.

Sign in to add your own answer.

More questions

• What is a join, and what types are there?
• What is the difference between INNER JOIN and LEFT JOIN?
• How do you find rows in one table that have no match in another?
• What is a self join?
• What is a CROSS JOIN?
• Why does a missing join condition create duplicate or huge results?