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
 Left Join

Author  Topic 

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-02-08 : 11:16:32
Hi,

Hope someone can help me. Should be straight forward. I am having a hard time understanding this:

The following query returns the right results as expected.

SELECT
S.companyname AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
LEFT JOIN Production.Products AS P
ON S.supplierid = P.supplierid
WHERE S.country = N'Japan';


The following query, which I thought should show me the same results as above does not. :(


SELECT
S.companyname AS supplier, S.country,
P.productid, P.productname, P.unitprice
FROM Production.Suppliers AS S
LEFT JOIN Production.Products AS P
ON S.supplierid = P.supplierid
and S.country = N'Japan';


can someone explain the behaviour, please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 11:18:35
see

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

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

Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-08 : 12:20:00
A description of the behavior in words might be stated this way:

The first query is telling SQL Server this:

"Give me those the rows from Suppliers where the country is Japan. And, oh, by the way, include a few columns (productid, productname etc) from the products table if these supplier ids are found in the products table."

The second query is telling SQL Server this:

"Give me all the rows from Suppliers. And, oh, by the way, include a few columns (productid, productname etc) from the products table if these supplier ids are found in the products table AND if the supplier is from Japan"
Go to Top of Page

dolphin123
Yak Posting Veteran

84 Posts

Posted - 2013-02-08 : 22:29:33
Thanks a lot guys.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-08 : 23:18:26
welcome

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

Go to Top of Page
   

- Advertisement -