June 2, 2004 at 10:18 am
Hi ,
I have a basic question. Can you please tell me the order sequence in the following select statement.
select a.col1,a.col2,b.col2 from table1 a inner join table2 b on a.col1 = b.col1 where a.col1 > 100.
Which one will be first executed? the "on condition" or "where condition"?
Thanks,
Sridhar!!
June 2, 2004 at 11:31 am
Joins are always executed before the where criteria.
In queries with more than 1 join, you can sometimes make the query more efficient by moving some of the criteria to the join statement.
For example, if you have the following query:
Select *
From Table1 As t1 Inner Join Table2 As t2 On t1.t1ID = t2.t1ID
Inner Join Table3 As t3 On t2.t2ID = t3.t2ID
Where t1.SomeValue > 10
You can improve performance by moving the criteria to the first join because it will limit the number of rows resulting from the first join making the second join faster. Like so:
Select *
From Table1 As t1 Inner Join Table2 As t2 On t1.t1ID = t2.t1ID And t1.SomeValue > 10
Inner Join Table3 As t3 On t2.t2ID = t3.t2ID
June 3, 2004 at 12:26 am
However, this is no exact science, and there are more factors than just semantics.
For inner joins, there is no difference for the endresult if stuff goes into the ON clause or the WHERE clause.
Many times the optimizer will choose to execute queries like this exactly the same.
SELECT ...
FROM tabA JOIN tabB
ON tabA.id = tabB.id
WHERE tabA.someValue > 100
SELECT ...
FROM tabA JOIN tabB
ON tabA.id = tabB.id
AND tabA.someValue > 100
The only way to know is to check the query plans generated.
Having said that - join order sequence does matter for outer joins, but that's another thing than this question.
=;o)
/Kenneth
June 3, 2004 at 7:24 am
Joins occurr before the where condition.
And when multiple joins exist the occurr top to bottom, inside to outside.
So say you have (IJ = Inner Join)
SELECT
...
FROM
tA
IJ
tB
IJ
tC
ON
tC.Col = tB.Col
ON
tB.Col = tA.Col
IJ
tD
ON
tD.Col = tC.Col
IJ
tE
IJ
tF
ON
tF.Col = tE.Col
ON
tF.Col = tC.Col
WHERE
...
GROUP BY
...
HAVING
...
ORDER BY
...
So in the above this occurrs
tC joined to tB results joined to tA results joined to tD results joined to results of (tF joined to tC).
Where is then processed, then Group By, then Having (Consider Having the Where of a Group By), Then Order By.
Also good to note is that in the join tB to tC the On clause for those does not recognize the existance of tA and in the Join of tF to tE the On clause does not recognize the other resultsets.
Also note with Inner Join more times than not it will not make a difference in speed but that does mean it wouldn't hurt to check. With Outer joins it can change your entire filnal result.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply