SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Nuances of Left Join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

scrapple
Starting Member

2 Posts

Posted - 09/08/2010 :  17:11:43  Show Profile  Reply with Quote
Hi, I'm trying to do a left join in SQL Server 2005 and I'm getting three different results from these three queries. I'm moderately familiar with SQL (primarily in Oracle) and I'm surprised that I'm getting different results from these three queries.

Note, this is not homework, it's for a data store I'm building for work.

Can anyone help me understand why these queries are giving me different results?


Query 1 gives me the correct result of 4193 records. There are 73 records in the masterfile table that do not have corresponding records in the grant table.

select e.code, e.year, e.id_number, e.f_name, e.l_name, g.poss, g.sess from masterfile e left outer join grant g on e.id_number = g.id_number and e.code = g.code and e.year = g.year and g.term = 'A' where e.year in ('08', '09') order by id_number



Query 2 gives me 4120 records. The 73 records in the grant table are being filtered out of the output as there are no nulls in the poss or sess fields.

select e.code, e.year, e.id_number, e.f_name, e.l_name, g.poss, g.sess from masterfile e left outer join grant g on e.id_number = g.id_number and e.code = g.code and e.year = g.year where g.term = 'A' and e.year in ('08', '09') order by id_number



Query 3 gives me 18952 rows!

select e.code, e.year, e.id_number, e.f_name, e.l_name, g.poss, g.sess from masterfile e left outer join grant g on e.id_number = g.id_number and e.code = g.code and e.year = g.year and g.term = 'A' and e.year in ('09', '10') order by id_number



I think the issue must be that I'm not understanding the sequence of how SQL Server applies the where clause vs. the join conditions. I thought that SQL was supposed to apply the where clause before applying the join, but Query 2's result seems to indicate that the where clause is being applied after the join. Is that true?

If the where clause is applied after the join, why does the third query give me so many rows? Many of the rows are for years that I filtered out (I'm getting '07' and '10' coming in the output).

Any help is appreciated. I'm pretty confused right now.

Thanks!

tkizer
Almighty SQL Goddess

USA
36636 Posts

Posted - 09/08/2010 :  17:15:15  Show Profile  Visit tkizer's Homepage  Reply with Quote
You should read this: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

scrapple
Starting Member

2 Posts

Posted - 09/08/2010 :  21:00:25  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

You should read this: http://www.sqlteam.com/article/additional-criteria-in-the-join-clause

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog



Thank you Tara! That was really helpful.

Follow up question, why did my third query not end up with the same result as the first query. The only difference between the first and third queries is the location of the "e.year in" condition. By moving it into the join, shouldn't it end with the same result since it's a filter on the left table?
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
790 Posts

Posted - 09/08/2010 :  21:17:18  Show Profile  Reply with Quote
Because your third query does not have a where clause to filter out any results. The third query basically says:

Get all rows from the masterfile - and only show matching rows from grant where all of the join conditions are true. If you review the results from your third query, you'll find that most of the values from the grant table are NULL, because for those rows there are now matching rows in the grant table.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
2916 Posts

Posted - 09/09/2010 :  04:15:57  Show Profile  Reply with Quote
1. raw query.
2. "left join" "implicity" converted into an "inner join" with the WHERE CLAUSE by the "where g.term = 'A'" statement. automatically excludes the NULL records from the LEFT JOIN table.

3. a) for a start - different set of years named in the IN statement
b) no where clause, so you are returning records with null in the RHS...i.e for the LEFT JOIN table.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000