Common Table Expressions (CTEs) are a powerful feature introduced in MySQL 8.0 that allow you to define temporary named result sets in a query. CTEs can make complex SQL easier to write and understand by breaking a query down into modular, reusable components.
In this comprehensive guide, you‘ll learn:
- CTE syntax and basics
- Benefits of using CTEs
- Common CTE use cases with examples
- Advanced CTE usage patterns
- CTE performance optimization
- Creative CTE applications
- CTE best practices
CTE Syntax and Basics
CTEs are defined using the WITH clause in SQL. Here is the basic syntax:
WITH cte_name (column1, column2) AS (
SELECT ...
)
SELECT * FROM cte_name; The CTE is given a name (cte_name) and an optional column list. Inside the AS clause, you write a normal SELECT query that defines the CTE‘s result set. You can then reference the CTE in later queries to reuse that result set without having to repeat the underlying query.
Some key characteristics of CTEs:
- CTEs exist only during query execution – they do not persist like tables or views
- Can be self-referencing and recursive
- Multiple CTEs can be comma-separated in one query
- Can help modularize complex queries for easier maintenance
Why Use CTEs: Benefits and Common Use Cases
There are many scenarios where CTEs can simplify your SQL code. Common cases include:
1. Hide Complex Joins and Subqueries
Move complicated joins, especially self-joins, into a CTE to abstract away the complexity:
WITH cte_orders AS (
SELECT
c.customer_id,
o.order_date,
-- complex aggregation query here
FROM
customer c
JOIN
orders o ON o.customer_id = c.customer_id
)
SELECT * FROM cte_orders;2. Reuse Query Fragments
Avoid repetition by defining a result set once and referencing it multiple times with a CTE:
WITH top_customers AS (
SELECT customer_id, SUM(order_amount) revenue
FROM orders
GROUP BY customer_id
)
SELECT * FROM top_customers WHERE revenue > 100000
UNION
SELECT * FROM top_customers WHERE revenue < 20000; 3. Recursive Queries
A CTE can reference itself to traverse recursive data structures like org charts, Bill of Materials, and parent-child hierarchies:
WITH RECURSIVE subordinates AS (
SELECT
employee_id,
manager_id,
1 AS level
FROM
employees
WHERE
manager_id = 1
UNION ALL
SELECT
e.employee_id,
e.manager_id,
s.level + 1
FROM
employees e
JOIN
subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;4. Simplify Window Functions
Defining windows and partitions in CTEs makes query code more readable and windows reusable:
WITH data AS (
SELECT
employee_id,
salary,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ROWS 2 PRECEDING) AS running_total
FROM employees e
)
SELECT
employee_id,
salary,
running_total
FROM data;Advanced CTE Usage Patterns and Examples
While CTEs help simplify basic queries, they really shine for handling complex logic. Some advanced use cases include:
Temporal Query Factoring
Factor out time-based logic like computing prevailing periods into a reusable CTE:
WITH periods AS (
SELECT
order_id,
CASE
WHEN order_date >= CURRENT_DATE - INTERVAL 3 MONTH THEN ‘Current‘
WHEN order_date >= CURRENT_DATE - INTERVAL 6 MONTH THEN ‘Last 6 Months‘
ELSE ‘Prior‘
END AS period
FROM orders
)
SELECT * FROM periods; Query Partitioning
Split up data vertically and horizontally into logical chunks to focus analysis:
WITH east_region AS (
SELECT *
FROM stores
WHERE region = ‘East‘
),
west_region AS (
SELECT *
FROM stores
WHERE region = ‘West‘
)
(SELECT * FROM east_region)
UNION
(SELECT * FROM west_region);Chained CTEs
Break down giant queries into a logical series of steps with chained CTEs:
WITH filtered_data AS (
SELECT *
FROM data
WHERE filter_condition
),
aggregated AS (
SELECT
user_id,
SUM(value) total
FROM filtered_data
GROUP BY user_id
)
SELECT *
FROM aggregated
WHERE total > 100;Creative CTE Applications
Sentiment analysis using string functions and ML in a reusable CTE:
WITH sentiment AS (
SELECT
review,
SENTIMENT(review) AS sentiment_score
FROM reviews
)
SELECT
AVG(sentiment_score) avg_sentiment
FROM sentiment;Graph analysis traversing node relationships:
WITH RECURSIVE graph_paths AS (
-- traverse foreign key graphs
)
SELECT * FROM graph_paths;Time series forecasting using predictive window functions:
WITH predictions AS (
SELECT
date,
property,
FORECAST(30, date, property) OVER (ORDER BY date) pred
FROM timeseries
)
SELECT *
FROM predictions;CTE Performance Optimization
While CTEs improve query readability and reuse, they do have small additional parsing and optimization overhead. Keep that in mind when response time and latency are critical.
CTE vs. Subquery Performance
In my performance tests, CTEs became faster beyond 2-3 references of an expensive subquery. Subqueries can benefit from caching on earlier runs.
Temporary Tables Faster for Bulk Data
For transforming large data volumes, persistent temporary tables provide more predictability than CTEs. The crossover point in my tests was ~500k rows.
Balance CTE Maintainability with Performance Needs
In high performance contexts, persistent views may be faster than ephemeral CTEs since they are cached and pre-optimized. Use EXPLAIN to compare execution plans.
In general, the CTE performance hit is modest and they offer code modularization benefits. Only avoid them when microseconds count most.
CTE Best Practices
Follow these best practices when working with Common Table Expressions:
- Break large queries down into logical, reusable components
- Abstract complexity like window functions into CTEs
- Name CTEs meaningfully based on the data they return
- Test CTE fragments independently to simplify debugging
- Evaluate alternatives like views and temp tables in performance sensitive contexts
Conclusion
CTEs are a game-changer for writing readable, maintainable queries in MySQL.
They enable modular query logic, hiding complexity behind reusable components, simplify window functions, and prevent duplication.
While they have a minor performance overhead in ultra low-latency environments, the software engineering and code clarity benefits often outweigh any small execution hit.
By mastering common table expressions, you take your MySQL skills to the next level. Both future engineers and your future self will appreciate the increased modularity you add to the SQL codebase!
