most efficient query to get record from a table with IDS

Question

I have a table with purpose of holding id's. I want to select from other table ( a big table of millions of records) also many records.

Which one would outperform:

SELECT id, att1, att2 
FROM myTable 
WHERE id IN (SELECT id FROM @myTabwithIDS)

Or

SELECT id, att1, att2 
FROM myTable t
INNER JOIN @myTabwithIDS t2
    ON t2.id = t.id

score 1 · Accepted Answer · edited May 23 '17 at 12:11

I would use the Query Analyzer built in to SQL Server to explore the execution plan.

http://www.sql-server-performance.com/2006/query-analyzer/

Specifically turn on Show Execution Plan, and Statistics IO and Time.

Normally a join is better than a subquery, especially in your case where the outer queries condition depends on the results of the subquery (known as a correlated subquery). See Subqueries vs joins for more details.

most efficient query to get record from a table with IDS

1 Answers1