Understanding SQL Subqueries: A Beginner-Friendly Guide

SQL is a powerful language for data analysis and management, and one of its most important features is the subquery. Subqueries help us break down complex questions into smaller, manageable parts, making our queries more flexible and effective.

In this article, we’ll explore what subqueries are, their types, where they are used, and how to use them efficiently.

What Is a Subquery?

A subquery is simply a query written inside another query.

It is commonly used to:

• Solve complex problems step by step

• Retrieve filtered or aggregated data

• Use temporary results inside a main query

• Work with SELECT, INSERT, UPDATE, and DELETE statements

• The result of a subquery is temporary and exists only during query execution. Subqueries inside another subquery are called nested subqueries.

Categories of Subqueries (Based on Dependency)

1️⃣ Non-Correlated Subquery

• Independent of the main query

• Executes only once

• Result is reused by the outer query

• Easier to write and faster in performance

2️⃣ Correlated Subquery

• Depends on the main query

• Executes once for each row in the outer query

• More complex

• May impact performance

Understanding this difference is important for writing optimized SQL queries.

Categories of Subqueries (Based on Result Type)

Subqueries can also be classified based on what they return:

🔹 Scalar Subquery

A scalar subquery is a subquery that returns exactly one value. This value can be used in places where a single value is expected, such as in the WHERE, SELECT, or HAVING clause.

Example:

The inner query calculates the average payment amount. It returns a single value. The outer query compares each payment with this value, and the final output is displayed. Since the subquery returns only one result, it is called a scalar subquery. Because it is not dependent on the outer query, it runs only once and is efficient.

🔹 Multi-Row Subquery

A multi-row subquery returns multiple rows/single column and is usually used with operators like IN, ANY, or ALL.

Example:

The subquery returns a list of customer_id values. These IDs belong to customers who made payments in February. The outer query selects customers whose IDs match this list and returns their names. Since the subquery returns multiple values, it is called a multi-row subquery.

🔹 Table Subquery (Derived Table)

A table subquery returns a complete result set (multiple rows and multiple columns) and is used in the FROM clause like a temporary table. It is also called a derived table.

Example:

The inner query returns multiple rows and multiple columns creating a temporary table with customer IDs and rental counts. This result is given an alias (total_rent). The outer query filters customers who rented more than 40 times. Since the subquery behaves like a table, it is called a table subquery.

Each type of subquery is used for different analytical needs.

Where Can Subqueries Be Used?

Subqueries can be used in different parts/clause of a SQL statement.

1️⃣ In SELECT Clause

A subquery can be used inside the SELECT clause to return a calculated value along with each row of the main query.

Syntax:

SELECT column1, column2,
    (SELECT column_name FROM table_name WHERE condition) AS alias_name
FROM main_table
WHERE condition;

Example: List customers and the total amount they have paid so far.

SELECT first_name,last_name,(
	SELECT SUM(amount) FROM payment 
	) AS tot_pymnt
FROM customer;

The subquery calculates the total amount from the payment table. It returns a single value. This value is displayed as tot_pymnt for every customer. Since the subquery returns only one value, it is a scalar subquery used in the SELECT clause.

Output:

2️⃣ In FROM Clause

A subquery can be used in the FROM clause to create a temporary result set that acts like a table. So, table subquery is used in the FROM clause.

Syntax:

SELECT column1, column2 FROM (
      SELECT column1,column2 FROM table_name WHERE condition
     ) AS alias;

Example: What is the average of total sales spent per day?

SELECT Round(AVG(total_amt),2) AS Average_perday_sales 
FROM (
		SELECT DATE(payment_date) AS pymnt_date, 
		SUM(amount) AS total_amt 
		FROM payment
		GROUP BY pymnt_date
	) AS total_sales ;

The inner query calculates total sales for each day. It groups payments by date and calculate the total amount. This result is saved as a temporary table named total_sales. The Outer query uses the values from subquery to find the average of total sales.

Output:

3️⃣ In WHERE Clause

A subquery is used in WHERE clause for filtering data. We can use subquery with both comparison operators like <, ≤, >, ≥, =, != and logical operators like IN, ANY, ALL, EXISTS.

Syntax:

SELECT column_name FROM table_name 
WHERE column_name IN (SELECT column_name FROM table_name2 WHERE condition);

Example1: List films with rental duration higher than the average rental duration of all films.

SELECT title, rental_duration 
FROM film 
WHERE rental_duration > AVG(rental_duration);

