CODEWITHSUNDEEP
sql-serverleft-join
jackstraw22
jackstraw22

Reputation: 641

SQL Server left join behavior

I'm having an issue with left joins. Every time I think I understand them I run across an issue. In the following query I'm only trying to return records where PracticeTypeName = 'Facility.' I know for a fact that PracticeTypeID's 13328 and 502 exist in both Table A and Table B, so there shouldn't be any NULL values in this instance. PracticeTypeID = 502 is PracticeTypeName = 'Billing', so it should not be returned. I'm using a left join to account for other cases where the ID's don't match.

    Select Memberid, locationname, practicetypeid, practicetypename
    From A
    Left Join B
    On A.PracticeTypeId = B.PracticeTypeID
    And A.PracticeTypeName = 'Facility'

This query returns the following result

    MemberID     LocationName     PracticeTypeID    PracticeTypeName
    10            UHC                13328               Facility
    10            TV                  502                NULL

But if use this query instead:

    Select Memberid, locationname, practicetypeid, practicetypename
    From A
    Left Join B
    On A.PracticeTypeId = B.PracticeTypeID
    Where A.PracticeTypeName = 'Facility'

I get the following result, which is what I'm looking for:

    MemberID     LocationName     PracticeTypeID    PracticeTypeName
    10            UHC                13328               Facility

I thought that with left joins you would use the following, otherwise the left join just becomes an Inner Join.

   On A.PracticeTypeId = B.PracticeTypeID
   And A.PracticeTypeName = 'Facility'

Why is this left join doing the reverse of what I thought a left join should do?

Upvotes: 0

Views: 557

Answers (1)

Jeffrey Van Laethem
Jeffrey Van Laethem

Reputation: 2651

The WHERE clause will filter on your entire result set, whereas adding your condition to the JOIN will only join on those rows. Since it's a LEFT JOIN, putting the condition in the JOIN clause will still return rows that have no match, with NULL for the joined table's columns.

The WHERE looks at your results and says "Which rows have Facility?", and returns those.

Upvotes: 2

Related Questions