Tutorials›SQL›CTEs — Common Table Expressions
🟢 Free Demo
SQL TutorialTopic 20 of 20

CTEs — Common Table Expressions

Write cleaner, more readable complex queries using WITH

✅ What You Will Learn

›What a CTE is and how it differs from a subquery
›How to write WITH clause syntax
›How to chain multiple CTEs
›How to use CTEs for recursive queries
›When to use CTE vs subquery vs temp table

A CTE (Common Table Expression) is a named temporary result set defined at the start of a query using the WITH keyword. Think of it as giving a subquery a name so you can reference it clearly, multiple times if needed.

CTEs make complex queries dramatically easier to read and debug. Instead of nesting subqueries inside each other, you build the result step by step — each step has a clear name. Senior analysts use CTEs in almost every complex query they write.

📋 CTEs break complex queries into named steps using this orders table

order_idcustomer_nameproductamountcity
1001Rahul SharmaLaptop45000Delhi
1002Priya VermaMobile Phone18000Noida
1003Amit KumarHeadphones3500Gurgaon
1004Sneha KapoorLaptop52000Delhi
1005Vikram SinghTablet28000Noida
1006Neha JoshiLaptop48000Delhi

Syntax

SQL SYNTAX
WITH cte_name AS (
  SELECT ...
  FROM ...
  WHERE ...
)
SELECT *
FROM cte_name;

-- Multiple CTEs
WITH cte1 AS (...),
     cte2 AS (...)
SELECT *
FROM cte1
JOIN cte2 ON ...;

Examples

Example 1Rewrite a subquery as a CTE
-- Subquery version (hard to read)
SELECT customer_name, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- CTE version (much clearer)
WITH avg_order AS (
  SELECT AVG(amount) AS avg_amount
  FROM orders
)
SELECT o.customer_name, o.amount
FROM orders o
JOIN avg_order a ON o.amount > a.avg_amount;
💡

The logic is identical — but the CTE version is self-documenting. Anyone reading it immediately knows what avg_order represents.

Example 2Multi-step analysis with multiple CTEs
WITH
-- Step 1: total revenue per customer
customer_revenue AS (
  SELECT customer_id,
         SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
),
-- Step 2: classify customers
customer_tiers AS (
  SELECT customer_id,
         total_spent,
         CASE
           WHEN total_spent >= 40000 THEN 'Platinum'
           WHEN total_spent >= 15000 THEN 'Gold'
           ELSE 'Silver'
         END AS tier
  FROM customer_revenue
)
-- Final result: join with customer names
SELECT c.customer_name,
       ct.total_spent,
       ct.tier
FROM customers c
JOIN customer_tiers ct ON c.customer_id = ct.customer_id
ORDER BY ct.total_spent DESC;
OUTPUT
customer_name | total_spent | tier
--------------|-------------|--------
Rahul Sharma  | 48500       | Platinum
Priya Verma   | 18000       | Gold
💡

Three logical steps, each clearly named. Debugging is easy — you can run each CTE separately to check its output.

📌 Key Points to Remember

  • ✓CTEs start with WITH and are defined before the main SELECT
  • ✓Multiple CTEs are separated by commas
  • ✓A CTE can reference a previous CTE defined in the same WITH block
  • ✓CTEs are not stored — they only exist for the duration of the query
  • ✓Use CTEs whenever a query has more than two levels of nesting

🏢 Real-World Application

CTEs are the professional way to write complex SQL queries. Without CTEs, a multi-step analysis becomes an unreadable mess of nested subqueries. With CTEs, you break the problem into named steps: WITH monthly_revenue AS (GROUP BY month), top_months AS (filter from monthly_revenue), final AS (join with targets) — each step is clear and testable. Data engineers at companies like Razorpay, CRED, and MakeMyTrip write production SQL using CTEs extensively. Recursive CTEs are used for hierarchical data — org charts, bill of materials, category trees. Mastering CTEs is a sign of SQL maturity.

⚠️ Common Mistakes to Avoid

WRONGUsing a comma before the first CTE or after the last CTE
FIXCTEs are separated by commas between them, not before the first or after the last. WITH cte1 AS (...), cte2 AS (...) SELECT ... — the comma goes between cte1 and cte2 only.
WRONGReferencing a CTE before it is defined
FIXCTEs are evaluated in the order they are written. A later CTE can reference an earlier one, but not vice versa. Chain them in logical dependency order.
WRONGUsing CTEs when a simple subquery or direct query would be clearer
FIXCTEs add value for complex multi-step logic. For a simple one-step transformation, a subquery is often cleaner. Use CTEs when you have 2+ logical steps or need to reuse an intermediate result.

❓ Frequently Asked Questions

What is a CTE in SQL?

CTE stands for Common Table Expression. It is a named temporary result set defined with the WITH clause before a SELECT statement. CTEs make complex queries more readable by breaking them into named steps.

What is the difference between a CTE and a subquery?

Both create temporary result sets. CTEs are defined once at the top with WITH and referenced by name — making them reusable within the query and easier to read. Subqueries are written inline and cannot be reused without repeating code.

Can you have multiple CTEs in one query?

Yes. Chain multiple CTEs with commas: WITH cte1 AS (SELECT ...), cte2 AS (SELECT ... FROM cte1) SELECT * FROM cte2. Each CTE can reference previously defined CTEs in the same WITH clause.

What is a recursive CTE in SQL?

A recursive CTE references itself to traverse hierarchical data. It has an anchor member (starting point) and a recursive member (next step). Used for org charts, category trees, folder structures, and network graphs.

Is a CTE better than a temporary table?

CTEs are faster to write and automatically dropped after the query completes. Temporary tables persist for the session, can be indexed, and work better for very large intermediate datasets that need to be queried multiple times. For most analytics queries, CTEs are sufficient and preferred.

✏️ Practice Exercise

Rewrite the GROUP BY + HAVING query you wrote earlier (products with revenue above ₹10,000) as a CTE. Then add a second CTE that ranks those products by revenue.

← PreviousWindow Functions
🎉 Series Complete!
Join Live SQL Course →
🎓 Level Up Faster

Learn SQL with Live Trainer Guidance

These tutorials give you the theory. Our live SQL course at EVIKA Academy, Noida teaches you to apply SQL on real company datasets — with a trainer who uses it daily at MakeMyTrip.