CODEWITHSUNDEEP
sqlleft-join
MattFunke
MattFunke

Reputation: 33

SQL LEFT JOIN excludes members depending on what it's joined with?

I have a database where sometimes language entries are defined for particular item numbers in the "GHS_ItemStoreHandle" table, and sometimes not. Why does the first query return fewer items than the second query? Shouldn't both return a number of rows equal to the number of active languages in the "Language" table?

SELECT l.NativeName, l.Id, i.ItemNumber, i.StoreHandle FROM Language l 
  LEFT JOIN GHS_ItemStoreHandle i ON l.Code = i.Lang_Code 
  WHERE l.Active = 1 AND 
  (i.ItemNumber = 'MM1-931' OR i.ItemNumber IS NULL) 
  ORDER BY l.NativeName;

SELECT l.NativeName, l.Id, l.Active FROM Language l
  WHERE l.Active = 1
  ORDER BY l.NativeName;

Upvotes: 3

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You want the condition in the ON clause, not the WHERE clause:

SELECT l.NativeName, l.Id, i.ItemNumber, i.StoreHandle
FROM Language l LEFT JOIN
     GHS_ItemStoreHandle i
     ON l.Code = i.Lang_Code AND i.ItemNumber = 'MM1-931'
WHERE l.Active = 1 
ORDER BY l.NativeName;

The reason your version returns unexpected results is because sometimes i does not have MM1-931' but has another value. These are filtered out by your logic.

Upvotes: 3

Related Questions