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:
FROM   myTable
WHERE  myTableId NOT IN (SELECT myTableId FROM myOtherTable)
…but that sometimes generates rather poor query plans. An alternative is:
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.
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: Logo

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

Google photo

You are commenting using your Google 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 )

Connecting to %s