How Left Join works? - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

How Left Join works?

Author

Topic

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-14 : 05:53:31

Hi everyone,

hope you will be having a good time...

Well this is not a problem in fact, but just trying to know why the result sets of below queries are different! .... :-S

Create table Orders (OID int, CName varchar(100))
Create table OrderDetails(OdID int,ODname varchar(100),OID int)

Insert into Orders values (12345,'MIK1')
Insert into Orders values (12346,'MIK2')
Insert into Orders values (12344,'MIK3')

Insert into OrderDetails values (1,'ABC',12345),(2,'DEF',12346),(3,'XYZ',12345)

First Example
A:
Select *
from Orders
Left Join OrderDetails on OrderDetails.OID=Orders.OID
Where (OrderDetails.OID is null Or OrderDetails.OID=12345)
B:
Select *
from Orders
Left Join OrderDetails on OrderDetails.OID=Orders.OID
And (OrderDetails.OID is null Or OrderDetails.OID=12345)

As you can see that the second one is showing both orderIDs (12346 and 12344) with NULL records, however 12346 exists in orderDetails! Why is it treating it different than the A?

Second Example
A:
Select *
from Orders
Left Join OrderDetails on OrderDetails.OID=Orders.OID
Where (OrderDetails.OID is null)
B:
Select *
from Orders
Left Join OrderDetails on OrderDetails.OID=Orders.OID
And (OrderDetails.OID is null)

Again in the second one it comes as to be NULL for all values of orders??

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-14 : 06:31:28

In general terms--the FROM clause will build the table, and a WHERE clause will filter that result, so in both cases, the 2nd table has no filter.

In A:, it returns all rows from orders where the matching condition is true, then filters out the NULL Orders.OID and the Details.OID=12345 from that result.

4 rows match on condition of the join, and 1 row matches the isnull where condition, and 1 row is filtered out where the detials.OID = 3

In B: it makes a table returning all rows with the matching condition is true AND where either of the other conditions are true, but nothing is being filtered out.
4 rows match on joining condition again, NO rows have an OID of null, and 2 rows have the matching 12345.

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-16 : 05:38:19

see
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources