CODEWITHSUNDEEP
t-sqljoin
user3150260
user3150260

Reputation: 99

Criteria in WHERE vs. JOIN

Is there any difference/limitations/considerations that need to be made when adding additional criteria to a JOIN rather than including it in a WHERE clause. Example...

SELECT
*
FROM
TABLE1 t1
INNER JOIN 
TABLE2 t2
ON t1.a = t2.a
AND
t1.DATE_TIME < 06/01/2015

versus

SELECT
*
FROM
TABLE1 t1
INNER JOIN 
TABLE2 t2
ON t1.a = t2.a
WHERE
t1.DATE_TIME < 06/01/2015

Upvotes: 0

Views: 42

Answers (2)

paparazzo
paparazzo

Reputation: 45096

The optimize most likely will treat those two the same

But if you get into 4 or more join what can happen is for the query optimizer to go into a loop join and in that case they might process differently

The safer bet is to have the condition in the join (the first)

Upvotes: 0

Renzo
Renzo

Reputation: 27424

All the optimizers of DBMS threats the two queries in the same way, so there is no difference in performance between them. The most commonly used form is the second one.

Upvotes: 1

Related Questions