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
• 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:

SELECT *
FROM payment
WHERE amount > (
    SELECT ROUND(AVG(amount), 2)
    FROM payment
);

The inner query calculates the average payment amount. It returns a single value as below.

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 and is usually used with operators like IN, ANY, or ALL.

Example:

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) = 2
);

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. Since the subquery returns multiple values, it is called a multi-row subquery.

Output:

🔹 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:

SELECT *
FROM (
    SELECT customer_id, COUNT(rental_id) AS rental_count
    FROM rental
    GROUP BY customer_id
) AS total_rent
WHERE rental_count > 40;

Output:

The inner query creates 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 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,
       (SELECT column2 FROM table_name WHERE condition) AS alias
FROM table_name;

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.

Output:

Since the subquery returns only one value, it is a scalar subquery used in the SELECT clause.

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)
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 sums the amounts. This result is treated as a temporary table named total_sales.

Get Radhika Thimmaiyan’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Output:

The outer query calculates the average of these daily totals.

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.

Best Approach: Using a Subquery

SELECT title, rental_duration
FROM film
WHERE rental_duration > (
    SELECT AVG(rental_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.

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.

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 sums the amounts. 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.

In this blog, we learned about non-correlated subqueries. To understand correlated subqueries in more detail, check out my separate blog through the link below

Correlated Subqueries Simplified. A correlated subquery is a subquery… | by Radhika Thimmaiyan | Jan, 2026 | Medium

Happy querying!