Understanding SQL Subquery with Examples

Summary: SQL subqueries simplify complex queries by allowing a query inside another. Learn single-row, multi-row, correlated, and nested subqueries with examples. Optimize performance by using indexed columns and choosing JOINs when applicable. Master SQL subqueries and advance your data science skills with Pickl.AI’s expert-led courses.

Introduction

If you’ve ever used SQL to fetch data, you know how powerful queries can be. But what if you need to run a query inside another query? That’s where SQL subqueries come in! Think of them as little detectives—searching for answers before handing them over to the main query.

In this blog, you’ll learn what an SQL subquery is, how it works, and when to use it. We’ll also break down tricky concepts like correlated subquery SQL with easy-to-follow SQL subquery examples. By the end, you’ll confidently use subqueries like a pro (without breaking a sweat)! 

Key Takeaways

• SQL subqueries allow querying within another query to filter, calculate, or retrieve data dynamically.
• Correlated subqueries depend on the main query for execution and run row-by-row, affecting performance.
• Types of subqueries include single-row, multi-row, nested, and correlated subqueries for varied use cases.
• Use JOINs for faster data retrieval, but subqueries work best for complex filtering.
• Optimising SQL subqueries with indexes and efficient query structuring improves performance in large datasets.

What is SQL Subquery?

SQL subquery is like a “query within a query.” Imagine you are looking for the best pizza place in town, but instead of checking all the restaurants, you first find the top-rated ones and then pick the best among them. That’s exactly how a subquery works in SQL—it helps filter or calculate data before the main query runs.

How Does a Subquery Work?

Think of SQL as a detective solving a case. The main query is the detective, and the subquery is their assistant, who gathers background information first. The assistant (subquery) finds key details, and the detective (main query) uses them to solve the case.

For example, suppose you want to find employees who earn more than the company’s average salary. In that case, a subquery first calculates the average salary, and then the main query selects employees earning above that amount.

Basic Syntax of a Subquery

A subquery always sits inside parentheses () and runs before the main query. Here’s how it looks:

This finds employees who earn more than the average salary. A correlated subquery SQL works differently—it depends on each row of the main query while running. We’ll explore that soon!

Types of SQL Subqueries

SQL subqueries come in different types, each serving a unique purpose. A subquery is a query inside another query, helping to fetch specific data. Understanding these types will help you write better and more efficient SQL queries.

Single-Row Subqueries 

A single-row subquery returns only one value. This means the result is a single number, text, or date. It is often used with comparison operators like =, >, <, >=, and <=.

Example: Suppose you want to find the employee with the highest salary. You can first get the highest salary using a subquery and then use it in the main query:

Multi-Row Subqueries 

A multi-row subquery returns more than one value. It is used when you need a list of results instead of just one. Operators like IN, ANY, and ALL are used in these subqueries.

Example: Find employees who work in the same department as “John”:

Correlated Subqueries

A correlated subquery depends on the main query for its execution. It runs once for each row processed in the main query, making it slower than other types.

Example: Find employees earning more than the average salary of their department:

Nested Subqueries (Multiple Levels of Subqueries)

A nested subquery contains another subquery inside it. SQL processes these from the innermost query to the outermost.

Example: Find the names of employees working in the department with the highest number of employees:

Subquery Placement in SQL Queries

A subquery is a smaller query inside a bigger query. It helps fetch data in a structured way by breaking a complex question into smaller parts. Subqueries can be placed in different parts of an SQL statement to achieve various tasks. Below are the key places where you can use subqueries in SQL.

In SELECT Clause (Derived Values)

Sometimes, you need to calculate a value before displaying it in the result. A subquery in the SELECT clause helps generate these values dynamically.

For example, imagine you have a list of employees and want to show each person’s salary along with the average salary of all employees. You can use a subquery to get the average salary and display it alongside individual salaries.

Example:

In WHERE Clause (Filtering Data)

A subquery in the WHERE clause helps filter data based on a condition. It is useful when you need to compare values with another dataset.

