Can someone explain if this is correct.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Can someone explain if this is correct.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2010-11-17 : 12:41:15

In the below two queries, it produces two different results. I understand HOW sql will process this, but I want to confirm my interpretation of WHY it functions like this is accurate.


Select * 
 from #InfoTable a
left join #COMPUTEFields b
on a.EmployeeID = b.EmployeeID 
and a.PlanID = b.PlanID
Where a.Typeidentifier = 0


Select
 from #InfoTable a
left join #COMPUTEFields b
on a.EmployeeID = b.EmployeeID 
and a.PlanID = b.PlanID
and a.Typeidentifier = 0

I know how this works, but can someone explain WHY this works this way? Shouldn't a filter on the join of "And a.TypeIdentifier = 0" filter out the first table as well since it is explicitly being instructed to, or is it just simply because it's a left join, that anything in that join clause only pertains to the right table?

Now let's take the argument one step further.


Select
 from #InfoTable a
Inner join #ANOTHERTABLE bb
on a.EmployeeID = bb.EMployeeID
and a.Typeidentifier = 0

--Filters fine above since SQL knows that a inner join is checking
--Both tables

Select
 from #InfoTable a
Inner join #ANOTHERTABLE bb
on a.EmployeeID = bb.EmployeeID
and a.Typeidentifier = 0

left join #COMPUTEFields b
on a.EmployeeID = b.EmployeeID 
and a.PlanID = b.PlanID

--Here it does not filter anymore, because once a Left Join is
--introduced on table a, it now no longer will filter

So again even with a inner join as long as that left join is present, there is NO filtering on that first table in the join syntax, it needs to be in the where. But in the last query to me it seems like since the inner join exists that it SHOULD filter "a.Typeidentifier = 0". It would've if I wrote as


Select
 from #InfoTable a
Inner join #ANOTHERTABLE bb
on a.EmployeeID = bb.EmployeeID
and a.Typeidentifier = bb.TypeIdentifier

left join #COMPUTEFields b
on a.EmployeeID = b.EmployeeID 
and a.PlanID = b.PlanID

Why didn't Microsoft write the SQL engine to factor in a explicit left table filter when there is a left join, it is not crystal clear to me? I understand there is a legitimate reason, but I would like to know it if someone could explain?

Thanks!

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-11-17 : 12:57:27

It's a "feature!" :(

If you want to use a left join and apply a predicate to the left table you must do it in the where clause not on the join condition.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-11-17 : 13:11:26

The ON clause, for an OUTER JOIN, will determine whether the non-preserved table returns actual data or not but it doesn't impact the rows returned by the preserved table. The WHERE clause is used to filter the results of the join.

For an INNER JOIN, the ON clause is, de facto, a filtering mechanism since a non-true result prevents either table from returning data.

=======================================
Man is least himself when he talks in his own person. Give him a mask, and he will tell you the truth. -Oscar Wilde, writer (1854-1900)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources