MySQL Common Table Expression (CTE)

In MySQL, if we want to get the final result from the result of another query, we usually use derived tables to store the result of the previous query. However, MySQL provides us with another way to perform the same operation in a better way. We can use MySQL CTE which stands for common table expression to write complex queries in a more readable format.

In this tutorial, we will see what is CTE, its syntax, and examples.

Also read: Python-MySQL BLOB

What is MySQL CTE?

MySQL CTE is used to assign names to the temporary results which are produced by a query. The CTE we define exists only within the execution scope of that particular statement such as INSERT, UPDATE, DELETE, SELECT etc.

How is it different from the derived table?

Unlike in the derived tables, where we store the temporary result in an object, in the CTE, we use the temporary result right inside the same query without storing it anywhere for further use in another query.

You will understand it easily by the syntax and examples.

But let’s see CTE’s characteristics and important points-

• CTE is defined using the WITH clause.
• We can define more than one CTEs in the same query.
• One CTE can be used to reference another CTE in the same WITH scope.
• CTE can be used in that query only in which it is defined.

MySQL CTE Syntax

Following is the syntax of defining a CTE-

WITH cte_name (column_list) AS (
    query
) 
SELECT * FROM cte_name;Code language: SQL (Structured Query Language) (sql)

Where,

• cte_name – It is the name of the CTE for further use.
• column_list – names of columns. It is optional. However, the column_list must be the same as of the columns specified in the query.
• query – query to get the result.
• At the last, you can use the cte_name as a table name to derive the final result.

MySQL CTE Example

To demonstrate the CTE example, we will use the following table and we will insert some data into it.

CREATE TABLE emps(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT,
city VARCHAR(100),
salary FLOAT
);Code language: SQL (Structured Query Language) (sql)

INSERT INTO emps(name,age,city,salary) VALUES
("John", 45, "New York", 60000),
("Jane", 30, "Boston", 80000),
("Mary", 34, "California", 30000),
("Mike", 40, "Arizona", 50000),
("Yash", 35, "Florida", 50000),
("Raj", 30, "New York", 60000),
("Ravi", 25, "Arizona", 50000),
("Cathey", 50, "Arizona", 80000),
("sara", 40, "California", 60000),
("susan", 35, "Boston", 50000),
("alice", 30, "Alaska", 60000),
("bob", 40, "SA", 50000),
("charlie", 35, "Alaska", 50000),
("david", 30, "Alaska", 60000);Code language: SQL (Structured Query Language) (sql)

SELECT * FROM emps;Code language: SQL (Structured Query Language) (sql)

Here, we will see an example only to demonstrate how MySQL CTE works.

We will find the list of employees who work in Arizona and have ages greater than 25.

For that, we need to find a list of employees working in Arizona first. We will write the query to find those employees inside the WITH clause. Using that CTE name, we will get the list of employees having ages greater than 25.

Let’s proceed.

WITH emps_arizona AS(
SELECT * FROM emps WHERE city="Arizona"
)
SELECT * FROM emps_arizona WHERE
age > 25 ORDER BY age;Code language: SQL (Structured Query Language) (sql)

There are three employees who work in Arizona. However, only two of them have an age greater than 25.

Let’s see if we get the correct result.

As you can see, we have got the expected result.

MySQL CTE Advanced Example

In this example, we will see how to use joins inside the CTE to create a more readable form.

For that, we will use two tables- customers and orders.

Now, we will write a query to join two tables and display only particular column values in a more readable way.

WITH temp AS(
SELECT cust_id, name, occupation FROM customers INNER JOIN orders USING (cust_id)
)
SELECT * FROM orders JOIN temp USING (cust_id);Code language: SQL (Structured Query Language) (sql)

Here, we first join the customers and orders tables to find the cust_id, name and occupation. Then we again join the CTE table with the Orders table to get all details from the orders table and the previously fetched result from the CTE query.

Let’s see the output now.

If you had to get the same result, you would need to write an alias for both the tables and using that aliases, get the value of each attribute of the tables. But using the CTE, we can read it in a more simple and readable way.

Conclusion

In this tutorial, we have learned what is CTE and how to use it through simple and effective examples. There can be more use cases where you will need to implement CTE and that is totally up to you to use CTE or any other method. However, you can practice it by having a good command of advanced MySQL terms.