Developing Matt

My Technical Journal

Comparing Data between Tables.

leave a comment »

Exists is
Quicker
Cleaner
Nicer
Than Not Exists is
Quicker
Cleaner
Nicer
Than Joins are
Quicker
Cleaner
Nicer
Than Subqueries are
Quicker
Cleaner
Nicer than a
Pen.

If you are comparing data between tables use EXISTS if you can.  The reason is that it checks for the first instance and, if true. returns true to the calling query.  A table scan is technically called, but it’s much more efficient. 

select * from tableA a where exists (select 1 from tableB b where b.name=a.username)

From a performance standpoint the next best thing is NOT EXISTS.  Logically this makes sense is that it has to do a full table scan to determine whether the value actually exists or not….but it’s still quicker than a join.

select * from tableA a where not exists (select 1 from tableB b where b.name=a.username)

Next on the list is a join. This is how many compare data between tables.  It’s usally how I do it when just trying to get data (not implementing in a stored procedure).  It’s not as friendly or easy to read, so the above two are better.  The idea is to do a left join and then check the primary table for nulls.  It’s not as efficient because it has to do a join to all the records.  It goes something like.

select* from tableA a left join tableB b on a.username = b.name where a.name is null

Subqueries are traditionally a bad way to go.  Avoid them.  Highly innefficient.  In this case the In statement would be utilized.

select * from tableA where username in (select name from tableB)

Last on the list is writing out all the data with pen and paper.  Yeah, that would be slow. 

Advertisements

Written by matt

June 26, 2008 at 10:01 am

Posted in Sql Server, T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: