What Is Subquery in SQL (with Examples)?

In today’s blogs post I will show you what is subquery in SQL (with examples). A subquery, also known as a nested query, is a query within another SQL query. Subqueries allow you to perform more complex filtering, calculations, and lookups by embedding one query inside another.

In this blog post, I’ll explain what subqueries are, where and how to use them, and walk you through several practical examples.

What Is a Subquery?

A subquery is a SQL query that is placed inside another query – usually within a SELECT, WHERE, or FROM clause. It allows you to run an inner query first, and then use its result in the outer query.

Basic Subquery Syntax:

SELECT column1
FROM table1
WHERE column2 = (SELECT column2 FROM table2 WHERE condition)

Types of Subqueries

Subqueries can be used in different parts of an SQL statement:

1. In the WHERE clause
2. In the FROM clause
3. In the SELECT clause

Let’s break them down with examples.

Example 1: Subquery in the WHERE Clause

In my previous post, we created a new table called EmployeeBonus. Imagine you want to find employees who get more than average bonus.

SELECT EmployeeID, Amount
FROM EmployeeBonus
WHERE Amount > (SELECT AVG(Amount) FROM EmployeeBonus)

In this example the inner query calculates the average salary, while the outer query finds all employees with a salary higher than that. Here is what this looks like in SQL Server Management Studio:

Example 2: Subquery in the FROM Clause

First, let’s add some bonus amounts for 2023 and 2024. Use the following query:

INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (1, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (2, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (3, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (4, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (5, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (6, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (7, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (8, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (9, 2023, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (1, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (2, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (3, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (4, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (5, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (6, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (7, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (8, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));
INSERT INTO EmployeeBonus (EmployeeID, Year, Amount) VALUES (9, 2024, ROUND((RAND(CHECKSUM(NEWID())) * 10000) + 10000, 2));

Let’s say you want to calculate the average bonus by year, and then filter years with an average bonus above 15,000:

SELECT Year, avg_bonus
FROM (
  SELECT Year, AVG(Amount) AS avg_bonus
  FROM EmployeeBonus
  GROUP BY Year
) AS dept_avg
WHERE avg_bonus > 15000

Here, the subquery (aliased as dept_avg) calculates average bonuses, and the outer query filters the results.

Here is what it looks like in SSMS:

Example 3: Subquery in the SELECT Clause

You can also use a subquery in the SELECT clause to show related information:

SELECT emp.FirstName, emp.LastName, 
	(SELECT Sum(Amount) FROM EmployeeBonus empbon WHERE empbon.EmployeeID = emp.EmployeeID) AS total_orders
FROM Employees emp

In this case, we will list all employees along with the totals of their bonuses for all years. Here is how it looks in SSMS:

When to Use Subqueries?

• When you need to compare a value to a calculated result (like an average or max).
• When breaking down a complex query into simpler parts.
• When you need a temporary result to use in another query.

Subquery Tips & Best Practices

• Always alias subqueries in the FROM clause.
• Avoid deeply nested subqueries when JOINs can do the job better – they’re often more performant.
• Use EXISTS or IN for subqueries that return multiple rows.

Summary

So, what is subquery in SQL (with examples)? It’s a flexible, powerful way to nest one query inside another. Whether you’re filtering data, calculating aggregates, or building dynamic reports, subqueries help you write more elegant and efficient SQL code.

Next time you’re stuck trying to figure out how to write a complex query, make sure you try using a subquery. It might just make your life easier.

Make sure you check my blog post from last week on What Is FULL OUTER JOIN in SQL?

If you would like to learn more about SQL, make sure you read this book: T-SQL Fundamentals (Developer Reference)