This query throws an error because aggregate functions like AVG(), SUM(), and COUNT() cannot be used directly in the WHERE clause. The WHERE clause filters rows before aggregation happens, so SQL does not allow aggregate functions there.

Instead, we can try the below approach.

Step 1: Find the Average Rental Duration

SELECT Round(AVG(rental_duration),2) AS avg_rent_duration FROM film;

Example output:

This query calculates the average rental duration as 4.99

Step 2: Use the Result in the Main Query

SELECT title, rental_duration FROM film WHERE rental_duration > 4.99;

This works, but it is static. If the data changes, the value must be updated manually, which is practically not reliable.

Best Approach: Using a Subquery

SELECT title,rental_duration
FROM film 
WHERE rental_duration > (
	SELECT AVG(rental_duration) as avg_rent_duration FROM film
	);

The subquery calculates the average rental duration. It returns a single value (scalar subquery). The outer query compares each film’s rental duration with this value. The result updates automatically when data changes.

Output:

Example using IN operator: List the names of customers who made a purchase in the month of May.

Syntax:

SELECT first_name || ' ' || last_name AS customer_name 
FROM customer 
WHERE customer_id IN (SELECT customer_id
 	    FROM payment WHERE EXTRACT(MONTH FROM payment_date) = 5);

The subquery finds all customer_id values from the payment table where the payment was made in May. This subquery returns multiple rows. The outer query selects customers whose IDs match this list. The IN operator is used to compare a value with multiple results.

First, it scans the payment table and builds a temporary list of customer IDs who paid in May. Internally, this result is stored in memory as a temporary set as below.

Next, the main query scans the customer table. For each row, it checks:

customer_id IN {155, 267, 267,269,269,274….}

Only customers whose IDs appear in the set are selected.

For the matching customers, first name and last name columns are combined and displayed under labeled column customer_name.

Output:

When a subquery returns multiple values, operators like IN, ANY, and ALL are commonly used to filter records based on those results.

4️⃣ In JOIN Clause

When a subquery is used inside a JOIN, it acts as a table subquery (derived table). The result of the subquery behaves like a temporary table that can be joined with other tables. This is useful when you need to join aggregated or summarized data.

Syntax:

SELECT t1.col1, t2.col2 
FROM table1 t1 JOIN (
      SELECT col1, col2
      FROM table2
      WHERE condition) t2 
ON t1.col1 = t2.col1;

Example: Show the details of all customers and the payments made by them

SELECT c1.customer_id,
       c1.first_name,
       c1.last_name,
       p1.total_payment
FROM customer c1 JOIN (
    SELECT customer_id,
    SUM(amount) AS total_payment
    FROM payment
    GROUP BY customer_id) p1 
ON c1.customer_id = p1.customer_id 
ORDER BY c1.customer_id;

The subquery calculates the total payment made by each customer. It groups payments by customer_id and calculates the total payment amount for each customer. This result acts as a temporary table (p1). The outer query joins this result with the customer table. This displays each customer along with their total payment.

Output:

Using subqueries in JOIN clauses helps combine detailed data with summarized data in a clean and organized way.

Order of Execution

SQL does not always execute queries from top to bottom. The optimizer decides the execution order.

Generally:

• The subquery (innermost query) executes first, followed by the outer query

• For correlated subqueries, the outer query executes first. The inner query runs once for each row processed by the outer query.

Understanding this helps in debugging and optimization.

Rules for Efficient Subqueries

To write better subqueries, follow these best practices:

✔A subquery must always be enclosed in parentheses

✔Alias must be given, when the subquery is used in FROM clause and JOIN clause.

✔Use single-row comparison operators (=,>,>=, <,<=, <>, !=) with only Scalar subqueries.

✔Use multiple rows IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS) with multi row subqueries

✔Cannot be used in ORDER BY clause.

✔BETWEEN operator cannot be used with subquery.

Following these rules improves performance and readability.

Why Subqueries Matter for Data Analysts

For data analysts and SQL developers, subqueries are essential because they:

✅ Enable advanced filtering

✅ Support layered analysis

✅ Simplify complex logic

✅ Improve reporting accuracy

✅ Help build reusable query structures

Mastering subqueries takes your SQL skills to the next level.

Final Thoughts

Subqueries are one of the most powerful tools in SQL. Whether you’re filtering records, updating data, or performing deep analysis, subqueries allow you to think in layers and solve problems systematically.

By understanding their types, usage, and optimization rules, you can write cleaner, faster, and more professional SQL queries.

Happy Querying!