SQL Query Optimization: When to Use CTEs vs. Subqueries

WSDA News | February 28, 2025

SQL is a powerful tool for managing and analyzing data, and two commonly used techniques for writing complex queries are Common Table Expressions (CTEs) and subqueries.

Both allow you to break down queries, improve readability, and make SQL code more manageable. However, knowing when to use each technique is crucial for performance and clarity.

This guide explains the key differences, practical use cases, and how to choose the best approach for your SQL queries.

Understanding CTEs and Subqueries

Common Table Expressions (CTEs)

A CTE is a temporary, named result set that exists only during the execution of a query. It is defined using the WITH statement and improves readability, especially for complex queries.

Example: Finding employees who made more than $50,000 in total sales

WITH EmployeeSales AS (
    SELECT 
        employee_id, 
        SUM(sales_amount) AS total_sales
    FROM Sales
    GROUP BY employee_id
)
SELECT 
    employee_id, 
    total_sales
FROM EmployeeSales
WHERE total_sales > 50000;        

Advantages of Using CTEs:

• Makes queries easier to understand by breaking them into logical steps
• Allows the same result set to be referenced multiple times within a query
• Useful for recursive queries and simplifying nested logic

Subqueries

A subquery is a query embedded inside another SQL statement. It is commonly used for filtering data dynamically or performing calculations for comparison.

Example: Finding employees who made more sales than the average sales per employee

SELECT 
    employee_id, 
    SUM(sales_amount) AS total_sales
FROM Sales
GROUP BY employee_id
HAVING SUM(sales_amount) > (
    SELECT AVG(sales_amount) FROM Sales
);        

Advantages of Using Subqueries:

• Useful for performing quick one-time calculations
• Helps filter results dynamically
• Keeps queries compact without requiring a named result set

Key Differences Between CTEs and Subqueries

When to Use CTEs vs. Subqueries

Situations Where CTEs Are More Effective:

• When breaking down a complex query into logical steps for better readability
• When the query result set needs to be referenced multiple times within the same query
• When working with hierarchical or recursive data, such as organizational structures
• When maintainability is a priority and improving query structure is necessary

Situations Where Subqueries Are More Effective:

• When filtering data dynamically based on calculations, such as comparisons with an average
• When performing quick, simple lookups without the need for reuse
• When avoiding the creation of a named temporary result set
• When optimizing performance for smaller datasets requiring fast execution

Real-World Example: CTE vs. Subquery

Finding Customers Who Spend More Than the Average Order Value

Using a CTE:

WITH AvgOrder AS (
    SELECT 
        AVG(order_total) AS avg_order_value
    FROM Orders
)
SELECT 
    o.customer_id, 
    o.order_total
FROM Orders o
CROSS JOIN AvgOrder
WHERE o.order_total > AvgOrder.avg_order_value;        

Using a Subquery:

SELECT customer_id, order_total
FROM (
    SELECT customer_id, order_total, AVG(order_total) OVER () AS avg_order_value
    FROM Orders
) subquery
WHERE order_total > avg_order_value;        

Both queries achieve the same result, but the CTE approach enhances readability and can be expanded upon easily, while the subquery approach is more compact and efficient for quick filtering.

Final Thoughts

CTEs and subqueries are essential SQL techniques that improve query structure and efficiency. Choosing the right approach depends on query complexity, reusability, and performance considerations.

• CTEs are ideal for breaking down queries into readable steps and when the result set needs to be referenced multiple times.
• Subqueries work best for quick calculations and filtering within a single-use query.

Mastering both techniques allows for more efficient and maintainable SQL queries, making data analysis workflows smoother and more effective.

Data No Doubt! Check out WSDALearning.ai and start learning Data Analytics and Data Science today!