Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Doubt Left Join Expand / Collapse
Author
Message
Posted Wednesday, April 28, 2010 2:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, April 28, 2012 6:21 AM
Points: 38, Visits: 102
Please explain different between the queries

SELECT *
FROM EmpAddress addr
LEFT JOIN EmpEducation edu
ON addr.empid = edu.empid
--WHERE EDU.EMPID IS NULL
WHERE addr. AddType = 2


SELECT *
FROM EmpAddress addr
LEFT JOIN EmpEducation edu
ON addr.empid = edu.empid AND addr. AddType =
Post #911707
Posted Wednesday, April 28, 2010 3:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, May 22, 2014 4:35 AM
Points: 172, Visits: 258
Is this Left outer join?

If yes.
1. "addr.empid = edu.empid"-----This is join condition
2. "addr. AddType = 2"----This is filter condition

filter condition should not be mixed with join condition. But in this scenario there should not be any difference in the resultset of both.


Post #911710
Posted Wednesday, April 28, 2010 5:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
vinodpec (4/28/2010)
Please explain different between the queries

SELECT *
FROM EmpAddress addr
LEFT JOIN EmpEducation edu
ON addr.empid = edu.empid
--WHERE EDU.EMPID IS NULL
WHERE addr. AddType = 2


SELECT *
FROM EmpAddress addr
LEFT JOIN EmpEducation edu
ON addr.empid = edu.empid AND addr. AddType =


the 1st query will show only results where from empaddress where addtype=2 - it will then join edu table and show those fields as well (but will not use this to limit rows returned)

the second query will will show all records from empaddress regardless off addtype and will then join edu table and show any related data - again it will not be used to filter the record set

query 1 will return less records
query 2 will have more blank data in the columns for edu table where the addr table <>2



MVDBA
Post #911782
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse