Joins difference

Question

Joins difference

stephensunil

SSC Veteran

Points: 272
More actions
June 26, 2007 at 3:45 am

#76727

Hi,
Can any one tell me what is the difference between using
left outer join and Using *= in a query. Below given query returns two different answers
SELECTTable2.ApplicationID,
Name,
Table1.ItemID,
Table2.Access_Level,
ISNULL(Table1.Description,'') Description,
Table2.Access_Level Actual_Access,
'U' Action
FROM Table1 WITH (Nolock) ,
Table2 WITH (Nolock)
WHERE Table1.ItemId *=Table2.ItemId
AND Table1.ApplicationID = 3
AND ROLEID = 11
----------------------------------------------------------------
SELECTTable2.ApplicationID,
Name,
Table1.ItemID,
Table2.Access_Level,
ISNULL(Table1.Description,'') Description,
Table2.Access_Level Actual_Access,
'U' Action
FROM Table1 WITH (Nolock)
left outer joinTable2 WITH (Nolock)
on Table1.ItemId =Table2.ItemId
WHERE
Table1.ApplicationID = 3
AND ROLEID =11
First returns has expected 376 records but the second one returns only 375.Can any one help me out from this?
Thanks in advance
Stephen

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18859 More actions · Answer 1

If ROLEID is in glb_Item_Rights then you should put it into the ON clause of the join otherwise ROLEIDs of NULL will be removed when ROLEID = 11 is evaluated in the WHERE clause.

SQL starts evaluating a query by:

1. evaluating the JOINs.

2. adding any outer bits of the JOINs.

3. evaluating the WHERE clause.

...

It is best not to use the old style *= syntax as it is not supported in SQL2005.

stephensunil SSC Veteran Points: 272 More actions · Answer 2

Thanks Ken. Got the difference. Thanks again.

Can you explain what is "Adding any outer bits of the JOINs."

Ken McKelvey SSCoach Points: 18859 More actions · Answer 3

In the case of a LEFT JOIN an INNER JOIN is done first, then any rows in the left table not in the result set are added with NULLs in the right table's columns.