For example, if you want to find employees who earn more than the average salary, you can use a subquery in the WHERE clause.

Example:

In FROM Clause (Using Subqueries as Virtual Tables)

Sometimes, you may need to use the result of a subquery as a temporary table. This is useful when breaking down complex queries into smaller, readable parts.

For example, let’s say you want to see the top 3 highest-paid employees. You can first create a temporary table using a subquery, then select from it.

Example:

In HAVING Clause (Filtering Aggregated Data)

A subquery in the HAVING clause is useful when filtering grouped data. It is mainly used with aggregate functions like SUM, COUNT, AVG, etc.

For example, suppose you want to list departments with higher total salary expenses than the average department salary expense.

Example:

SQL Subquery Examples

Subqueries help us find specific information from a database by using one query inside another. Let’s look at some real-world examples to understand how subqueries work.

Example 1: Finding the Highest Salary in a Company

Imagine a company that wants to find the employee with the highest salary. Instead of manually checking each salary, we can use a subquery to find the maximum salary and then retrieve the employee’s details.

Example 2: Retrieving Customers Who Placed the Largest Orders

A business wants to find customers who place the largest order. We can use a subquery to get the highest order amount and then find the customers who spent that much.

Example 3: Using Subqueries in UPDATE and DELETE Statements

Subqueries also help update or delete records efficiently. Suppose we need to increase the salary of employees who earn less than the average salary:

Similarly, if we want to delete inactive customers who never placed an order:

Performance Considerations

Using subqueries in SQL can make queries more powerful, but they can also slow down performance if not used correctly. Understanding when to use subqueries, how to optimise them, and common mistakes to avoid will help write efficient SQL queries.

When to Use Subqueries vs. JOINs

Subqueries work well when you need to break a problem into smaller parts. They are useful for dynamically filtering data, performing calculations, or retrieving values. However, subqueries can sometimes make queries run slower because the database processes them separately for each row.

JOINs are often a better choice when combining data from multiple tables. Unlike subqueries, JOINs usually perform better because they process all related data simultaneously. If the same result can be achieved using a JOIN instead of a subquery, it’s usually the faster option.

Optimising Subquery Execution

To speed up subqueries, use indexed columns whenever possible. Indexing helps the database find data faster. Also, avoid unnecessary subqueries—sometimes, rewriting a query using a JOIN or a common table expression (CTE) can improve performance.

Common Mistakes to Avoid

One common mistake is using subqueries inside loops, which can slow down execution. Another is selecting too many columns when only one is needed. Also, avoid correlated subqueries when a simple query can do the job more efficiently.

Closing Statements

SQL subqueries are powerful tools for simplifying complex queries and enhancing data retrieval. You can write more efficient SQL queries by mastering single-row, multi-row, correlated, and nested subqueries. 

Understanding when to use subqueries versus JOINs ensures better performance. Whether filtering data or performing calculations, subqueries streamline SQL operations. You can strengthen your SQL skills and advance in data science by enrolling in Pickl.AI’s data science courses. 

These courses cover SQL, machine learning, and other essential techniques for aspiring data analysts and scientists. Start learning today to gain hands-on expertise and excel in your data-driven career!

Frequently Asked Questions

What is a Correlated Subquery in SQL?

A correlated subquery is an SQL query that references columns from the main query and executes once for each row processed. Unlike standard subqueries, it depends on the outer query for execution, making it less efficient. It’s commonly used for comparisons within grouped datasets.

How Does an SQL Subquery Improve Query Efficiency?

An SQL subquery improves efficiency by breaking down complex queries into smaller, manageable parts. It helps dynamically filter, calculate, and retrieve data before executing the main query. However, excessive use of subqueries may slow performance, so optimising with indexes and using JOINs when applicable is recommended.

What Are The Key Differences Between Subqueries and JOINs in SQL?

Subqueries filter or retrieve values dynamically within a single query, whereas JOINs combine data from multiple tables in a structured way. JOINs often perform better for retrieving related data in bulk, while subqueries are useful for conditional filtering or calculating values before query execution.