[thelist] SQL left join - it works, but why?

Liz Lawson lizlawson at charitycards.co.uk
Tue Jul 18 11:09:50 CDT 2000

Hi List...

I have a linking table products_categories with columns prodId and catId.
The columns are not unique but each prodId / catId record is.

I need to pull out any products in a category which don't appear in any
other categories. The query below seems to work:

SELECT a.prodId, a.catId, b.catId
  FROM  products_categories a
         LEFT JOIN products_categories b ON (a.prodId = b.prodId AND b.catId
!= a.catId)

 WHERE a.catId= '5' AND b.catId IS NULL;

What I don't understand is why!

My understanding is that left join means every record in the first table
gets a row in the results, but if the joined tables don't have any matching
records the returned row will have nulls. That's why I get a null for the
join where a.catId = b.catId.

But if a prodId has several different catIds, one of which is my selected
category, when I do the join why don't I get a null for that prodId where
a.catId = b.catId?

Does a row in SQL depend on the other rows (ie will return a null for a left
join only if no other rows are returned for that record in the first table),
or am I (as usual) thinking very hard in the wrong direction :)?

(hoping for a blinding flash of clarity!)

More information about the thelist mailing list