TSQL Operators IN vs INNER JOIN

Question

Using SQL Server 2014:

Is there any performance difference between the following statements?

DELETE FROM MyTable where PKID IN (SELECT PKID FROM @TmpTableVar)

AND

DELETE FROM MyTable INNER JOIN @TmpTableVar t ON MyTable.PKID = t.PKID

Answer 1

In your given example the execution plans will be the same (most probably).

But having same execution plans doesn't mean that they are the best execution plans you can possibly have for this statement.

The problem I see in both of your queries is the use of the Table Variable.

SQL Server always assumes that there is only 1 row in the table variable. Only in SQL Server 2014 and later version this assumption has been changed to 100 rows.

So no matter how many rows you have this the table variable SQL Server will always assume you have one row in the @TmpTableVar.

You can change your code slightly to give SQL Server a better idea of how many rows there will be in that table by replacing it with a Temporary table and since it is a PK_ID Column in your table variable you can also create an index on that table, to give best chance to sql server to come up with the best possible execution plan for this query.

SELECT PKID INTO #Temp
FROM @TmpTableVar

-- Create some index on the temp table here .....

DELETE FROM MyTable
WHERE EXISTS (SELECT 1 
              FROM #Temp t
              WHERE MyTable.PKID = t.PKID)

Note

In operator will work fine since it is a primary key column in the table variable. but if you ever use IN operator on a nullable column, the results may surprise you, The IN operator goes all pear shape as soon as it finds a NULL values in the column it is checking on.

I personally prefer Exists operator for such queries but inner joins should also work just fine but avoid IN operators if you can.