What is a CTE?
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard | Easy |
| Reusable | No | Yes |
| Debugging | Difficult | Easy |
3 rows
A temporary named result that exists only during your query.
Syntax
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard | Easy |
| Reusable | No | Yes |
| Debugging | Difficult | Easy |
3 rows
WITH temp_name AS (
SELECT ...
)
SELECT * FROM temp_name;Example: Before and After
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard | Easy |
| Reusable | No | Yes |
| Debugging | Difficult | Easy |
3 rows
Without CTE (nested, hard to read):
SELECT * FROM (
SELECT * FROM (SELECT * FROM students WHERE age > 18)
WHERE grade = 'A'
) WHERE score > 90;With CTE (clean steps):
WITH adults AS (
SELECT * FROM students WHERE age > 18
),
grade_a AS (
SELECT * FROM adults WHERE grade = 'A'
)
SELECT * FROM grade_a WHERE score > 90;Real Example
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard | Easy |
| Reusable | No | Yes |
| Debugging | Difficult | Easy |
3 rows
WITH customer_stats AS (
SELECT customer_id, SUM(total) AS spent
FROM orders
GROUP BY customer_id
)
SELECT c.name, cs.spent
FROM customers c
JOIN customer_stats cs ON c.id = cs.customer_id
WHERE cs.spent > 1000;Summary
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard | Easy |
| Reusable | No | Yes |
| Debugging | Difficult | Easy |
3 rows
- WITH: Creates temporary named result
- Makes complex queries readable
- Can create multiple CTEs
- Better than nested subqueries