September 20, 2012 at 12:43 pm
Well answer to this is "it depends" on situation to situation, like: volume of data, indexes on columns and selectivity of rows.
IN & NOT IN generally should be avoided and only be used when filtering some set of values or constants. I generally do not prefer to put sub-queries with IN & NOT IN clause. IN & NOT IN may return incorrect data while dealing with NULL values. Check this blog post: http://sqlwithmanoj.wordpress.com/2011/02/15/not-in-not-exists-joins-with-null-values/[/url]
You can use EXISTS() instead of IN(), like:
SELECT 1 FROM ABC X WHERE EXISTS (SELECT * FROM ABC1 Y WHERE Y.ID = X.ID)
... also you can use JOINS instead of IN() as you've mentioned in your original question. Check the execution plan and cost of queries with EXISTS() & JOINS, and choose the one which you see more performant.
September 20, 2012 at 1:07 pm
manub22 (9/20/2012)
IN & NOT IN generally should be avoided and only be used when filtering some set of values or constants.
Why?
IN & NOT IN may return incorrect data while dealing with NULL values.
IN's behaviour does not change in the presence of nulls. NOT IN does, but it is not incorrect data. Unexpected possibly, but it is completely correct by the definition and documented behaviour of NOT IN
... also you can use JOINS instead of IN() as you've mentioned in your original question. Check the execution plan and cost of queries with EXISTS() & JOINS, and choose the one which you see more performant.
http://sqlinthewild.co.za/index.php/2010/04/27/in-exists-and-join-a-roundup/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply