Difference between the AND statement in an Inner Join or in a WHERE clause

Question

Hello guys I have a specific question about the AND clause in SQL.

The two following SQL statements provide the same output:

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id AND t2.id = 0


SELECT * FROM Table1 t1 INNER JOIN Table2 t2 ON t1.id = t2.id WHERE t2.id = 0

Notice the difference at the end of the query. In the first one, I use the AND clause (without using the WHERE clause before). In the second one, I use a WHERE to specify my id.

1. Is the first syntax correct?
2. If yes, is the first one better in terms of performance (not using WHERE clause for filtering after)?
3. Should I expect different outputs with different queries?

Thanks for your help.

Answer 1

Yes, no, and no.

To be specific:

1. Yes, the syntax is correct. Conceptually, the first query creates an inner join between t1 and t2 with the join condition t1.id = t2.id AND t2.id = 0, while the second creates an inner join on t1.id = t2.id and then filters the result using the condition t2.id = 0.

   However, no SQL engine I know of would actually execute either query like that. Rather, in both cases, the engine will optimize both of them to something like t1.id = 0 AND t2.id = 0 and then do two single-row lookups.

2. No, pretty much any reasonable SQL engine should treat these two queries as effectively identical.

3. No, see above.

By the way, the following ways to write the same query are also valid:

SELECT * FROM Table1 t1 INNER JOIN Table2 t2 WHERE t1.id = t2.id AND t2.id = 0

SELECT * FROM Table1 t1, Table2 t2 WHERE t1.id = t2.id AND t2.id = 0