0

Following two queries produce exactly the same execution plan on SQL Server 2008 R2.

But I feel like the second query should perform better.

Why do I see the same query execution plan for these two?

DECLARE @user_id INT;
SET @user_id = 13;

IF @user_id IN (SELECT [USER_ID] 
                FROM DBO.[USER] 
                WHERE HOME_CUSTOMIZATION IS NOT NULL)
BEGIN
    PRINT('USER HAS HOME CUSTOMIZATION...');
END
ELSE
BEGIN
    PRINT('USER HAS NO HOME CUSTOMIZATION...');
END;

IF EXISTS (SELECT [USER_ID] 
           FROM DBO.[USER] 
           WHERE [USER_ID] = @user_id 
           AND HOME_CUSTOMIZATION IS NOT NULL)
BEGIN
    PRINT('USER HAS HOME CUSTOMIZATION...');
END
ELSE
BEGIN
    PRINT('USER HAS NO HOME CUSTOMIZATION...');
END

Query Plans

paparazzo
  • 44,497
  • 23
  • 105
  • 176
BuddhiP
  • 6,231
  • 5
  • 36
  • 56
  • 2
    http://stackoverflow.com/questions/24929/difference-between-exists-and-in-in-sql – SubqueryCrunch Oct 29 '14 at 06:47
  • 1
    The query optimizer has done a good job with the first form and found the best way to satisfy the query. If that's the case, it's got no room to do a *better* job with the second query and so it won't perform any better. Since both forms produce identical plans, choose the one that you think reads better and use it. – Damien_The_Unbeliever Oct 29 '14 at 08:12
  • 2
    Why would you be surprised that the query optimizer did the right thing? – paparazzo Oct 29 '14 at 14:59
  • Your assumption is correct in that the second approach should be faster if MSSQL would actually do what you tell it to do. However, the Query Optimizer will look at your statement and look for ways to more efficiently get the requested *result*. As such, there often is a difference between what you tell the system to do (the query statement) and what it actually does (the execution plan). The Query Optimizer will use the statistics information on the tables involved to figure out what 'most efficient' would be; therefore it's important to make sure these are kept up-to-date at all times. – deroby Oct 31 '14 at 16:28

0 Answers0