Describe and Write Subqueries in SQL

As the name implies, a subquery^[1] is a query within a query, sometimes called a nested query. A subquery can be nested inside the WHERE or HAVING clause of an outer statement, or inside another subquery. Up to 32 levels of nesting are possible in SQL Server 2025, though the practical limit depends on available memory and how complex the rest of the query is.

A subquery can often be used in place of joining two tables together. Generally, there is no meaningful performance difference between using a join across multiple tables and using an equivalent subquery, with one notable exception: when existence needs to be checked, a join typically performs better than a subquery built around EXISTS. In that specific case, it is usually preferable to construct a join instead.

A subquery is written as a nested SELECT statement enclosed in parentheses, and can appear in a WHERE, FROM, SELECT, or HAVING clause. Subqueries are generally categorized by what they return:

• A scalar subquery returns a single value.
• A row subquery returns a single row.
• A table subquery returns a full result table, multiple rows and columns.

A subquery used with the IN keyword follows this general syntax:

SELECT column(s)
FROM table(s)
WHERE column IN (subquery)

An IN keyword in a WHERE clause restricts a query's results to values that match a list of values produced by the subquery. You can use a subquery anywhere an expression is allowed within a SELECT, UPDATE, INSERT, or DELETE statement, with one exception: it cannot appear in the ORDER BY clause of any of these statements.

SELECT Salary
FROM Employees
WHERE EmployeeID IN (SELECT EmployeeID
FROM ActiveEmployee)

This retrieves salaries only for employees who also appear in the ActiveEmployee table. Conceptually, SQL Server evaluates the innermost query first, building a result set of EmployeeIDs from ActiveEmployee, and then selects salaries from Employees only where the EmployeeID matches one from that result set. This innermost-first model is how Microsoft's own documentation explains nested subqueries, though it describes the logical result rather than a guarantee of exactly how the query optimizer executes the statement internally; the optimizer is free to choose a different physical execution plan that produces the same result.

Beyond the IN form above, a few other subquery patterns are worth knowing.

Scalar subquery used as an expression. A subquery that returns a single value can be used directly in the select list:

SELECT Name, (SELECT AVG(Salary) FROM Employees) AS AvgSal
FROM Employees;

Multi-row subquery with IN or EXISTS. Already covered above; the subquery produces a list of values the outer query filters against:

SELECT * FROM Orders
WHERE CustomerID IN (SELECT CustomerID FROM Customers WHERE City = 'NY');

Derived table. A subquery used in the FROM clause, standing in for a table. A derived table must be given an alias:

SELECT d.Dept, s.Total
FROM Departments d
JOIN (SELECT DeptID, SUM(Amount) AS Total FROM Sales GROUP BY DeptID) s
ON d.DeptID = s.DeptID;

Correlated subquery. Unlike the subqueries above, a correlated subquery references a column from the outer query, which means it cannot be evaluated independently; it runs once for each row the outer query considers:

SELECT * FROM Orders o
WHERE Amount = (SELECT MAX(Amount) FROM Orders WHERE CustomerID = o.CustomerID);

For row-by-row logic that goes beyond what a correlated subquery can express, CROSS APPLY and OUTER APPLY extend the same idea further, letting you invoke a table-valued expression once per row of the outer query.

As a general rule, prefer a join over an equivalent subquery when performance matters and the two are truly interchangeable, since the query optimizer often has more room to work with a join.

A short recap before the next lesson:

• A subquery is a SELECT statement nested inside another statement, usable in a WHERE, FROM, SELECT, or HAVING clause, up to 32 levels deep.
• Scalar, row, and table subqueries are categorized by what they return; derived tables and correlated subqueries describe how a subquery is used rather than its return shape.
• A correlated subquery depends on the outer query and runs once per outer row, unlike the other kinds covered here, which can be evaluated independently.
• Joins and subqueries usually perform about the same, with existence checks (EXISTS) being the main case where a join tends to be faster.

In the next lesson, you will learn how to construct distributed select statements to retrieve data across multiple servers.

Click the exercise link below to practice creating a subquery.
Using Subqueries - Exercise

[1] Subquery: a query nested inside another query, used to supply a value, a row, or a result table that the outer query depends on.