Common Table Expression(CTE) in MySQL

Last updated on June 20th, 2024 at 06:33 am

Common Table Expressions (CTE) are a useful feature that allow MySQL developers to name temporary result of SQL queries. They allow you to easily write complex SQL queries in MySQL. In this article, we will look at what is Common Table Expression in MySQL and how to write CTEs in MySQL.

What is Common Table Expression (CTE)

Often database developers and data analysts end up creating really long SQL queries with many subqueries. They are not only difficult to understand but also slow in performance. Common Table Expression is a named result set whose scope is limited to the SQL query in which it is defined and used. It is similar to a derived table but it is not stored as an object and can be self-referencing, unlike derived tables. As a result, CTE can speed up queries & give better performance than derived tables in MySQL. They improve query readability and can be re-used in other queries. You can even use it to create self-referencing recursive queries.

Common Table Expression in MySQL

We will learn the syntax of basic CTE and then some of its common variations. Here is the syntax of common table expression in MySQL.

WITH cte_name (column_list) AS (     
      query 
)  
SELECT * FROM cte_name;

In the above query, you need to specify CTE’s name, the SQL query whose result should be referenced using CTE name. Please note, column_list is optional. But if you mention it, then the number of columns in column_list should be same as those in your query.

You can also define multiple CTEs using a single statement as shown below.

WITH
    cte_name1 AS (
        -- Query here
    ),
    cte_name2 AS (
        -- Query here
    )

Let us look at an example of common table expression (CTE).

Let us say you have sales(id, order_date, amount) table.

mysql> create table sales(id int, order_date date, amount int);

mysql> insert into sales(id, order_date, amount)
      values(1, '2021-01-01', 200),
      (2, '2021-01-02', 250),
      (3, '2021-01-03', 220),
      (4, '2021-01-04', 230),
      (5, '2021-01-05', 210),
      (6, '2021-01-06', 100),
      (7, '2021-01-07', 120),
      (8, '2021-01-08', 150),
      (9, '2021-01-09', 180),
      (10, '2021-01-10', 200);

 mysql> select * from sales;
 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    1 | 2021-01-01 |    200 |
 |    2 | 2021-01-02 |    250 |
 |    3 | 2021-01-03 |    220 |
 |    4 | 2021-01-04 |    230 |
 |    5 | 2021-01-05 |    210 |
 |    6 | 2021-01-06 |    100 |
 |    7 | 2021-01-07 |    120 |
 |    8 | 2021-01-08 |    150 |
 |    9 | 2021-01-09 |    180 |
 |   10 | 2021-01-10 |    200 |
 +------+------------+--------+

Here is a CTE to select only sales records that have amount>200.

with test_cte as (
     select id,order_date,amount from sales
     where amount>200
 ) select * from test_cte; 

 +------+------------+--------+
 | id   | order_date | amount |
 +------+------------+--------+
 |    1 | 2021-01-01 |    200 |
 |    2 | 2021-01-02 |    250 |
 |    3 | 2021-01-03 |    220 |
 |    4 | 2021-01-04 |    230 |
 |    5 | 2021-01-05 |    210 |
 |   10 | 2021-01-10 |    200 |
 +------+------------+--------+

In the above CTE, we specify the SELECT query to filter rows with amount>200 inside WITH clause.

You can also change the names of your table columns via CTE. Here is an example where we have renamed the order_date column in sales table to sale_date in CTE. Here, MySQL will look at the number of columns mentioned in CTE and its query and assign column names based on their order in the query result. Here sale_date is 2nd column in CTE and order_date is 2nd column in SELECT query so order_date is renamed to sale_date in CTE.

with test_cte (id, sale_date, amount) as (
     select id,order_date,amount from sales
     where amount>200
 ) select * from test_cte; 

 +------+------------+--------+
 | id   | sale_date  | amount |
 +------+------------+--------+
 |    1 | 2021-01-01 |    200 |
 |    2 | 2021-01-02 |    250 |
 |    3 | 2021-01-03 |    220 |
 |    4 | 2021-01-04 |    230 |
 |    5 | 2021-01-05 |    210 |
 |   10 | 2021-01-10 |    200 |
 +------+------------+--------+

Variations of CTE

Please note, you can use CTE with SELECT, UPDATE, INSERT and DELETE statements. Here are the syntaxes for the same.

WITH … SELECT …
WITH … UPDATE …
WITH … DELETE …

You can also use the result of CTE as input to other queries, just as we use result of sub queries as input to other queries. For example, if you use the result of a SELECT statement as the input for CREATE/INSERT/REPLACE statements, then also you can use WITH clause in it. Here are some examples,

CREATE TABLE … WITH … SELECT …
CREATE VIEW … WITH … SELECT …
INSERT … WITH … SELECT …
REPLACE … WITH … SELECT …
DECLARE CURSOR … WITH … SELECT …
EXPLAIN … WITH … SELECT …

CTE as Subquery

Also, you may use a CTE as a subquery, as shown below.

SELECT … WHERE id IN (WITH … SELECT …);
SELECT * FROM (WITH … SELECT …) AS temporary_table;

Recursive CTE

Here is the syntax for recursive CTE in MySQL. You need to mention RECURSIVE keyword after WITH in SQL query.

WITH RECURSIVE cte_name (column_names) AS ( subquery )   
SELECT * FROM cte_name;  

Common Table Expression in Ubiq

Ubiq Reporting tool supports CTE-based SELECT queries and makes it easy to visualize SQL results in different ways. It also allows you to create dashboards & charts from MySQL query results. Here is the above CTE query in Ubiq.

In fact, after you run the query, you can simply click a visualization type to plot the result in a chart.

Conclusion

In this article, we have learnt what common table expressions (CTE) are in MySQL. Please note, CTE is supported in all major database systems such as SQL Server, Oracle, PostgreSQL, etc. We have also learnt several important variations in its syntax, and also recursive CTEs. They are a great way to simplify complex queries and good alternative to subqueries.

Need a reporting tool for MySQL? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Also Read:
How to Calculate Margin in MySQL
How to Get Top Selling Product using SQL Query
How to Use CASE statement in MySQL
Get Multiple Counts with different conditions in MySQL