All 7 Subqueries questions
More in Subqueries
What is a subquery in SQL Server, and how is it typically used within a larger query? What is the difference between a correlated and a non correlated subquery? How do you use a subquery within the SELECT list to calculate a related aggregate value alongside each row of the outer query? What are common mistakes developers make when writing subqueries that can lead to incorrect results or poor performance? How would you rewrite a slow correlated subquery as an equivalent, potentially faster JOIN based query? How does the SQL Server query optimizer typically handle and potentially transform a subquery internally when generating an execution plan?
Advertisement
Back to results for “Joins”
SQL Server › Subqueries

What is the difference between using EXISTS and IN when checking for the presence of related data with a subquery?

Intermediate

Answer

EXISTS checks only whether the subquery returns any rows at all, stopping as soon as it finds a match, which can be more efficient for large data sets, while IN compares a specific value against the full list of values returned by the subquery, and can behave unexpectedly if that list happens to contain a NULL value, sometimes leading to surprisingly no results at all.
-- EXISTS often performs better and avoids NULL related surprises
SELECT * FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerId = c.CustomerId);
Real-world example A developer switches a slow query using IN with a large subquery to use EXISTS instead, both improving performance and avoiding a subtle bug caused by an unexpected NULL value in the subquery's results.

Common follow-ups: Why does a NULL value in the subquery results cause problems specifically with NOT IN?;Is there ever a case where IN is actually the better choice over EXISTS?

Joins;Data Types & Schema Design
All 7 Subqueries questions