ON Versus WHERE

For an inner join they behave the same. For an outer join they absolutely do not.

A condition in ON decides what counts as a match. A condition in WHERE filters the rows that came out. After a LEFT JOIN, the unmatched rows are full of NULLs, and a NULL fails every WHERE comparison, so those rows are removed and your LEFT JOIN has quietly become an inner one.

◈ Graded Query

Return every author's name and how many books the archive holds by them, including the authors with none, who must show 0. Most books first, and alphabetical by name where counts tie. Two columns: name, then the count.

Checked against the rows your query returns. 1 visible and 2 hidden checks, some against data you cannot see.

loading editor…

Try it

Which version answers 'how many 1980s books does each author have?' Both are valid queries and only one is right.

loading editor…

real SQLite, in your browser

Now move the year condition into a WHERE clause and run it again. Authors with no 1980s book disappear entirely, which is a different answer to a different question.

Mark this lesson complete

Signed in, your progress follows you to every device.

Next: Practice Test →

← Back