How to use MySQL CTE?

What is MySQL CTE?

CTE, or common table expression, is a named temporary result set that exists within the scope of a single statement and that can be referred to later within that statement, possibly multiple times.

How to use CTE in MySQL?

1. Syntax

In MySQL, a CTE will be defined using a WITH clause at the beginning, then you specify the CTE name, and the subquery that produce the results set.

Here is the common syntax to define the CTE in MySQL:

WITH
	cte_name [column_list] AS (subquery)
	[, cte_name2 [column_list2] AS (subquery2)] ...

The column_list is optional. If you specify it, the number of columns in the subquery must be the same as the number of columns in the column_list. If you omit the column_list, the CTE will use the column list of the subquery.

Simple example of a CTE being used:

WITH simplecte AS (SELECT emp_no, title FROM company)
SELECT * FROM simplecte LIMIT 2;

2. A CTE with WITH clause will be used in these contexts:

At the beginning of SELECT, UPDATE, and DELETE statements:

WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...

At the beginning of subqueries:

SELECT * FROM (WITH ... SELECT ...) AS tbl…

Immediately preceding SELECT for statements that include a SELECT statement:

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

3. Example

We have table company:

emp_no	title	from_date	to_date
10009	Assistant Engineer	1985-02-18	1990-02-18
10013	Senior Staff	1985-10-20	9999-01-01
10048	Engineer	1985-02-24	1987-01-27
10064	Staff	1985-11-20	1992-03-02
10070	Technique Leader	1985-10-14	9999-01-01
10098	Engineer	1985-05-13	1992-05-13
10126	Staff	1985-09-08	1991-09-08
10133	Engineer	1985-12-15	1991-12-15
10137	Staff	1985-02-18	1994-02-18
10144	Staff	1985-10-14	1992-10-14

Now define a CTE all_engineer to get all records with title Engineer:

WITH all_engineer AS (SELECT * FROM company WHERE title = 'Engineer')
SELECT emp_no, title, from_date from all_engineer;

The result is:

emp_no	title	from_date
10048	Engineer	1985-02-24
10098	Engineer	1985-05-13
10133	Engineer	1985-12-15

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.