Posted By: BCS
After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins.
The answer may be related (or even the same) but the question is different.
What is the difference and what should go in each?
If I understand the theory correctly, the query optimizer should be able to use both interchangeably.
They are not the same thing.
Consider these queries:
SELECT * FROM Orders LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID WHERE Orders.ID = 12345
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 all orders, but only order
12345 will have any lines associated with it.
INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.