CODEWITHSUNDEEP
sql-servert-sql
hidarikani
hidarikani

Reputation: 1131

Should I use EXISTS or IN

I have two tables:

  1. Foo with 24.000.000 rows
  2. 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?

Upvotes: 2

Views: 112

Answers (2)

Martin Smith
Martin Smith

Reputation: 453887

Either EXISTS or IN are fine.

Both should give you the same plan with a logical semi join operator (NULL does not change the semantics here unlike NOT IN/NOT EXISTS)

Replacing with an INNER JOIN could change the results except if Baz is guaranteed to be unique in Bar.

Without this constraint an inner join could bring back additional rows that you would then need to get rid of with DISTINCT.

Upvotes: 4

jle
jle

Reputation: 9489

Wouldn't this be the same as well but less confusing? The scenario you describe lends itself to an inner join.

SELECT F.* FROM Foo as F inner join Bar as B on F.Baz=B.Baz

Upvotes: 0

Related Questions