MySQL for Developers
Sequence
Common table expressions (CTEs)
Sequence

Using common table expressions (CTEs) in MySQL

It can be challenging to write complicated queries that require multiple joins or subqueries. But fear not! With the introduction of Common Table Expressions (CTEs), the process of composing complex queries becomes much more manageable. In this video, we will explore what CTEs are, how they work, and how you can use them to simplify your queries.

What are common table expressions (CTEs)?

At their core, a Common Table Expression is a SQL statement that can be referenced within the context of a larger query. CTEs are supported in MySQL 8. CTEs allow queries to be broken down into smaller parts that can be more easily comprehended by us mere humans. By doing so, it becomes simpler to reason about and compose complex queries.

How to use CTEs in MySQL

In MySQL, CTEs can be created using the WITH keyword. For example:

SQL
with cte_name as (
select ...
)
select ...
from cte_name

The WITH keyword is followed by the name of the CTE and the query that generates it in parentheses. After defining the CTE, we can reference it in another query. Here's an example that uses CTEs to find customers who have spent more than the average on purchases at a particular store:

SQL
with spend_last_6 as (
select
customer_id,
sum(amount) as total_spend
from
payment
inner join customer on customer.id = payment.customer_id
where
store_id = 1
and payment_date > CURRENT_DATE - INTERVAL 6 MONTH
group by
cusomter_id
)
select * from spend_last_6

This query uses CTEs to define a temporary table called spend_last_6 which encapsulates all the logic required to calculate the amount a customer has spent in the last six months at store number one. Then we can select from it as if it were a normal table!

Conclusion

Common Table Expressions offer a powerful tool for composing complex queries in MySQL. By breaking down queries into smaller, more reusable parts, you can make your code more readable and maintainable. Furthermore, by using CTEs, you can reduce the number of times that MySQL needs to execute a query, resulting in faster and more efficient queries. If you haven't already, give CTEs a try in your next project, and see how much simpler your queries can become!

About this lesson

Common Table Expressions are a powerful way to refactor complicated queries into more readable versions. MySQL also provides a few optimizations for them beyond what subqueries offer.

09:16
Closed captioned

Meet your instructor, Aaron Francis

Developer Educator at PlanetScale

I’m a dad to one-year-old twins and a software developer with a keen interest in processes and automation.

Feedback or questions? Reach out to our team at education@planetscale.com.

By submitting your email, you agree to the processing of your personal information by PlanetScale as described in the Privacy Policy.