www.webdeveloper.com
Results 1 to 4 of 4

Thread: Which is faster/better?

  1. #1
    Join Date
    May 2007
    Posts
    184

    Which is faster/better?

    When comparing two tables to see what the differences are? Using an EXCEPT or a WHERE NOT EXISTS?

    I'm comparing tables where each have about 2000+ rows.

    Also, I usually only get about 5-10 different rows with the EXCEPT (the EXCEPT is what I'm currently using).

  2. #2
    Join Date
    Aug 2008
    Posts
    5
    Where Not Exists

  3. #3
    Join Date
    May 2007
    Posts
    184
    Quote Originally Posted by ksailesh
    Where Not Exists
    Can you give an explanation on why?

  4. #4
    Join Date
    Jul 2004
    Posts
    300
    except would be faster and better because that is what it's built for. it is also more legible. after all, what would you rather read:

    select ColumnA, ColumnB, ColumnC
    from TableA
    except
    select ColumnA, ColumnB, ColumnC
    from TableB

    or...

    select ColumnA, ColumnB, ColumnC
    from TableA a where not exists
    (select *
    from TableB b where
    Coalesce(a.ColumnA, '') = Coalesce(a.ColumnA, '')
    and Coalesce(a.ColumnB, '') = Coalesce(b.ColumnB, '')
    and Coalesce(a.ColumnC, '') = Coalesce(b.ColumnC, '')
    )

    Full Outer Join is also another option (with the same limitations as not exists) but if you have except available to you there is no reason why you should not use it. Again, that is what the except operator is built for!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles