Understanding Subqueries and CTE's

Introduction

Imagine you are a data analyst working for a retail company. Your manager asks you to identify customers who spend more than the average customer, employees earning above their department's average salary, or products generating the highest revenue.

You already know how to retrieve data using SELECT, filter records using WHERE, and combine tables using JOIN. However, some business questions require you to perform multiple calculations before arriving at the final answer.

For example, how would you identify employees earning above the company's average salary without knowing the average salary beforehand?

You would first need to calculate the average salary and then use that result to identify employees whose salaries exceed it.

This is where SQL Subqueries and Common Table Expressions (CTEs) come in.

Both techniques allow you to break down complex analytical problems into smaller, more manageable queries. They help you perform intermediate calculations, filter records based on the results of other queries, and organize your SQL code more effectively.

In this article, we will explore what subqueries and CTEs are, how they work, their key differences, and how to apply them to real-world data analysis problems.

SQL Subqueries

A subquery, also known as a nested query or inner query, is a SQL query written inside another SQL query.

The inner query produces a result that the outer query uses to perform another operation.

In simple terms, a subquery allows you to answer one question and use that answer to solve another question within the same SQL statement.

Basic Syntax of a Subquery

SELECT column_name
FROM table_name
WHERE column_name operator (
SELECT column_name
FROM table_name
);

The query inside the parentheses is called the inner query or subquery, while the query surrounding it is called the outer query.

For a simple, independent subquery, SQL can evaluate the inner query and use its result in the outer query. However, the actual execution strategy depends on the database engine and the type of subquery.

Let's understand this concept using a practical example.

Example 1: Finding Employees Earning Above the Average Salary

Suppose we have an employees table containing the following information:

employee_id	employee_name	department	salary
1	John	IT	80000
2	Mary	HR	55000
3	Peter	IT	70000
4	Jane	Finance	45000
5	David	Finance	50000

The HR manager wants to identify employees earning above the company's average salary.

To solve this problem, we need to calculate the average salary and then compare each employee's salary against that average.

Step 1: Calculate the average salary.

SELECT AVG(salary) AS average_salary
FROM employees;

The AVG() function calculates the average salary across all employees.

The result is:

average_salary: 60000

Step 2: Use the average salary to identify employees earning above it.

Instead of manually entering 60000 into another query, we can combine both operations using a subquery.

SELECT
employee_name,
department,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

How does this query work?

The inner query:

SELECT AVG(salary)
FROM employees;

calculates the company's average salary.

The outer query:

SELECT
employee_name,
department,
salary
FROM employees
WHERE salary > (...);

retrieves employees whose salaries exceed the value returned by the inner query.

The final result is:

employee_name	department	salary
John	IT	80000
Peter	IT	70000

Notice that we did not need to enter the average salary manually. The query calculates it directly from the data.

If the employee salaries change, running the query again will use the updated average salary.

This makes subqueries particularly useful when working with dynamic data.

Types of SQL Subqueries

Subqueries can be classified according to the number of values they return and how they interact with the outer query.

1. Scalar Subqueries

A scalar subquery returns a single value, such as an average salary, maximum price, or total revenue.

For example:

SELECT
employee_name,
salary
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
);

Here, the subquery returns one value: the average salary.

The outer query uses that value to filter employees.

2. Multiple-Row Subqueries

A multiple-row subquery returns more than one row.

These subqueries are commonly used with operators such as IN, ANY, and ALL.

For example, suppose we have a departments table containing department names and locations.

We want to identify employees working in departments located in Nairobi.

SELECT
employee_name,
department
FROM employees
WHERE department IN (
SELECT department_name
FROM departments
WHERE location = 'Nairobi'
);

The inner query retrieves the names of departments located in Nairobi.

The outer query then selects employees whose department appears in that list.

3. Correlated Subqueries

A correlated subquery is a subquery that references a column from the outer query.

Unlike an independent subquery, it depends on values supplied by the outer query.

For example, suppose we want to identify employees earning above the average salary in their respective departments.

SELECT
e.employee_name,
e.department,
e.salary
FROM employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department = e.department
);

In this example, the subquery calculates the average salary for the department associated with each employee in the outer query.

The condition:

WHERE e2.department = e.department****

connects the inner query to the outer query.

This allows each employee's salary to be compared against the average salary of their own department rather than the company-wide average.

Correlated subqueries are useful when performing comparisons within groups, such as comparing individual customer spending against the average spending of customers in the same region.

3. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary named result set defined within a SQL statement using the WITH keyword.

A CTE allows you to write a query, give its result a meaningful name, and reference that result in the main SQL statement.

Think of a CTE as creating a temporary working table that helps you organize a complex query into smaller, logical steps.

Unlike a permanent database table, a CTE does not remain available for use by later, separate SQL statements.

Basic Syntax of a CTE

The basic structure of a Common Table Expression looks like this:

WITH cte_name AS (
    SELECT column_name
    FROM table_name
    WHERE condition
)
SELECT *
FROM cte_name;

Let's break this down.

The WITH keyword tells SQL that we are defining a Common Table Expression.

cte_name is the name we give to the result produced by the query inside the parentheses.

The main query can then reference the CTE using that name.

In simple terms:

WITH
    ↓
Create temporary result
    ↓
Give it a name
    ↓
Use that name in the main query

Let's apply this to our employee data.

Example 2: Finding Employees Earning Above the Average Salary Using a CTE

Earlier, we solved this problem using a subquery:

SELECT
    employee_name,
    department,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

We can solve the same problem using a CTE.

First, we create a CTE that calculates the company's average salary.

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT
    e.employee_name,
    e.department,
    e.salary
FROM employees AS e
CROSS JOIN average_salary AS a
WHERE e.salary > a.avg_salary;

Let's understand what is happening.

The CTE:

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)

calculates the average salary and gives the result the name average_salary.

Conceptually, it produces:

avg_salary
60000

The main query then uses that result:

SELECT
    e.employee_name,
    e.department,
    e.salary
FROM employees AS e
CROSS JOIN average_salary AS a
WHERE e.salary > a.avg_salary;

The final result is:

employee_name	department	salary
John	IT	80000
Peter	IT	70000

We have therefore answered the same question using two different approaches.

The subquery places the average calculation directly inside the WHERE condition, while the CTE calculates the average first, gives the result a meaningful name, and then references that result in the main query.

For a simple problem like this, the subquery may be shorter. However, as queries become more complex, CTEs can make SQL code easier to read and maintain.

---

Using a CTE to Summarize Data

CTEs become particularly useful when we need to perform an intermediate calculation before carrying out further analysis.

Suppose we have a sales table containing:

sale_id	customer_id	amount
1	101	15000
2	102	25000
3	101	30000
4	103	10000
5	102	20000
6	104	5000

Management wants to identify customers who have spent more than KSh 30,000 in total.

Before filtering the customers, we first need to calculate the total amount spent by each customer.

We can use a CTE:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
)
SELECT
    customer_id,
    total_spent
FROM customer_spending
WHERE total_spent > 30000;

The CTE produces an intermediate result similar to:

customer_id	total_spent
101	45000
102	45000
103	10000
104	5000

The main query then filters this result:

SELECT
    customer_id,
    total_spent
FROM customer_spending
WHERE total_spent > 30000;

giving us:

customer_id	total_spent
101	45000
102	45000

This demonstrates an important advantage of CTEs: we can perform one analytical step, give the result a meaningful name, and then continue our analysis using that result.

---

Using Multiple CTEs

We are not limited to creating only one CTE.

SQL allows us to define multiple CTEs within the same statement by separating them with commas.

The general structure is:

WITH cte_one AS (
    SELECT ...
),
cte_two AS (
    SELECT ...
)
SELECT ...
FROM cte_one
JOIN cte_two
    ON ...;

This can be particularly useful when solving a problem that contains several analytical steps.

Suppose we want to:

1. Calculate the total amount spent by each customer.
2. Calculate the average customer spending.
3. Identify customers who spent more than the average customer.

We can break the problem into logical steps.

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
),
average_spending AS (
    SELECT
        AVG(total_spent) AS avg_spent
    FROM customer_spending
)
SELECT
    cs.customer_id,
    cs.total_spent
FROM customer_spending AS cs
CROSS JOIN average_spending AS a
WHERE cs.total_spent > a.avg_spent;

Notice how the second CTE can reference the result produced by the first CTE.

Conceptually, our query follows this process:

Raw Sales Data
      ↓
Calculate spending per customer
      ↓
Calculate average customer spending
      ↓
Compare each customer with the average
      ↓
Return above-average customers

Instead of trying to solve the entire problem in one deeply nested query, CTEs allow us to express the analysis as a sequence of understandable steps.

This is one of the main reasons CTEs are popular when writing analytical SQL.

---

Subqueries vs CTEs

At this point, you may be wondering:

If both subqueries and CTEs can produce intermediate results, which one should I use?

The answer depends on the problem you are solving.

Consider our original employee example.

Using a subquery:

SELECT
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

Using a CTE:

WITH average_salary AS (
    SELECT AVG(salary) AS avg_salary
    FROM employees
)
SELECT
    e.employee_name,
    e.salary
FROM employees AS e
CROSS JOIN average_salary AS a
WHERE e.salary > a.avg_salary;

Both can produce the same result.

The main difference here is how the query is organized.

Feature	Subquery	CTE
Location	Nested inside another query	Defined before the main query using WITH
Readability	Good for simple queries	Often clearer for complex queries
Nesting	Can become difficult to read when heavily nested	Can break complex logic into named steps
Reuse within the same statement	Often requires repeating the subquery	A CTE can often be referenced multiple times
Recursive queries	Not normally used for recursion	Recursive CTEs can handle hierarchical problems
Lifetime	Exists only as part of its containing statement	Exists only for the statement in which it is defined

