Home Forums Programming General Selecting data in a table that are not in the other table RE: Selecting data in a table that are not in the other table

  • EXISTS (or NOT EXISTS in this case) can be cleaner and more intuitive as to what it's doing over a join/is null check.

    Using John's tables

    SELECTb.id

    , b.name

    FROM@records r

    join@books b on b.id = r.book_id

    join@lists l on l.id = r.list_id

    join@classes c on c.id = l.class_id

    join@users u on u.class_id = c.id

    whereu.id=1

    and r.list_id=1

    AND NOT EXISTS (SELECT 1 FROM @selection s WHERE r.book_id = s.book_id AND s.user_id = u.id)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass