Including specific records using a LEFT Join in SQL Server

Question

I have two tables joined through a left join. I want to include all records in the Borrowers table but only records where Loan_Type = 'Auto' in the Loans table.

Here is all of the code in a working SQL Fiddle example:

SQLFiddle

Answer 1

Instead of specifying the criteria in the WHERE clause, simply add the criteria to the join instead:

Rewrite this:

LEFT JOIN Loans l ON b.Loan_ID = l.Loan_ID
WHERE l.loan_type = 'Auto'

to this:

LEFT JOIN Loans l ON b.Loan_ID = l.Loan_ID AND l.loan_type = 'Auto'

WHERE filters the entire row from the resultset, whereas additional criteria in the joins will only filter that part of the join.