[thelist] SQL replace subquery with join

[Jason Handby]

> 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.

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

SELECT cd.* FROM cd
    LEFT JOIN (SELECT genre.cd FROM genre WHERE genre.name = 'rock')
therockones
    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?



Jason