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
);
