www.greg-wright.co.uk

Additional information in an ON Clause can be confusing. Consider the difference between


SELECT books.title, sales.quantity FROM books
LEFT JOIN sales
ON books.bookId = sales.storeId
WHERE sales.storeId = 123


and


SELECT books.title, sales.quantity FROM books
LEFT JOIN sales
ON books.bookId = sales.storeId
AND sales.storeId = 123


Based on http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

The first case does what you would expect - printing out books and sales data for store 123.

The second case takes each row in the books table and adds to it EITHER the row from the sales table if sales.bookId = books.bookID AND the sales.storeId is 123 Or else a row of NULLS. So in this case you get a list of all books plus the store information if the book was sold by store 123.