Subqueries are one of the most powerful features in SQL. They allow you to break complex problems into smaller, manageable parts by nesting one query inside another.

For data analysts, mastering subqueries is essential because they help solve real-world business problems such as filtering, ranking, and aggregating data.

In this guide, you’ll learn how to write subqueries in SQL step by step with clear examples.

What Is a Subquery?

A subquery is a query inside another SQL query.

It is usually placed inside:

• WHERE clause
• SELECT clause
• FROM clause

The inner query runs first, and its result is used by the outer query.

Step 1: Start With a Simple Query

Before writing a subquery, always begin with a basic query.

Example:

SELECT customer_id, revenue
FROM orders;

This helps you understand the dataset before adding complexity.

Step 2: Identify the Problem

Subqueries are useful when you need to answer questions like:

• Which customers spent more than average?
• Which products have the highest sales?
• Which orders exceed a certain threshold?

Example problem:

Find customers who spent more than the average revenue.

Step 3: Write the Inner Query First

The inner query (subquery) calculates the value you need.

SELECT AVG(revenue)
FROM orders;

This returns the average revenue.

Step 4: Use the Subquery in the Main Query

Now, place the subquery inside the main query.

SELECT customer_id, revenue
FROM orders
WHERE revenue > (
    SELECT AVG(revenue)
    FROM orders
);

How it works:

• The subquery calculates the average revenue
• The outer query filters customers above that value

Step 5: Use Subqueries With IN

Subqueries can return multiple values.

Example:

Find customers who placed orders.

SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
);

Why this works:

The subquery returns a list of customer IDs, and the main query filters based on that list.

Step 6: Use Subqueries With EXISTS

EXISTS checks if a subquery returns any result.

SELECT customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Use case:

Efficient for checking relationships between tables.

Step 7: Use Subqueries in SELECT

You can also use subqueries to create calculated columns.

SELECT customer_id,
       (SELECT AVG(revenue) FROM orders) AS avg_revenue
FROM orders;

Insight:

Adds additional context to your results.

Step 8: Use Subqueries in FROM (Derived Tables)

Subqueries can act as temporary tables.

SELECT region, AVG(total_revenue)
FROM 
    SELECT region, SUM(revenue) AS total_revenue
    FROM sales
    GROUP BY region
) t
GROUP BY region;

Benefit:

Helps organize complex queries.

Step 9: Know When to Use Subqueries vs JOINs

Subqueries are great for:

• Simpler logic
• Step-by-step filtering
• Aggregations inside conditions

However, joins are often more efficient for large datasets.

Use subqueries when they make your query easier to understand.

Common Mistakes to Avoid

When writing subqueries, watch out for:

• Returning multiple values when only one is expected
• Poor performance with large datasets
• Forgetting to test the inner query separately

Always test your subquery first before combining it.

Subqueries are a powerful tool for solving complex SQL problems.

By breaking queries into smaller parts, you can write cleaner, more logical SQL statements.

As a data analyst, mastering subqueries will help you answer deeper business questions and improve your problem-solving skills.

FAQs