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_numberQuery 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_numberQuery 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_numberI 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!
Nuances of Left Join
Posted - 2010-09-08 : 17:11:43
