[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 :)?
Liz
(hoping for a blinding flash of clarity!)
More information about the thelist
mailing list