SQL WHERE or ON for join statement

Question

Way1:
Select * from tableA inner join tableB 
on tableA.aid = tableB.aid
where a.condA = xx
AND a.condB = xx
AND a.condC = xx

Way2:
Select * from tableA inner join tableB 
on tableA.aid = tableB.aid
AND a.condA = xx
AND a.condB = xx
AND a.condC = xx

Judging from above syntax, is there any significant differences in terms or performances?

Check execution plans, it is so simple. Also query optimizer can generate the same plan for both, but as always it depends. — Lukasz Szozda, Sep 15 '15 at 14:34
And notice the result for both queries would be different if you use a `left join` instead. — juergen d, Sep 15 '15 at 14:36
You can enable Execution Plans in the Query dropdown list. After running your query, it will show you the computational cost of every operation done, and more detailed information when you hover over an icon. — jjjjjjjjjjj, Sep 15 '15 at 14:38
https://www.simple-talk.com/sql/performance/execution-plan-basics/ — wiretext, Sep 15 '15 at 14:38
@juergend: a left join is something different then an inner join. (you can achieve an outer (left) join using where syntax as well, albeit different per SQL dialect. (WHERE *= ) — Frederik Gheysels, Sep 15 '15 at 14:40

score 1 · Answer 1 · answered Sep 15 '15 at 14:38

1

Normally, the DBMS should generate the same execution plan.

I find the former one (the JOIN syntax) more obvious then the latter one since it is clearer what you're doing.

answered Sep 15 '15 at 14:38

Frederik Gheysels

56,135
11
101
154

It is also an ANSI standart form (http://www.w3schools.com/sql/sql_quickref.asp) – Burcin Sep 15 '15 at 14:52

score 0 · Answer 2 · answered Sep 15 '15 at 14:50

0

No. No difference.

It doesn't make any difference in case of INNER JOIN, both performance wise and result wise.

In case of OUTER JOIN, the placement of condition makes the difference.

answered Sep 15 '15 at 14:50

Sateesh Pagolu

9,282
2
30
48

score 0 · Answer 3 · answered Sep 15 '15 at 23:30

They should generate the same execution plan with the same results, the advantage of using example 1 is the readability. In small queries it's okay to use either but as queries get more complex it's nice to have something readable plus be nice to your peers if they ever read your code. It's like formatting, everyone likes neat code, easy to read.

score -1 · Answer 4 · answered Sep 15 '15 at 15:00

In terms of the queries being equivalent, the only time it really matters is for outer joins. In those cases whether you put the criteria in the join or the where matters. One is applied before/during the outer join and the other after the join. Thus conditions in the where need to handle nulls as the outer join could result in no actual join. So the two queries for an outer join are not equivalent.

Sometimes in inner joins it will matter if the condition will cause a field to be converted into another type. If that is the case there may be a type conversion issues which prevents it from working properly. In other words if your comparing an integer column to a string column and the string column contains 'TEST', well you're going to have a bad time. Unless you exclude those rows via the join condition.

In terms of performance, you want to put the conditions in the join which will hit the index you want to use. Beyond that they should go in the where clause unless they are needed in the join.

But your example may not be the best. It looks like all checks against specific fields for specific values. If you have conditions that just check a value from one table against a constant or variable these are typically not used as join conditions.

SQL WHERE or ON for join statement

4 Answers4