0

Suppose I have the following code:

SELECT * 
FROM [myTable]
WHERE [myColumn] IN (SELECT [otherColumn] FROM [myOtherTable])

Will the subquery be executed again and again for every row?

If so, can I execute it and store its results and use them for every row instead? For example:

SELECT [otherColumn] 
INTO #Results 
FROM [myOtherTable]

SELECT * 
FROM [myTable]
WHERE [myColumn] IN (#Results)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Idan Yadgar
  • 974
  • 1
  • 6
  • 16
  • The optimizer should cache or make a temp table so it doesn't execute the subquery repeatedly. It's relatively good at that. You should also check out `EXISTS` – Jacob H Oct 13 '17 at 17:45
  • No it’s not bad for performance, SQL Server can choose hash join or merge join (which evaluate the sub query once) or nested loops which will execute different parts of the sub query for each outer row. – Martin Smith Oct 13 '17 at 17:49
  • You won't get optimal performance until you 1) index 2) add to the where clause. – Lee Oct 13 '17 at 20:54

2 Answers2

2

SQL server query optimizer is smart enough to not run the same subquery over and over again. If anything, the temp table is less optimal because of additional steps after getting the results.

You can see this by looking at the SQL query execution plan.

enter image description here

Edit: After looking into this further, it can also be more than once. Apparently query optimizer can also do a lot of interesting things like convert your IN to a JOIN to increase performance. There's lots of information on it here: Number of times a nested query is executed

None the less, view your execution plan to see what your RDMS's query optimizer decided to do.

justiceorjustus
  • 2,017
  • 1
  • 19
  • 42
1

Have you considered using a join instead? I think that could be best in terms of performance.

SELECT * FROM [myTable] INNER JOIN [myOtherTable]
ON ([myTable][myColumn] = [myOtherTable][otherColumn]);

This however will only work if you don't expect duplicates to be in myOtherTable.

amburt05
  • 484
  • 3
  • 6