Optimizing Subqueries with CTEs and JOINs

The Problem: The "Pyramid of Doom"

Subqueries are queries nested inside another query. They are powerful but can quickly become problematic. When you have multiple levels of nested subqueries, especially in the FROM or JOIN clauses (often called derived tables), you end up with what's sometimes called the "pyramid of doom." The query's logic becomes difficult to follow because you have to read it from the inside out.

Let's look at an example. Imagine you need to find the top 5 clients by delivery volume in the last week, but exclude your own company, "Beekeeper". A solution using a subquery might look like this:

SELECT d.created_at::date, c.name, COUNT(1)
FROM deliveries d
JOIN clients c ON c.id = d.client_id
WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days'
AND c.id IN (
    -- Subquery to find the top 5 clients
    SELECT c1.id FROM clients c1
    JOIN deliveries d1 ON c1.id = d1.client_id
    WHERE d1.created_at >= date_trunc('day', now()) - INTERVAL '7 days'
    AND c1.name <> 'Beekeeper'
    GROUP BY c1.id
    ORDER BY count(d1.id) DESC
    LIMIT 5
)
GROUP BY c.name, d.created_at::date
ORDER BY c.name DESC;

This works, but the core business logic—identifying the top five clients—is buried inside the main query. It's hard to read and even harder to modify or debug. What if you also needed to reuse that list of top clients for another purpose in the same query? You'd have to copy and paste the entire subquery, violating the DRY principle.

Solution 1: Refactoring with Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a named, temporary result set that you define at the beginning of a query using the WITH keyword. You can then reference this "temporary table" by name throughout the rest of your query. This allows you to break down complex logic into a series of logical, readable steps.

To understand exactly how this works, let's watch a video that demonstrates the process.

Applying CTEs to Our Example

Now, let's refactor our "top 5 clients" query using a CTE. We can extract the subquery logic into a named CTE called top_five_clients.

WITH top_five_clients AS (
    SELECT c.id, c.name 
    FROM clients c
    JOIN deliveries d ON c.id = d.client_id
    WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days'
    AND c.name <> 'Beekeeper'
    GROUP BY c.id, c.name
    ORDER BY COUNT(d.id) DESC
    LIMIT 5
)
SELECT 
    d.created_at::date, 
    c.name, 
    count(1)
FROM deliveries d
JOIN top_five_clients c ON c.id = d.client_id
WHERE d.created_at >= date_trunc('day', now()) - INTERVAL '7 days'
GROUP BY c.name, d.created_at::date
ORDER BY c.name DESC;

Notice the difference:

• Top-Down Logic: The query now reads like a story. First, we define top_five_clients. Then, we use that result to get our final counts.
• Readability: The main SELECT statement is clean and its purpose is immediately clear: it joins deliveries with our pre-defined list of top clients.
• Maintainability: If the definition of a "top client" changes, you only need to edit the CTE at the top. The main query remains untouched.

The following article provides another great example, where a redundant LATERAL JOIN is simplified into a CTE that is then used twice. This highlights the reusability benefit.

Solution 2: Rewriting Subqueries as JOINs

Another common, and often inefficient, use of subqueries is as a column in the SELECT list. This is called a correlated subquery because it has to run once for every single row processed by the outer query. This is almost always less performant and harder to read than an explicit JOIN.

Let's watch a quick video that contrasts these two approaches perfectly.

As the video explains, rewriting the subquery as a JOIN makes the relationship between the tables explicit. The query optimizer often handles a JOIN more efficiently than a correlated subquery, but the primary benefit for an analyst is clarity and maintainability.

SELECT
    e.employee_name,
    (SELECT d.department_name FROM departments d WHERE d.department_id = e.department_id) AS department_name
FROM
    employees e;

SELECT
    e.employee_name,
    d.department_name
FROM
    employees e
INNER JOIN
    departments d ON e.department_id = d.department_id;

Summary: CTEs vs. Subqueries

Here is a quick reference table that summarizes the key differences between CTEs and Subqueries. It's a useful visual to reinforce today's concepts.

Conclusion

Congratulations on completing the Advanced SQL module! You've moved from writing basic queries to structuring complex, multi-step analyses and ensuring your results are accurate by handling tricky edge cases like NULL values. Today, you learned how to write clean, professional, and maintainable SQL, a skill that sets apart a junior analyst from a senior one.

Key Takeaways:

• Avoid the Pyramid: Nested subqueries make code hard to read and maintain.
• Use CTEs for Clarity: CTEs (WITH clause) are your primary tool for breaking complex analytical problems into logical, readable steps. They promote a top-down query structure.
• CTEs Improve Maintainability: By naming and isolating logic, CTEs make your queries easier to debug and update.
• CTEs Promote Reusability: A single CTE can be referenced multiple times within a query, adhering to the DRY principle.
• Prefer JOINs over Correlated Subqueries: Rewriting subqueries in the SELECT list as explicit JOINs makes your code clearer and often more performant.

Next Lesson Preview:

With a strong SQL foundation, we are now ready to pivot to another essential tool for data analysis. In our next module, Python for Data Analysis, we'll start with the absolute cornerstone of the Python data science ecosystem. You will learn to manipulate DataFrames using Pandas for efficient data cleaning, filtering, and transformation. This will open up a new world of analytical capabilities.