SQL JOIN: what is the difference between WHERE clause and ON clause?

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 The first will return an order and its lines, if any, for order number 12345. The second will return … Read more

SQL Filter criteria in join criteria or where clause which is more efficient

I wouldn’t use performance as the deciding factor here – and quite honestly, I don’t think there’s any measurable performance difference between those two cases, really. I would always use case #2 – why? Because in my opinion, you should only put the actual criteria that establish the JOIN between the two tables into the … Read more