Will "WHERE x IN (SELECT value FROM table) " be inefficient for large tables?

Question

The following two SQL queries return the same results:

    SELECT * FROM Table1
    WHERE Table1.Value1 = 'something' OR Table1.Value2 IN (SELECT Value2 FROM Table2)

    SELECT * FROM Table1
    LEFT JOIN Table2 
    ON Table1.Value2 = Table2.Value2
    WHERE (Table1.Value1 = 'something' OR Table2.Value2 IS NOT NULL)

Similarly, these two queries return the same results:

    SELECT * FROM Table1
    WHERE Table1.Value1 = 'something' AND Table1.Value2 NOT IN (SELECT Value2 FROM Table2)

    SELECT * FROM Table1
    LEFT JOIN Table2
    ON Table1.Value2 = Table2.Value2
    WHERE Table1.Value1 = 'something' AND Table2.Value2 IS NULL

Personally, I find it easier to read the options that use "IN" or "NOT IN" (especially since my real query already has a pile of joins in it). However, if the number of values in Table2 grows large (currently it only returns three results), will this query become inefficient? Or will the query optimizer figure it out and turn it into a join behind the scenes? I'm using SQL Server 2012.

Answer 1

The first would be better as:

SELECT <cols> 
  FROM dbo.Table1
  WHERE Value1 = 'something' 
  OR EXISTS (SELECT 1 FROM dbo.Table2 WHERE Value2 = Table1.Value2);

Though your performance problem - assuming Value2 is indexed in both locations and you are really only going to select the columns you need instead of forcing a scan or a lookup using * - is going to be the OR. You might consider this alternative, if Value1 is properly indexed, at least to test the difference in performance (you'll want to look at the plans, not just measure time, while you have just three rows):

SELECT <cols>
  FROM dbo.Table1 
  WHERE Value1 = 'something'
UNION ALL
SELECT <cols>
  FROM dbo.Table1
  WHERE Value1 <> 'something'
  AND EXISTS (SELECT 1 FROM dbo.Table2 WHERE Value2 = Table1.Value2);

For the NOT IN query, this will be both more reliable and at least as efficient as the two options you offered:

SELECT <cols>
  FROM dbo.Table1
  WHERE Value1 = 'something' 
  AND NOT EXISTS (SELECT 1 FROM dbo.Table2 WHERE Value2 = Table1.Value2);

Indexing is going to be key here, but it is important to understand NOT IN and LEFT OUTER JOIN can throw you in a hole. See the following article:

http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join