Subqueries

Learn how to nest queries in SQL Server using subqueries in WHERE and SELECT, and the basic difference between correlated and non-correlated subqueries.

Introduction

Sometimes a single query needs to depend on the result of another query — for example, finding all employees who earn more than the average salary. A subquery is a query nested inside another query, and SQL Server allows them in several places, including WHERE and SELECT. This lesson introduces the most common subquery patterns.

What You Will Learn

What a subquery is and where it can appear.
How to use a subquery inside a WHERE clause.
How to use a subquery inside a SELECT list.
The difference between correlated and non-correlated subqueries.

What is a Subquery?

A subquery is simply a SELECT statement written inside another SQL statement, enclosed in parentheses. The outer query is often called the main query, and the inner one is the subquery, or sometimes an "inner query." Subqueries are evaluated as part of resolving the outer query.

Subqueries in WHERE

A common use of subqueries is inside a WHERE clause, to filter rows based on a value calculated from another query — such as comparing each employee's salary to the overall average.

Subquery in WHERE

SELECT FirstName, Department, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

Result

Click Run to see what this code prints.

Subqueries are also used with IN to check membership against a set of values returned by another query.

Subquery with IN

SELECT CustomerName
FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders WHERE OrderTotal > 5000);

Result

Click Run to see what this code prints.

Subqueries in SELECT

A subquery can also appear directly in the SELECT column list, as long as it returns exactly one value per outer row. This is often used to attach a summary value, like an average or a count, alongside each row of detail data.

Subquery in SELECT

SELECT
    FirstName,
    Salary,
    (SELECT AVG(Salary) FROM Employees) AS CompanyAverage
FROM Employees;

Result

Click Run to see what this code prints.

Correlated vs Non-Correlated Subqueries

A non-correlated subquery can run entirely on its own, independent of the outer query — the examples above are all non-correlated, since the inner SELECT AVG(Salary) FROM Employees does not reference anything from the outer query. A correlated subquery, by contrast, references a column from the outer query, so it must be re-evaluated for every row the outer query processes.

Correlated Subquery

SELECT e1.FirstName, e1.Department, e1.Salary
FROM Employees AS e1
WHERE e1.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees AS e2
    WHERE e2.Department = e1.Department
);

Result

Click Run to see what this code prints.

Here, the inner query references e1.Department from the outer query, which means it computes a different average for every department, rather than a single overall average. This makes it a correlated subquery.

Correlated Subqueries Can Be Slower

Because a correlated subquery logically re-executes for each row of the outer query, it can be less efficient on large tables than a non-correlated subquery or an equivalent JOIN. Always check the execution plan on performance-critical queries.

Common Mistakes

Avoid These Mistakes

Writing a subquery in the SELECT list that can return more than one row, which causes a runtime error.
Using a subquery with = when the subquery could return multiple rows — use IN instead.
Forgetting to alias tables in a correlated subquery, making it unclear which table each column reference belongs to.
Overusing correlated subqueries when a JOIN would be simpler and more efficient.

Best Practices

Use IN or EXISTS for subqueries that may return multiple rows.
Alias tables clearly in correlated subqueries to avoid ambiguity.
Consider rewriting correlated subqueries as JOINs when performance matters.
Keep subqueries readable — extremely deep nesting is a sign a CTE (covered next) may be a better fit.

Frequently Asked Questions

Can a subquery return multiple columns?

Yes, but it depends on where it is used. A subquery used with a single comparison (=, >, etc.) must return exactly one column and one row. A subquery in the FROM clause can return multiple columns, since it acts like a temporary table.

What is EXISTS and how does it relate to subqueries?

EXISTS is used with a correlated subquery to test whether it returns any rows at all, without caring about the actual values. It is often more efficient than IN for existence checks.

Are subqueries always slower than joins?

Not always — the SQL Server query optimizer often rewrites subqueries internally in ways that perform similarly to an equivalent join. However, correlated subqueries in particular can be less efficient and are worth reviewing with an execution plan.

Key Takeaways

A subquery is a SELECT statement nested inside another query.
Subqueries can appear in WHERE, SELECT, FROM, and other clauses.
Non-correlated subqueries run independently of the outer query.
Correlated subqueries reference the outer query and re-evaluate per row.
A subquery used with = must return a single value.

Summary

Subqueries let you nest logic inside a query to answer more complex questions. Next, you will learn about Common Table Expressions (CTEs), which offer a cleaner, more readable alternative to deeply nested subqueries.

Next Lesson →