[thelist] SQL replace subquery with join

Bill Moseley moseley at hank.org
Tue Jan 5 09:22:35 CST 2010


Ok, I'm ready for a "doh!" moment.

I have a database to manage my CD collection (yes, a bit dated example).

Two tables, one is the cd table with (id, name) and another table genre with
(cd, name) where name might be "rock" or "jazz".
For the sake of this question a cd can have many genres (zero or more).

Say I want a list of CDs that do NOT have a genre of "rock".

I could use a sub-query:

SELECT cd.*
FROM cd
WHERE cd.id NOT IN (select cd from genre where name = 'rock')

I know there's a way to use a join for this, though.

SELECT cd.*
FROM
    cd
    LEFT JOIN genre ON genre.cd = cd.id
WHERE
    (genre.name IS NULL or genre.name != 'rock')

But, of course, I get CDs I do not want.  That is if a cd has rock, jazz,
and alternative genres associated then I'll still get the jazz and
alternative rows.

I want distinct CDs and only ones that don't have "rock" as their genre.

-- 
Bill Moseley
moseley at hank.org


More information about the thelist mailing list