It is important to note that a CTE is not automatically faster than a subquery.

Modern database engines have query optimizers that determine how SQL statements should actually be executed. Depending on the database system and the query, a CTE may be inlined, materialized, or optimized in another way.

Therefore, CTEs should not automatically be chosen because they are assumed to improve performance.

Their biggest advantage is often clarity and organization.

---

When Should You Use a Subquery?

Subqueries work particularly well when the intermediate calculation is relatively simple and is needed in only one place.

For example:

SELECT
    product_name,
    price
FROM products
WHERE price > (
    SELECT AVG(price)
    FROM products
);

The logic is straightforward:

Find the average price and return products priced above it.

Writing a separate CTE may add unnecessary complexity to such a simple query.

Subqueries are commonly useful when working with:

• WHERE
• HAVING
• SELECT
• FROM
• IN
• EXISTS
• NOT EXISTS
• Comparison operators such as >, <, and =

---

When Should You Use a CTE?

CTEs become especially useful when:

• A query contains several logical steps.
• The same intermediate result needs to be referenced more than once.
• Nested subqueries are becoming difficult to understand.
• You want to give intermediate calculations meaningful names.
• You are working with hierarchical or recursive data.

For example, names such as:

customer_spending
monthly_revenue
department_average
top_customers
regional_sales

immediately communicate what each part of the query represents.

Compare that with several layers of unnamed nested queries.

A well-structured CTE can make complex analytical SQL read almost like a sequence of instructions.

---

Common Mistakes When Using Subqueries and CTEs

1. Returning Multiple Values Where One Is Expected

Consider:

SELECT *
FROM employees
WHERE salary = (
    SELECT salary
    FROM employees
    WHERE department = 'IT'
);

If the IT department contains several employees with different salaries, the subquery may return multiple rows.

But = expects a single value.

Depending on the intended question, an operator such as IN may be more appropriate:

SELECT *
FROM employees
WHERE salary IN (
    SELECT salary
    FROM employees
    WHERE department = 'IT'
);

Always think about whether your subquery is expected to return one value, one row, or multiple rows.

2. Making Subqueries Unnecessarily Deep

A query containing many levels of nested subqueries can quickly become difficult to understand.

For example:

Query
 └── Subquery
      └── Subquery
           └── Subquery

When the logic becomes difficult to follow, consider restructuring the query using CTEs.

3. Forgetting That a CTE Is Temporary

A CTE is available only to the SQL statement immediately associated with it.

For example:

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent
    FROM sales
    GROUP BY customer_id
)
SELECT *
FROM customer_spending;

After this statement finishes, you cannot run a separate query such as:

SELECT *
FROM customer_spending;

and expect the CTE still to exist.

If you need to permanently store a result, you may need a table, view, or another database object depending on your requirements.

4. Assuming CTEs Are Always Faster

CTEs are excellent for organizing SQL, but they do not automatically improve query performance.

Performance depends on factors such as:

• The database management system
• Available indexes
• Table sizes
• Join conditions
• Filtering
• Aggregations
• Query execution plans

Use CTEs primarily when they make your SQL logic clearer, and investigate the execution plan when performance matters.

---

Putting Everything Together

Subqueries and CTEs ultimately help us solve the same fundamental problem:

How can we use the result of one query as part of a larger analytical question?

A subquery places one query inside another:

Outer Query
     ↓
(Subquery)

A CTE takes a more step-by-step approach:

WITH intermediate_result AS (...)
              ↓
         Main Query

Neither approach is universally better.

For a short calculation that is used once, a subquery may be the simplest solution.

For an analysis containing several intermediate calculations, a CTE can make the logic significantly easier to follow.

---

Conclusion

As SQL queries become more advanced, business questions rarely require just one simple SELECT statement.

You may need to calculate an average before filtering records, summarize customer transactions before comparing spending patterns, or perform several intermediate calculations before producing the final result.

This is where subqueries and Common Table Expressions (CTEs) become valuable.

Subqueries allow us to place one query inside another and use the result as part of a larger SQL operation. They are particularly useful for concise calculations and filtering based on dynamically generated values.

CTEs, on the other hand, allow us to break more complex SQL logic into named, logical steps using the WITH keyword. This can make analytical queries easier to read, understand, debug, and maintain.

The easiest way to remember the distinction is:

A subquery nests the logic, while a CTE names and organizes the logic.

As a data analyst, you do not need to choose one approach for every situation. The important skill is understanding the problem, recognizing the shape of the intermediate result you need, and selecting the approach that expresses your logic most clearly.

Once you are comfortable with subqueries and CTEs, you are ready to tackle more advanced SQL techniques such as window functions, recursive CTEs, ranking, and multi-step analytical queries.