Useful (?) Tip #2

See? I forgot to do one yesterday, but at least I remembered to catch up…
 
This is more of a SQL optimisation trick. You sometimes need to get a result set containing items NOT in another result set. The obvious code would be:
 
SELECT *
FROM   myTable
WHERE  myTableId NOT IN (SELECT myTableId FROM myOtherTable)
 
…but that sometimes generates rather poor query plans. An alternative is:
 
SELECT *
FROM   myTable M
       LEFT JOIN myOtherTable O ON M.myTableId = O.myTableId
WHERE  O.myTableId IS null
 
One obvious consequence is that you’ll get more columns available, so don’t really use * in your select.
 
Compare your query plans and see which one is better for you.
Advertisements
This entry was posted in Computers and Internet. Bookmark the permalink.

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