Back to basics - JOINs
-
I have a basic question about the JOINs. If we have 2 tables tblA, tblB. If I join both the tables on col1 and there is also a WHERE condition to filter the rows off the 2 tables, will the WHERE clause filter be applied after matching rows in the ON clause or the WHERE clause is applied first before the matching is done?
Thanks
-
AFAIK no.
I think the viewing the execution plan of your query would tell you for certain. The restriction of the data (WHERE) should be done before the join but to be honest I don't know for sure.
cheers
dbgeezer
-
I think it's up to the db optimiser to decide how to execute a query - so I don't think it would make a difference whether you write
select *
from A
join B
on B.col1 = A.col1
where B.col2 = @var
or
select *
from A
join B
on B.col1 = A.col1
and B.col2 = @var
and depending on indexes etc. and any variables in the query, the execution plan could change on that.
If you're joining to table B and you've got a restriction to apply to table B, I think it reads easier if you put that in the join clause for B.
-
- your execution plan will clarify this
- as always " it depends "
* wich type of join are you using (inner /left-right /full outer)
* filterfactors of the used predicates (where- and join-clause)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply