Subqueries

A subquery is a query nested inside another query, used to filter, compute, or supply values based on the result of that inner query.

Subqueries in WHERE

A subquery can return a list of values used with IN, or a single value compared directly. This lets you filter based on a calculation that depends on another table entirely.

SELECT FirstName, LastName
FROM Customers
WHERE CustomerId IN (
    SELECT CustomerId FROM Orders WHERE Total > 1000
);

Correlated Subqueries and EXISTS

A correlated subquery references a column from the outer query, re-running conceptually for each outer row. EXISTS checks only whether any matching row exists, often performing better than IN for this kind of check since it can stop at the first match.

SELECT c.FirstName, c.LastName
FROM Customers c
WHERE EXISTS (
    SELECT 1 FROM Orders o
    WHERE o.CustomerId = c.CustomerId AND o.Total > 1000
);

Common Mistakes

• Using a subquery that returns multiple rows where only a single value is expected, causing a runtime error.
• Choosing IN over EXISTS (or vice versa) without considering performance on large tables — EXISTS often performs better for existence checks.
• Forgetting that a correlated subquery references the outer query's columns, and mistyping the outer alias.
• Overcomplicating a query with nested subqueries where a simple JOIN would be clearer and often faster.