Conditions in the JOIN or in the WHERE?

Question

Which is more effective, put the conditions in the JOIN:

SELECT * FROM table1
LEFT JOIN table2 ON 
     table1.id=table2.fk AND
     table1.field1='some' AND
     ....

Or put in the WHERE:

SELECT * FROM table1
LEFT JOIN table2 ON 
     table1.id=table2.fk
WHERE 
     table1.field1='some' AND
     ....

I voted to re-open this because the original question used an `INNER JOIN` and the explanations didn't really go through the difference in the case of an `OUTER JOIN`. (Some made vague reference, some said nothing.) Note this sample code is outer. — Andrew Lazarus, Feb 19 '14 at 08:22
[I'm sorry,but it seems](http://stackoverflow.com/questions/121631/inner-join-vs-where?rq=1) [to me that the question](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause?rq=1) [was asked before](http://stackoverflow.com/questions/1018952/condition-within-join-or-where?rq=1). — Alexander, Feb 19 '14 at 08:32
Performance. If I haver, for exemple, 1 milion results in every table which of this is the most efficient (speed, server load...) — David, Feb 19 '14 at 14:19

podiluska · Answer 1 · 2014-02-19T09:04:17.090

They're not more or less effective, they're functionally different.

With no filter, this joins all the records from the master to those matching in the subsidiary

select * from t1
    left join t2 on t1.a = t2.c

using the where, and filtering on the subsidiary table, this join will be equivalent to an inner join

select * from t1
    left join t2 on t1.a = t2.c 
where t2.d=5

using the join, and filtering on the subsidiary table, this filters the subsidiary table's records

select * from t1
    left join t2 on t1.a = t2.c and t2.d=5

using the where, filtering on the master table, this filters the master table records

select * from t1
    left join t2 on t1.a = t2.c 
where t1.b = 3

using the join, this filters the records in the master that can be joined to, but still returns all records from the master.

select * from t1
    left join t2 on t1.a = t2.c and t1.b = 3

So given the following tables

table t1

a           b
----------- -----------
1           2
1           3
2           4
3           3

table t2

c           d
----------- -----------
1           9
4           5
2           5

the results are

query1

a           b           c           d
----------- ----------- ----------- -----------
1           2           1           9
1           3           1           9
2           4           2           5
3           3           NULL        NULL

query2

a           b           c           d
----------- ----------- ----------- -----------
2           4           2           5

query3

a           b           c           d
----------- ----------- ----------- -----------
1           2           NULL        NULL
1           3           NULL        NULL
2           4           2           5
3           3           NULL        NULL

query4

a           b           c           d
----------- ----------- ----------- -----------
1           3           1           9
3           3           NULL        NULL

query5

a           b           c           d
----------- ----------- ----------- -----------
1           2           NULL        NULL
1           3           1           9
2           4           NULL        NULL
3           3           NULL        NULL

score 0 · Answer 2 · answered Feb 19 '14 at 08:17

0

SELECT t2.name

FROM table1 t1

INNER JOIN table2 t2 ON t1.h_id = t2.h_id

WHERE t1.Date = CURDATE()

Another possibility is the where clause, try it like this:

 SELECT t2.name
 FROM table1 t1
 INNER JOIN table2 t2 ON t1.h_id = t2.h_id
 WHERE convert(varchar, t1.Date, 112) = convert(varchar, getdate(), 112)

answered Feb 19 '14 at 08:17

Dot_NET Pro

2,095
2
21
38

1

Is this an answer? If it is, I'm not following. Can you expand it? – Alexander Feb 19 '14 at 08:19

M Khalid Junaid · Answer 3 · 2014-02-19T08:23:24.703

Conditions in join will only join the rows from the specific table where specific conditions matches while the conditions in where clause will filter out the whole result set. Like table2 has 10 rows and for specific condition table 2 has 3 rows so when applying condition in join you will get all the rows from left table i.e table1 ,but when applying condition in where clause you will get the rows from table1 where table2's condition matches ,not all the rows from table1

Conditions in the JOIN or in the WHERE?

3 Answers3