[thelist] SQL replace subquery with join

Jason Handby jason.handby at corestar.co.uk
Tue Jan 5 09:56:48 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
> with
> (cd, name) where name might be "rock" or "jazz".
> For the sake of this question a cd can have many genres (zero or
> 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.

I'm not sure there is, not without a subquery hidden in it, e.g.

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

Your version using IN looks fine to me, anyway, what's wrong with doing
it that way?


More information about the thelist mailing list