Should I use EXISTS or IN
I have two tables:
Foo with 24.000.000 rows
Bar with 16 rows
I'm considering rewriting query
SELECT * FROM Foo as F
WHERE EXISTS (SELECT 1 FROM Bar as B WHERE B.Baz = F.Baz)
with this one
SELECT * FROM Foo
WHERE Baz IN (SELECT Baz FROM Bar)
Edit: A third option was suggested in the comments. I didn't consider
joining because I don't need any columns from Bar
SELECT * FROM Foo as F
JOIN Bar as B on B.Baz = F.Baz
But after looking at the execution plans for both queries I couldn't spot
the difference. Are these queries really equivalent? Which query is
better?
What should I consider when deciding between EXISTS and IN. I was
wondering if SQL Server is smart enough to execute the nested query once
and store the result for comparison, or does it execute the nested query
for each row?
No comments:
Post a Comment