SQL Server left join behavior

Question

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?

You could use also an INNER JOIN which would also only return matching rows. The LEFT JOIN "becomes an inner join" only if you use the keyword INNER. Both join types can have one or multiple join-conditions. — Tim Schmelter
Perhaps your tables are actually views? Your posted queries should return the same resultset (though the order of rows might be different since you have no order by clause). So absent a mistake (e.g., a different database), what you see should not be possible. To double check - which is a habit you should develop - replace the where clause with one based on PracticeTypeID. You should get the same 2 rows. — SMor

Jeffrey Van Laethem Jeffrey Van Laethem · Accepted Answer · 2017-09-22T15:18:13

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.

SQL Server left join behavior

1 Answers