Does a select within a where clause affect performance?

Question

I know that we should avoid using functions in the where clause of a query wherever possible since the function will run for every row to return the proper results. However, does the same principle apply to a nested select in a where clause?

for instance:

where col1 in (select col2 from table)

vs moving the select into a temporary table.

Answer 1

No, the same principle doesn't apply in the example you gave. The optimizer will generally try to convert that type of predicate against a derived table into a set-based construct like a JOIN or APPLY.

Using the StackOverflow2010 sample database on SQL Server 2019, I ran these two queries (after creating the two indicated nonclustered indexes):

/*
CREATE NONCLUSTERED INDEX IX_Reputation
ON dbo.Users (Reputation);

CREATE NONCLUSTERED INDEX IX_OwnerUserId
ON dbo.Posts (OwnerUserId);
*/

/* Sub-query / table expression in the IN clause */
SELECT p.Id
FROM dbo.Posts p
WHERE 
    p.OwnerUserId IN 
    (
        SELECT u.Id 
        FROM dbo.Users u 
        WHERE u.Reputation > 500000
    );

/* Materialize in a temp table first */
DROP TABLE IF EXISTS #tmpUsers;
CREATE TABLE #tmpUsers
(
    Id int NOT NULL PRIMARY KEY
);

INSERT INTO #tmpUsers
SELECT u.Id 
FROM dbo.Users u 
WHERE u.Reputation > 500000;

SELECT p.Id
FROM dbo.Posts p
WHERE 
    p.OwnerUserId IN 
    (
        SELECT u.Id 
        FROM #tmpUsers u 
    );

Both queries complete in about the same amount of time, with very similar plans (the temp table plan has a scan rather than a seek, because the seek is part of the temp table insert).

The estimates are slightly better on the non-temp table version, which might make a bigger difference with larger datasets.

As a side note, you can get much better estimates in this case by adding OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). For the same two queries, the estimates went from being 10-30 times the actuals, to being within 2-3 times the actuals. This is because the original cardinality estimation model performs better histogram matching than the simple 'coarse alignment' of the later CE (for some details on that change, see Paul White's article SQL Server Join Estimation using Histogram Coarse Alignment).