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? What is the difference between using EXISTS and IN when checking for the presence of related data with a subquery? How do you use a subquery within the SELECT list to calculate a related aggregate value alongside each row of the outer query? 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 “Subqueries”
SQL Server › Subqueries

What are common mistakes developers make when writing subqueries that can lead to incorrect results or poor performance?

Intermediate

Answer

Common mistakes include using NOT IN with a subquery that might return NULL values, leading to unexpectedly empty results, writing a correlated subquery when a simple join would perform significantly better, and forgetting that a subquery returning multiple rows will cause an error if used in a context expecting only a single scalar value.
-- Problematic if OrderId can contain NULL
SELECT * FROM Customers WHERE CustomerId NOT IN (SELECT CustomerId FROM Orders WHERE CustomerId IS NOT NULL);
Real-world example A developer debugging a report that mysteriously returned zero results discovers a NOT IN subquery was silently failing due to an unexpected NULL value, and fixes it by explicitly filtering out NULLs or switching to NOT EXISTS instead.

Common follow-ups: How do you defensively write subqueries to avoid this kind of NULL related issue?;What error occurs when a subquery expected to return one value actually returns several?

Data Types & Schema Design;Query Optimization & Plans
All 7 Subqueries questions