SQL Subquery: A Complete Guide

As data volumes and complexity grow, writing efficient SQL queries is more important than ever. Subqueries, known as nested queries, allow you to break down complex problems into smaller, more manageable parts by embedding one query inside another. They’re essential for tasks like filtering data, performing calculations, and joining tables—making them a powerful tool for writing scalable SQL queries.

SQL subqueries come in different types, each serving a unique purpose:

• Scalar subqueries return a single value.

• Multiple-row subqueries return a set of rows.

• Correlated subqueries compare values between two tables.

In this blog, we’ll focus on SQL subqueries in Microsoft SQL Server, covering their syntax, types, practical applications, and optimization strategies to help you write better-performing queries.

Subquery syntax#

The following snippet depicts a general syntax for subqueries:

In this syntax:

• main_table is the main table from which we are selecting data.

• column1 and column2 are the columns we want to retrieve from the main table.

• columnN is the name in the main table that we want to compare with the subquery result.

• operator is a comparison operator such as =, >, <, IN, EXISTS, etc.

• subquery_table is the table in the subquery that is used to retrieve data.

• expression is the value or set of values we compare against in the outer query.

• condition is the condition used to filter data in the subquery.

Rules/guidelines for subqueries#

Here are a few rules/guidelines to remember when dealing with subqueries:

• A subquery must be enclosed in parentheses.

• A subquery must be placed on the right side of the comparison operator.

• Subqueries cannot manipulate their results internally. Therefore, the ORDER BY clause cannot be added to a subquery.

• Subqueries can be used with comparison operators such as >, <, >=, <=, =, or <>.

• Subqueries can be used with the IN or NOT IN operator.

• Subqueries can be used with the EXISTS or NOT EXISTS operator.

• Use table aliases or fully qualified column names to prevent ambiguity, especially when dealing with subqueries within larger queries.

Types of subqueries#

There are five major types of subqueries mentioned below. Let’s go through them individually, along with some code examples.

1. Single-row subquery#

A single-row subquery returns zero or one row in the result set. It is employed when we want to compare a single value from a subquery with the result of the outer query.

This subquery from lines 3–5 above calculates the average age of computer science students, and then the outer query retrieves names of only those students whose age is greater than that average.

2. Multiple-row subquery#

A multiple-row subquery returns one or more rows in the result set. It is useful when comparing or evaluating multiple values from the subquery with the outer query.

The subquery from lines 3–5 above finds the category IDs for discounted products, and the outer query retrieves product names in those categories.

3. Multiple-column subquery#

A multiple-column subquery returns one or more columns. It is applied when the result set requires different variables or multiple information from the subquery.

The subquery from lines 4–7 above finds the department-wise maximum salary, and the outer query gets the employee names and their respective departments for those maximum salaries.

4. Correlated subqueries#

Correlated subqueries refer to columns from the outer query and return one or more columns according to the main or the outer query.

The above subquery from lines 4–6 correlates to the outer query by referencing the client ID. It checks if the deadline of the current project is earlier than or equal to all other projects from the same client. The outer query then retrieves project names and deadlines that satisfy this condition.

5. Nested subqueries#

Nested subqueries involve queries within a query (inner and outer query). The inner query is executed first, and its result is used by the outer query, allowing for more complex and layered operations.

The innermost subquery from lines 6–8 retrieves location IDs for the US country ID. The middle subquery from lines 4–6 then finds department IDs associated with those locations. The outer query from lines 1–4 retrieves employee names and their respective departments for those in the US locations.

Overview of subquery types and their key characteristics#

Here’s a comparative table highlighting the types of subqueries and their key characteristics.

Subqueries with DML operations#

DML stands for data manipulation language. It essentially refers to the subset of SQL that manipulates data stored in a database, such as inserting, updating, and deleting records. Let’s take a look at subqueries in the context of DML operations.

1. Subqueries with the INSERT statements#

Subqueries can be used with INSERT statements to add data based on the results of another query. For instance, to insert students into a new class, we do the following:

2. Subqueries with the UPDATE statements#

