SQL join syntax: put data filter in "on clause" or "where clasuse"?

Question

for example, I have a sql query:

 select table1.*, table2.* from table1 
 left join table2 on table1.id=table2.id AND table2.someCol>123 (put it here)<p>
 where table2.someCol>123 (or put it here)

it seems that the two position all can put the filter condition, and is the SAME meaning, is it?

possible duplicate of [Condition within JOIN or WHERE](http://stackoverflow.com/questions/1018952/condition-within-join-or-where) — Tomas Pastircak, Apr 22 '14 at 14:41
@TomasPastircak Yes, that question is really answered my question. how could you find it out? — lovespring, Apr 23 '14 at 09:44

score 5 · Accepted Answer · answered Apr 22 '14 at 14:39

No, it's not the same. It matters when you are left/right joining. If you put a where condition in the where clause referencing a left or right joined table, you basically turn it into an inner join. It would be a left or right join again, if you add OR referenced_left_or_right_joined_column IS NULL.

SQL join syntax: put data filter in "on clause" or "where clasuse"?

1 Answers1