where clause, vs AND clause in the join
-
I was playing with a query to see the most efficient way to write it. it takes a table that contains a list of tables, and lists off any tables in that list that actually exist, simple stuff.
To get tables that exist, I need to remove any rows that have a null in them (where the tables dont exist in the system). If i put this "t2.tablename is null" in the where clause, its much less efficient than putting it as a join predicate. Two things i am unsure of:
Why is the query more efficient when i have a join predecate checking for null values?
Even with checking for the null join predecate i still need the where clause to do filtering. Why do i still need the where clause even when i am checking for null values in the join predecate?
-
Because you have two logically different queries there.
That's why there are different performance characteristics and why you still have to filter in the where even with the 'filter' in the join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply