The CTE is often easier to read when the query becomes complex.

A good rule:

Simple calculation → subquery can be fine.

Multiple logical steps → CTE is often clearer.

1️⃣7️⃣ CTE vs Derived Table

Conceptually, both can create an intermediate result.

Derived Table: Usually appears inside: FROM (...)

CTE: Defined before the main query: WITH Name AS (...)

CTEs generally make multi-step analytical queries easier to organize.

1️⃣8️⃣ CTE for Data Filtering

Suppose you only want 2026 orders.

WITH Orders_2026 AS (
    SELECT *
    FROM Orders
    WHERE Order_Date >= '2026-01-01'
      AND Order_Date < '2027-01-01'
)

SELECT
    Region,
    SUM(Sales) AS Total_Sales
FROM Orders_2026
GROUP BY Region;

This makes the query's logic easy to follow:

First → select 2026, Then → analyze by region

1️⃣9️⃣ CTE for Business Logic

Suppose you want to classify orders:

WITH Classified_Orders AS (
    SELECT
        Order_ID,
        Sales,
        CASE
            WHEN Sales >= 100000 THEN 'High'
            WHEN Sales >= 50000 THEN 'Medium'
            ELSE 'Low'
        END AS Sales_Category
    FROM Orders
)

SELECT
    Sales_Category,
    COUNT(*) AS Order_Count
FROM Classified_Orders
GROUP BY Sales_Category;

Now you've separated: Classification from: Aggregation. This is much easier to maintain.

2️⃣0️⃣ CTE for Multi-Step Analysis

Imagine the business asks:

Which region has the highest average customer sales?

A CTE can break this into understandable stages.

For example:

WITH Customer_Sales AS (
    SELECT
        Customer_ID,
        SUM(Sales) AS Total_Sales
    FROM Orders
    GROUP BY Customer_ID
),
Regional_Customer_Sales AS (
    SELECT
        c.Region,
        cs.Customer_ID,
        cs.Total_Sales
    FROM Customer_Sales cs
    JOIN Customers c
        ON cs.Customer_ID = c.Customer_ID
)

SELECT
    Region,
    AVG(Total_Sales) AS Avg_Customer_Sales
FROM Regional_Customer_Sales
GROUP BY Region
ORDER BY Avg_Customer_Sales DESC;

This is much easier to reason about than attempting everything at once.

2️⃣1️⃣ CTEs Are Not Permanent Tables

This is important.

A normal table: Customers, Orders, Products is stored in the database.

A CTE: WITH Customer_Sales AS (...) exists only for the duration of that query.

2️⃣2️⃣ CTEs and Performance

A common misconception is:

"CTEs are always faster than subqueries."

That's not necessarily true.

A CTE is primarily a query organization/readability tool.

Actual performance depends on: Database engine, Query structure, Indexes, Data volume, Optimizer behavior, Joins, Aggregations

So don't use a CTE simply because you think it automatically makes a query faster. Use it when it makes the logic clearer or otherwise fits your query design.

2️⃣3️⃣ Correlated Subquery

A correlated subquery references a column from the outer query.

Example:

SELECT
    e.Name,
    e.Salary
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.Department = e.Department
);