Optimizing Nested Subqueries with CTEs

The Problem: "Spaghetti SQL"

Complex business questions often lead to SQL queries where one subquery is nested inside another, which might be nested inside another. This is often called "Spaghetti SQL" because tracing the logic is like trying to follow a single noodle in a bowl of spaghetti.

Consider a query like this:

-- This is a conceptual example of a messy query
SELECT *
FROM employees e
WHERE e.department_id IN (
    SELECT department_id
    FROM departments d
    WHERE d.avg_sales > (
        SELECT AVG(s.total)
        FROM sales s
        WHERE s.region = d.region
    )
);

While the database can execute this, it's very difficult for a human to read. What is the business logic? If it produces an error or an incorrect result, how would you debug it? This is the problem we'll solve today.

Refactoring with CTEs allows us to untangle this mess. We can isolate each logical block, give it a meaningful name, and arrange the query in a clear, top-to-bottom sequence.

A Guided Refactoring Example

Let's watch a practical demonstration of refactoring. The following video first builds a query using nested subqueries to solve a problem and then rewrites that exact query using CTEs, allowing for a direct comparison of the two approaches.

As the video demonstrates, refactoring provides several key benefits:

• Readability: The main query logic is no longer hidden in nested parentheses. It's a clean SELECT from named, logical blocks.
• Maintainability: If you need to change how "total sales" are calculated, you only need to edit the total_sales CTE. The change is isolated and easy to perform.
• Reusability: The total_sales result was calculated once and then used by the average_sales CTE. The subquery version had to repeat that logic, which is inefficient and error-prone.

A Step-by-Step Method for Untangling Queries

Now that you've seen the "before and after," let's establish a formal process for refactoring. The goal is to be methodical, not just haphazardly copy-pasting code.

From the article, we can summarize the refactoring process into these steps:

1. Understand the Goal: Before touching the code, read the query and figure out the business question it's trying to answer.
2. Identify the Logical Blocks: Look at the innermost subqueries first. What piece of information is each one trying to produce? Give it a mental name (e.g., "this finds all active promotions," "this gets user play counts").
3. Extract the Innermost Logic: Take the innermost subquery and make it your first CTE. Give it a descriptive name.
4. Test As You Go: Immediately after creating your first CTE, comment out the rest of the old query and write SELECT * FROM your_new_cte;. Run it and verify the results. This ensures your foundation is solid before you build on it.
5. Build Outward: Move to the next subquery that depended on the one you just extracted. Rewrite it as a second CTE, but instead of using a subquery, have it SELECT from your first CTE. Test this new CTE.
6. Repeat: Continue this process, replacing each layer of subqueries with a new, tested CTE that references a previous one.
7. Construct the Final Query: Once all the messy logic is encapsulated in a clean sequence of CTEs, write your final, simple SELECT statement that orchestrates them.

SELECT
  e.employee_name,
  e.salary
FROM
  employees e
JOIN
  departments d ON e.department_id = d.department_id
WHERE
  d.location = 'New York' AND e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
  );

WITH department_averages AS (
  -- Step 1: Calculate the average salary for each department
  SELECT
    department_id,
    AVG(salary) AS avg_dept_salary
  FROM
    employees
  GROUP BY
    department_id
),
new_york_departments AS (
  -- Step 2: Identify the departments in New York
  SELECT
    department_id
  FROM
    departments
  WHERE
    location = 'New York'
)
-- Final Step: Join the data and apply the final filter
SELECT
  e.employee_name,
  e.salary
FROM
  employees e
JOIN
  department_averages da ON e.department_id = da.department_id
JOIN
  new_york_departments nyd ON e.department_id = nyd.department_id
WHERE
  e.salary > da.avg_dept_salary;

Conclusion

You have now completed the core lessons on Common Table Expressions. You know how to build queries with them and, just as importantly, how to refactor existing queries to use them. This is a powerful combination of skills that separates a beginner from a proficient SQL user.

Key Takeaways:

• Nested subqueries ("spaghetti SQL") are hard to read, debug, and maintain.
• Refactoring involves methodically extracting nested logic into a sequence of named CTEs.
• A reliable process is to start from the innermost query, extract it to a CTE, test it, and build outwards.
• The final result is a query that is self-documenting, modular, and much easier to work with for you and your future teammates.

Preview of the Next Lesson:
We are now going to shift our focus from reading data to manipulating it. In the next module, you will learn the fundamentals of Data Definition Language (DDL) and Data Manipulation Language (DML). We will start by learning how to CREATE, ALTER, and DROP tables, which are the foundational commands for defining the very structure of your database.