Using Subqueries in SQL – A Guide to Nested Queries

Learn how to leverage subqueries to perform complex data retrieval in SQL by embedding one query within another.

Nested Query Structure

How it works:

Inner query executes first, calculating the average orders for the North region
Result is used as a comparison value in the outer query’s WHERE clause
Outer query then returns customers whose orders exceed this average

Introduction

As you advance your SQL skills, you’ll discover that a single query isn’t always enough to extract the data you need. Subqueries—or nested queries—allow you to embed one SQL query within another, enabling more complex data operations.

In this guide, you will learn:

What subqueries are: Their role and advantages in SQL.

How to construct subqueries: Basic syntax and examples.

Types of subqueries: Including scalar, multi-row, and correlated subqueries.

Best practices: Tips to ensure efficient and accurate nested queries.

For foundational concepts, be sure to review our Basic SQL SELECT Statement and Filtering Data in SQL – Mastering the WHERE Clause articles. And if you haven’t yet, check out our comprehensive SQL course for hands-on training.

Get Certified in SQL

What Are Subqueries?

A subquery is a query nested inside another SQL query. It can be used in various clauses such as SELECT, FROM, or WHERE to provide a value or set of values that the outer query can use for further filtering or processing.

Benefits of Using Subqueries

Simplify complex queries: Break down complicated logic into manageable parts.

Enhance flexibility: Allow you to perform operations that would otherwise require multiple queries.

Improve readability: Encapsulate logic in a way that makes the main query easier to understand.

Basic Syntax of a Subquery

A subquery is usually enclosed in parentheses and can be placed in several parts of an SQL statement. Here’s a basic example where a subquery is used in the WHERE clause:

SELECT first_name, last_name FROM employees WHERE department_id = ( SELECT department_id FROM departments WHERE department_name = ‘Sales’ );

Explanation:

The inner query retrieves the department_id for the department named ‘Sales’.

The outer query uses that result to filter employees belonging to the Sales department.

Types of Subqueries

1. Scalar Subqueries

Scalar subqueries return a single value and can be used anywhere a single value is expected.

Example:

SELECT first_name, last_name, (SELECT AVG(salary) FROM employees) AS average_salary FROM employees;

Explanation:

This query displays each employee’s name along with the overall average salary calculated by the scalar subquery.

2. Multi-Row Subqueries

Multi-row subqueries return multiple values and are typically used with operators like IN, ANY, or ALL.

Example:

SELECT first_name, last_name FROM employees WHERE department_id IN ( SELECT department_id FROM departments WHERE location = ‘New York’ );

Explanation:

This query lists employees whose departments are located in New York, using the multi-row subquery to supply multiple department_id values.

3. Correlated Subqueries

Correlated subqueries reference columns from the outer query. They are evaluated once per row of the outer query.

Example:

SELECT e.first_name, e.last_name, e.salary FROM employees AS e WHERE e.salary > ( SELECT AVG(salary) FROM employees AS sub WHERE sub.department_id = e.department_id );

Explanation:

This query returns employees whose salary is above the average salary for their respective department. Notice how the subquery references the outer query’s department_id column.

Best Practices for Using Subqueries

Keep Subqueries Simple: Break down complex operations into multiple, simpler subqueries if necessary.

Test Independently: Run the inner subquery separately to ensure it returns the expected result before integrating it.

Consider Performance: In some cases, joining tables might be more efficient than using subqueries. Analyze performance when dealing with large datasets.

Use Correlated Subqueries Judiciously: Since correlated subqueries run for every row of the outer query, they can be slower. Optimize where possible.

Conclusion

Subqueries are a powerful tool in SQL that allow you to nest one query within another, thereby enabling more complex data retrieval strategies. By understanding and applying the different types of subqueries, you can write queries that are both powerful and flexible.

Stay tuned for our next article, “Demystifying SQL Functions – Aggregate, Scalar, and Window Functions“, where we expand your SQL toolkit even further.

Have questions or experiences with subqueries? Leave a comment below or join our community to share your insights and learn more!