We can even modify records based on the results of a nested query using UPDATE statements. To update grades for students who are in the Computer Science department, we do the following:

3. Subqueries with the DELETE statements#

Subqueries can also be applied with DELETE statements to remove records based on the results of a nested query. To delete students who have not attended any class, we do the following:

Applications of subqueries#

Let’s take a look at a few use cases of subqueries below.

• Filtering data: We can use a subquery to filter data based on a condition. For example, we can use a subquery to find all customers who have placed an order in the last 30 days:

• Performing calculations: We can also use a subquery to perform calculations on a dataset. For example, using a subquery to find the average price of all products in a category:

• Joining tables: Another use case is to utilize a subquery to join two tables together. For example, we can use a subquery to find all customers who have placed their most recent orders after a specific date:

Tips for optimizing subqueries#

Subqueries are a powerful feature in SQL, but they can sometimes slow down your queries, especially when working with large tables or complex conditions. To write faster and more efficient queries, let's understand how subqueries work and when alternatives like JOINs or common table expressions (CTEs) might be better.

Key performance considerations#

1. Subquery execution: Subqueries, especially correlated ones, might run multiple times—once for each row in the outer query. This repetition can make your queries slower if there’s a lot of data. To overcome this:

• Optimize correlated subqueries by ensuring the filters and conditions are as specific as possible.

• Replace correlated subqueries with JOINs or CTEs (common table expressions) whenever feasible, as these approaches often execute faster.

• Use query execution plansUse the EXPLAIN operator before your query to view the query plan, which shows the execution order and estimated times for each step. to identify bottlenecks and ensure that indexes are utilized effectively.

2. Using indexes: Subqueries work best when the columns they use are indexed. Without indexes, the database has to search through all the rows, which takes more time.

It’s important to note that while MySQL can leverage indexes effectively in subqueries, correlated subqueries may still perform poorly because MySQL often lacks sophisticated optimization for them, unlike some other DBMSs.

When to use alternatives#

Sometimes, JOINs or CTEs can replace subqueries and make your query faster or easier to read:

1. JOINs: JOINs combine data from multiple tables in a single step, often faster than running a subquery for every row. Use JOINs when you need to compare or combine data across tables.

2. CTEs (common table expressions): CTEs let you create a temporary table inside your query. This helps break down complex queries into smaller, easier-to-understand steps or for reusing a result in different parts of the same query.

General optimization tips#

• Keep subqueries small: Filter the data in your subquery to reduce the number of rows it processes.

• Avoid repeating work: Use JOINs or CTEs instead of correlated subqueries, which are slower because they run repeatedly.

• Check your query plan: Tools like EXPLAINIn SQL, EXPLAIN is a command that provides insights into the query execution plan, showing how the database processes a query and helps identify performance issues. can show how your query runs and help you spot slow parts.

• Use your database’s features: Some databases have special ways to handle subqueries more efficiently, so check their documentation. For example:

  • PostgreSQL:
    PostgreSQL’s query planner effectively optimizes subqueries, especially correlated ones. When using subqueries in PostgreSQL,  its advanced optimization techniques and detailed EXPLAIN ANALYZE to identify how queries are being executed.

  • MySQL:
    While MySQL can handle subqueries well, it benefits most from indexed subqueries. In particular, subqueries using IN or EXISTS are more efficient when indexes are applied to the columns. In cases of correlated subqueries, MySQL generally performs better with JOINs.

  • SQL Server:
    SQL Server provides a sophisticated query optimization engine that works efficiently with subqueries and JOINs. When replacing subqueries with JOINs or CTEs, SQL Server often executes them faster due to its powerful optimization features.

That’s all there is to know about subqueries to effectively get the hang of them. By intelligently choosing between subqueries, JOINs, and CTEs, you can make your queries efficient and easy to understand. They are a crucial topic in database systems; you will see them in coding interviews. Therefore, mastering subqueries is a game-changer for writing efficient SQL. Keep experimenting and optimizing!

Want to build a strong foundation in SQL and relational databases? Start this hands-on course today to master table management, complex subqueries, and joins using MySQL.