Post reply

Finding all records in TableB which don't exist in TableA

  • September 24, 2009 at 7:04 am

    #141913

    Somewhat theoretical question, I suppose - which of these optimal to use when you're looking for all records in a given table that are not contained in another table?

    SELECT 1

    FROM TableA

    LEFT JOIN TableB ON TableA.ColA = TableB.ColA

    WHERE TableB.ColA IS NULL

    vs

    SELECT 1

    FROM TableA

    WHERE NOT EXISTS(SELECT 1 FROM TableB WHERE TableA.ColA = TableB.ColA)

    And, is there another solution which is more performant?

  • September 24, 2009 at 7:15 am

    #1057989

    I usually use not exist. But you can check the performance by looking at the statistics. Also if you are looking at SQL 2008, You should check SOME/ANY/ALL

    -Roy

  • September 24, 2009 at 7:17 am

    #1057990

    In terms of performance, I think the answer is probably 'it depends'. In my experience they tend to generate pretty similar execution plans so it's often just down to personal preference.

    Personally, I much prefer doing a LEFT Join as it reads much better to me and you're explicitely defining the join.

    For me, a bracketed sub-query should be an isolated query that could be run stand-alone and it's just weird to reference a column that is out of the scope of that query, but that might just be me! 😀

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply