Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

WHERE conditions on a LEFT JOIN

From Wiki

Revision as of 03:17, 8 April 2010 by Naomi (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

WHERE conditions on a LEFT JOIN

A problem that happens occasionally in T-SQL code is the incorrect use of the left join. Let’s examine the queries below

  1. SELECT  a.field1
  2.     , a.field2
  3.     , b.field3
  4. FROM table1 a
  5. JOIN table2 b
  6.     ON a.id = b.id
  7. WHERE   a.field5 = 'test'
  8.     AND b.field3 = 1
  10. SELECT  a.field1
  11.     , a.field2
  12.     , b.field3
  13. FROM  table1 a
  14. LEFT JOIN table2 b
  15.     ON a.id = b.id
  16. WHERE   a.field5 = 'test'
  17.     AND b.field3 = 1

What would you expect to be the difference in the results set of these two queries? Many developers would say that they would expect the first query to return only the records that were in both tables and met the conditions of the where clause and the second would include all the records in both tables that met the conditions and any records in the first table which do not have a matching record in the second table. These developers would be wrong concerning the second query.

If you examine the where clause in the second query you will see that there is a condition on a field in table b. By doing so, we are, in effect, converting the left join into an inner join because that condition must be met in the recordset that is returned.

How do you fix this problem if you need where conditions on table2, but want the effect of a left join? The fix is to move the where condition to the join as shown below:

  1. SELECT  a.field1
  2.     , a.field2
  3.     , b.field3
  4. FROM table1 a
  5. LEFT JOIN table2 b
  6.     ON a.id = b.id AND b.field3 = 1
  7. WHERE a.field5 = 'test'

The only time you should have a where condition that invokes the table on the right side of a left join is when you want to find the records which exist in the first table but not the second. The code to do this is :

  1. SELECT  a.field1
  2.     , a.field2
  3. FROM  table1 a
  4. LEFT JOIN table2 b
  5.     ON a.id = b.id
  6.     WHERE   b.id IS NULL

Note that this can also be tricky to get correct as well since the field you choose in table2 for the where clause must not allow null such as the PK. A different way to perform the same task can be more performant and is less likely to get accidentally incorrect results:

  1. SELECT  a.field1
  2.     , a.field2
  3. FROM  table1 a
  4. WHERE NOT EXISTS (SELECT id FROM table2 c WHERE c.id = a.id and c.id IS NOT NULL)

You can skip the where clause in the subquery if the field you are comparing doesn’t allow nulls.

See also this blog Why LEFT JOIN doesn't bring all records from the LEFT table? discussing the same topic.

Part of SQL Server Programming Hacks

Section Pitfalls

689 Rating: 2.7/5 (94 votes cast)