March 4, 2011 at 7:29 am
This is downright embarrassing. I have been doing this for 16 years now and I am beginning to think that I have missed something basic in SQL theory. The classic definition that I have always used to explain how to use the left join was that you ALWAYS will get back ALL of the records from the left table, and those records from the right table that satisfy the join, with the others being null. I have run into a behavior that makes me believe that this definition is not accurate.
This was all done on SQL 2000.
For demonstration purposes, I create and populate two tables:
Table 1
IntVarchar(50)
pIdFullName
1Joe
2Sally
3Jack
4Joan
Table 2
IntVarchar(50)Int
oIdpIdLocId
111
212
321
442
So now I run a left joined query and attempt to limit the right table to only those records that have a LocId of 1
select t1.* from table1 t1
left join table2 t2 on t1.pid = t2.pid
where t2.locid = 1
pIdFullName
1Joe
2Sally
I can get to what I would consider to be my expected result by moving the limitation into the actual join statement like this:
select t1.* from table1 t1
left join table2 t2 on t1.pid = t2.pid
and t2.locid = 1
pIdFullName
1Joe
2Sally
3Jack
4Joan
So my question is why does the first query not return ALL of the records in table1? It is a left join, which I have always thought will return ALL records from the left table, regardless of everything else, other than where clause limit placed on the left table, but that does not appear to be the result I am seeing. Is SQL creating a composite result, then applying the where clause, thus filtering them out?
WABALUBADUBDUB
March 4, 2011 at 7:51 am
You are seeing exactly what I would expect. The JOIN and WHERE clauses are different parts of the query, and the JOIN is logically evaluated before the WHERE.
In a left join, any row that is not matched in the right table will have its columns returned as a NULL.
By putting a predicate against the right hand table in the WHERE clause, you're telling SQL Server that, AFTER it has evaluated the join (where your non matched rows from your right hand table will be included) to return only rows where that column is a 1 (not a NULL). By doing that, you have effectively turned it into an inner join.
Moving the criteria up into the JOIN means it will be avaluated first, and any non matching rows from the right hand table will be passed to the WHERE phase.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply