Subqueries and EXISTS
A subquery is a query inside another query. This lesson covers subqueries that return one value, a list or a table, correlated subqueries that run per row, and EXISTS and NOT EXISTS, the safest and often fastest way to ask whether related rows exist.
- StartOuter rowTake one customer
- Step 2Inner queryLook for an order with the same CustomerId
- Step 3First matchStop searching: EXISTS is true
- Step 4No matchEXISTS is false; NOT EXISTS keeps the row
- EndNext rowRepeat; an index on Orders(CustomerId) keeps it fast
Three shapes of subquery
- Scalar: returns one value, used like a constant. WHERE Price > (SELECT AVG(Price) FROM Products).
- List: returns one column, used with IN. WHERE CategoryId IN (SELECT CategoryId FROM Categories WHERE IsActive = 1).
- Table (derived table): used in FROM with an alias, like a temporary view for one query.
Correlated subqueries
A correlated subquery refers to the outer row, so logically it runs once per row. Here each product is compared with the average of its own category.
SELECT p.ProductId, p.Name, p.Price
FROM Products p
WHERE p.Price > (
SELECT AVG(p2.Price)
FROM Products p2
WHERE p2.CategoryId = p.CategoryId -- refers to the outer row
);EXISTS and NOT EXISTS
EXISTS is true as soon as one matching row is found, so SQL Server can stop early. NOT EXISTS is the reliable way to find rows with no match, and unlike NOT IN it behaves correctly when NULLs are involved.
-- Customers who ordered in the last 30 days
SELECT c.CustomerId, c.Name
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o
WHERE o.CustomerId = c.CustomerId
AND o.OrderedAt >= DATEADD(day, -30, SYSUTCDATETIME()));
-- Products that were never ordered
SELECT p.ProductId, p.Name
FROM Products p
WHERE NOT EXISTS (SELECT 1 FROM OrderLines ol WHERE ol.ProductId = p.ProductId);Subquery, JOIN or CTE?
The optimizer often turns IN and EXISTS into joins internally, so pick the form that states your intent most clearly: EXISTS for "has any", JOIN when you need columns from both tables, and a CTE when the query has several steps.
A JOIN can multiply rows when the other side has many matches; EXISTS never does. On big tables make sure the correlated column (o.CustomerId above) is indexed.
In EF Core, .Any() in LINQ becomes EXISTS and !.Any() becomes NOT EXISTS.
Interview question
Why is NOT EXISTS safer than NOT IN?
If the subquery behind NOT IN returns even one NULL, the comparison with that NULL is unknown for every row, so NOT IN returns no rows at all. NOT EXISTS only checks whether a matching row exists, so NULLs do not change the answer. It also lets SQL Server stop at the first match and is usually planned as an efficient anti semi join.
Want the full details? Read the official Microsoft documentation for this topic.