SQL JOIN - WHERE clause vs ON clause

Question

What is the difference and what should go in each?

Answer 1

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

And

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

For order number 12345, the first will return an order together with any lines that it contains. All orders will be returned by the second, but only order 12345 will have any lines attached to it.

The clauses are effectively equal when used with an INNER JOIN. The two types of sentences do not, however, have the same semantic meaning only because they are functionally equivalent in that they provide the same outcomes.

I hope this helps you.