What is a CTE?
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
CTE (Common Table Expression) is like creating a temporary named table that exists only during your query.
Simple analogy: Like writing notes on a scratch paper before writing the final answer.
Basic Syntax
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
WITH temp_name AS (
SELECT ...
)
SELECT * FROM temp_name;Simple Example
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
Without CTE (hard to read):
SELECT * FROM (
SELECT name, age FROM students WHERE age > 18
) AS adults
WHERE name LIKE 'J%';With CTE (easier to read):
WITH adults AS (
SELECT name, age FROM students WHERE age > 18
)
SELECT * FROM adults WHERE name LIKE 'J%';Much cleaner!
Real Example 1: Calculate Then Filter
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
WITH department_avg AS (
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
)
SELECT *
FROM department_avg
WHERE avg_salary > 60000;What this does:
- Create temporary result (department averages)
- Filter that result (show only high-paying departments)
Real Example 2: Multiple CTEs
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
WITH
high_scorers AS (
SELECT * FROM students WHERE score > 90
),
grade_a AS (
SELECT * FROM students WHERE grade = 'A'
)
SELECT h.name
FROM high_scorers h
JOIN grade_a g ON h.student_id = g.student_id;Create two temporary tables, then join them.
Real Example 3: Customer Analysis
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
WITH customer_totals AS (
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(total) AS total_spent
FROM orders
GROUP BY customer_id
)
SELECT
customers.name,
customer_totals.order_count,
customer_totals.total_spent
FROM customers
JOIN customer_totals ON customers.id = customer_totals.customer_id
WHERE customer_totals.total_spent > 1000;Why Use CTEs?
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
Benefits:
- Makes complex queries easier to read
- Can reuse the CTE result
- Better than nested subqueries
- Easier to debug
Without CTE:
SELECT ... FROM (SELECT ... FROM (SELECT ... FROM ...))
-- Nested and confusing!With CTE:
WITH step1 AS (...),
step2 AS (...),
step3 AS (...)
SELECT ... FROM step3;
-- Clear steps!Summary
CTE vs Subquery
| Aspect | Subquery | CTE |
|---|---|---|
| Readability | Hard to read | Easy to read |
| Reusable | No | Yes |
| Multiple | Nested (messy) | Sequential (clean) |
| Debugging | Difficult | Easy |
4 rows
CTE = Temporary named result
- Use WITH keyword
- Makes queries readable
- Like creating temporary tables
- Exists only during query
- Better than nested subqueries