CTEs (common table expressions - also commonly referred to as "with clauses") are an extremely powerful tool in SQL! Similar to subqueries, they can pull "a query in a query," but can be called multiple times within the same query especially easily! This is because CTEs are defined and named upfront.
Important note for MySQL Users: CTEs are only available in MySQL 8.0. For earlier versions of MySQL, use subqueries
Below are some FAQs on CTEs, post any further questions below!
1. When do I use a CTE?
CTEs are useful for running a query in a query (similar to subqueries!). CTEs come handy when we need to leverage a generated column in a query. For instance, the below instance would fail:
select
id
, first_name || ' ' || last_name as full_name
from
users
where
len(full_name)>20
In the above example, we only defined full_name in this select statement itself. Therefore, we cannot use the column alias name until this select statement has run completely.
To get this query to work, we need to wrap the above in a CTE. More info on CTE set up in FAQ 2!
with
user_cte as (
select
id
, first_name || ' ' || last_name as full_name
from
users
)
select
*
from
user_cte
where len(full_name)>20
2. How do I set up a CTE?
The basic structure of a CTE looks like this:
with <cte_name> as (
<query for CTE here>
)
select <columns> from <cte_name>
...
with
user_cte as (
select
id
, first_name || ' ' || last_name as full_name
from
users
)
select
*
from
gameplays
join user_cte on
user_cte.id = gameplays.user_id
3. Can I use multiple CTEs in a query?
Absolutely! We can chain together CTEs as follows. Note that subsequent CTEs can call prior CTEs (in other words, we don't need to "nest" the CTEs)
with
<cte_name_1> as (
<query for CTE 1 here>
)
, <cte_name_2> as (
<query for CTE 2 here>
)
select
<columns>
from
<cte_name_1> join <cte_name_2>. . .
Note that we only use "with" to open the first CTE, with commas linking subsequent CTEs.
Feel free to comment with any more questions or use cases for CTEs!
