Common Table Expression in MySQL

Common Table Expressions (CTE) 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

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 provides better performance than derived tables in MySQL.

Bonus Read : How to Calculate Margin in MySQL

Common Table Expression in MySQL

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, the number of columns in column_list should be same as those in your query.

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

Bonus Read : How to Get Top Selling Product using SQL Query

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.

Bonus Read : How to Use CASE statement in MySQL

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 …

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;

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 …

Bonus read : Get Multiple Counts with different conditions in MySQL

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.