Mastering CTEs: Writing Cleaner, Better SQL

If you've ever looked at a SQL query with three levels of nested subqueries and felt a headache coming on, you're not alone.

Enter Common Table Expressions (CTEs).

Introduced in the WITH clause, CTEs allow you to define temporary result sets that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. Think of them as variables for your queries.

CTE vs. Subquery: The Readability Test

Let's look at a classic problem: Finding departments where the average salary is higher than the company-wide average.

The Subquery Way (Hard to read)

SELECT department, AVG(salary) as avg_dept_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
);

You have to read the code "inside out" to understand it.

The CTE Way (Easy to read)

With CTEs, you read from top to bottom. First, we calculate the company average. Then, we calculate department averages. Finally, we compare them. It tells a story.

[!TIP] Messy SQL? CTEs help readability, but formatting matters too. Use our SQL Formatter to instantly beautify your complex queries.

Anatomy of a CTE

WITH CteName AS (
    -- Your query here
    SELECT column1, column2 FROM table
)
SELECT * FROM CteName;

You can define multiple CTEs by separating them with commas:

Recursive CTEs: The Superpower

This is where CTEs do things standard subqueries simply can't. A Recursive CTE can reference itself. This is incredibly useful for hierarchical data (like org charts) or generating sequences.

Example 1: Generating a Sequence

Example 2: Traversing an Org Chart

Imagine a table where every employee has a manager_id. How do you find the entire chain of command for a specific employee?

How it works:

1. Base Case: Finds the root node (Big Boss).
2. Recursive Step: Finds everyone whose manager_id matches the id found in the previous step.
3. Repeat: Keeps going until no more matches are found.

When to Use CTEs

1. Readability: When a query is complex and needs to be broken down into logical steps.
2. Reuse: When you need to reference the same sub-result multiple times in the main query.
3. Recursion: When dealing with hierarchical data (trees, graphs) or generating series.

Practice Challenge

Try modifying the Org Chart example above to:

1. Find only the direct reports of 'VP Sales'.
2. Display the hierarchy path like "Big Boss > VP Eng > Lead Dev".

CTEs are a hallmark of intermediate-to-advanced SQL. Mastering them will make your code cleaner, easier to debug, and far more powerful.