|
Getting your Trinity Audio player ready...
|
If Views help you reuse queries…
CTEs help you write complex queries in a clean and readable way.
In real-world reporting, queries become:
- Long
- Nested
- Hard to read
- Difficult to debug
CTE solves this problem.
What is a CTE?
A CTE (Common Table Expression) is a temporary result set
that you define using the WITH keyword
and use immediately inside your query.
Think of it as:
A temporary named query that exists only for that SQL statement.
Unlike a View:
- CTE is not stored in the database
- It runs only during execution
- It improves readability
Basic Syntax
PostgreSQL & MySQL (8.0+)
WITH cte_name AS (
SELECT column1, column2
FROM table_name
WHERE condition
)
SELECT *
FROM cte_name;Using FMCG Dataset Tables
We’ll use:
- customers
- products
- employees
- sales
Simple CTE Example
Scenario:
Get total sales per customer, then filter customers whose total sales > 50,000.
Without CTE → nested query (harder to read)
With CTE → clean.
WITH customer_sales AS (
SELECT
customer_id,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY customer_id
)
SELECT *
FROM customer_sales
WHERE total_sales > 50000;Much cleaner, right?
CTE with JOIN
Scenario:
Find total sales with customer names.
WITH customer_sales AS (
SELECT
customer_id,
SUM(sale_amount) AS total_sales
FROM sales
GROUP BY customer_id
)
SELECT
c.customer_name,
cs.total_sales
FROM customer_sales cs
JOIN customers c
ON cs.customer_id = c.customer_id
ORDER BY cs.total_sales DESC;Readable. Structured. Professional.
Read More: Joins in SQL
Multiple CTEs in One Query
You can define multiple CTEs separated by comma.
Scenario:
- Calculate total sales per product
- Calculate total sales per customer
- Show both
WITH product_sales AS (
SELECT
product_id,
SUM(sale_amount) AS total_product_sales
FROM sales
GROUP BY product_id
),
customer_sales AS (
SELECT
customer_id,
SUM(sale_amount) AS total_customer_sales
FROM sales
GROUP BY customer_id
)
SELECT
ps.product_id,
ps.total_product_sales,
cs.total_customer_sales
FROM product_sales ps
JOIN sales s ON ps.product_id = s.product_id
JOIN customer_sales cs ON s.customer_id = cs.customer_id;Now your query is modular.
Replacing Subqueries Using CTE
Earlier in Subqueries article:
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM sales
GROUP BY customer_id
HAVING SUM(sale_amount) > 50000
);Now using CTE:
WITH high_value_customers AS (
SELECT customer_id
FROM sales
GROUP BY customer_id
HAVING SUM(sale_amount) > 50000
)
SELECT *
FROM customers
WHERE customer_id IN (
SELECT customer_id FROM high_value_customers
);Much easier to understand.
Read More: Subqueries in SQL
CTE for Data Cleaning
Let’s clean sales first, then aggregate.
WITH clean_sales AS (
SELECT
sale_id,
customer_id,
CAST(sale_amount AS NUMERIC) AS sale_amount,
COALESCE(quantity, 0) AS quantity
FROM sales
)
SELECT
customer_id,
SUM(sale_amount) AS total_sales
FROM clean_sales
GROUP BY customer_id;Notice how logic is separated:
Step 1 → Clean
Step 2 → Analyze
That’s clean architecture.
Read More: Data Cleaning in SQL
Recursive CTE (Advanced)
Used for hierarchical data.
Example: employee-manager hierarchy.
PostgreSQL & MySQL 8+
WITH RECURSIVE employee_hierarchy AS (
SELECT
employee_id,
employee_name,
manager_id
FROM employees
WHERE manager_id IS NULL UNION ALL SELECT
e.employee_id,
e.employee_name,
e.manager_id
FROM employees e
JOIN employee_hierarchy eh
ON e.manager_id = eh.employee_id
)
SELECT *
FROM employee_hierarchy;Used in:
- Organizational structure
- Category tree
- Bill of materials
- Referral systems
Very powerful feature.
PostgreSQL vs MySQL Differences
| Feature | PostgreSQL | MySQL |
|---|---|---|
| Basic CTE | ✅ | ✅ (8.0+) |
| Recursive CTE | ✅ | ✅ (8.0+) |
| Performance | Strong optimization | Good but improving |
| Older versions support | Yes | No before 8.0 |
Important: MySQL before 8.0 does NOT support CTE.
When to Use CTE in SQL
Use CTE when:
✔ Query becomes too long
✔ Multiple aggregations needed
✔ Cleaning + reporting in same query
✔ Replacing nested subqueries
✔ Writing recursive logic
Avoid CTE when:
❌ Very simple query
❌ Performance-sensitive heavy loops (sometimes subquery performs faster)
CTE vs View
| Feature | CTE | View |
|---|---|---|
| Stored permanently | ❌ | ✅ |
| Exists temporarily | ✅ | ❌ |
| Reusable across queries | ❌ | ✅ |
| Good for complex one-time logic | ✅ | ❌ |
| Good for reporting layer | ❌ | ✅ |
CTE = Temporary structured logic
View = Permanent saved logic
Also Read: Views in SQL
Real MIS Reporting Example
Scenario:
Management wants:
- Total sales
- Average sale
- Number of transactions
- Only for active customers
Single structured query:
WITH active_customers AS (
SELECT customer_id
FROM customers
WHERE status = 'Active'
),
customer_sales AS (
SELECT
customer_id,
SUM(sale_amount) AS total_sales,
AVG(sale_amount) AS avg_sale,
COUNT(*) AS transactions
FROM sales
GROUP BY customer_id
)
SELECT
cs.customer_id,
cs.total_sales,
cs.avg_sale,
cs.transactions
FROM customer_sales cs
JOIN active_customers ac
ON cs.customer_id = ac.customer_id;Readable. Maintainable. Professional.
Common Mistakes
❌ Forgetting comma between multiple CTEs
❌ Not using RECURSIVE keyword for recursive CTE
❌ Naming conflicts
❌ Overusing CTE when simple query works
Why CTE is Important for Data Analysts
Because real-world queries:
- Combine cleaning
- Aggregation
- Ranking
- Filtering
- Joining
Without structure → messy
With CTE → readable
Interviewers LOVE CTE questions.
Final Summary
CTE (Common Table Expressions) in SQL allow you to define temporary named result sets using the WITH keyword. They improve readability, replace complex nested subqueries, support modular query design, and enable recursive logic for hierarchical data.
Both PostgreSQL and MySQL (8.0+) support CTEs, including recursive CTEs. CTEs are ideal for complex analytics, data cleaning pipelines, and structured MIS reporting queries. Mastering CTEs transforms long, confusing SQL statements into clean, professional, and maintainable queries.