In MySQL, every statement or query produces a temporary result or relation. A common table expression or CTE is used to name those temporary results set that exist within the execution scope of that particular statement, such as CREATE, INSERT, SELECT, UPDATE, DELETE, etc.
Some of the key point related to CTE are:
- It is defined by using the WITH clause.
- The WITH clause allows us to specify more than one CTEs in a single query.
- A CTE can reference other CTEs that are part of the same WITH clause, but those CTEs should be defined earlier.
- The execution scope of CTE exists within the particular statement in which it is used.
MySQL CTE Syntax
The syntax of MySQL CTE includes the name, an optional column list, and a statement/query that defines the common table expression (CTE). After defining the CTE, we can use it as a view in a SELECT, INSERT, UPDATE, and DELETE query.
The following is the basic syntax of CTE in MySQL:
WITH cte_name (column_names) AS (query)
SELECT * FROM cte_name; It is to ensure that the number of columns in the CTE arguments must be the same as the number of columns in the query. If we have not defined the columns in the CTE arguments, it will use the query columns that define the CTE.
Similar to the derived table, it cannot be stored as an object and will be lost as soon as the execution of a query completed. A CTE provides better readability and also increases the performance as compared to the derived table.
Unlike a derived table, a CTE is a subquery that can be self-referencing using its own name. It is also known as recursive CTE and can also be referenced multiple times in the same query.
Some of the essential points related to the recursive CTE are:
- It is defined by using the WITH RECURSIVE clause.
- A recursive CTE must contain a terminating condition.
- We will use the recursive CTE for series generation and traversal of hierarchical or tree-structured data.
MySQL Recursive CTE Syntax
The following is the basic syntax of recursive CTE in MySQL:
WITH RECURSIVE cte_name (column_names) AS ( subquery )
SELECT * FROM cte_name; Here, the subquery is a MySQL query refer itself by using the cte_name as its own name.
MySQL CTE Examples
Let us understand how CTE works in MySQL using various examples. Here, we are going to use a table “employees” for a demonstration. Suppose this table contains the following data:
Execute the following statement to understand the concept of CTE. In this example, the CTE name is employee_in_california, the subquery that defines the CTE returns the three columns emp_name, emp_age, and city. Hence, the CTE employee_in_california will return all employees who are located in the California city.
After defining the CTE employee_in_california, we have referenced it in the SELECT statement for selecting only those employees who are located in California.
WITH employees_in_california AS (
SELECT * FROM employees WHERE city = 'California'
)
SELECT emp_name, emp_age, city FROM employees_in_california
WHERE emp_age >= 32 ORDER BY emp_name; After executing the above statement, it will give the following output. Here, we can see that the result returns only the employee data that are located in California.
The more advanced MySQL CTE example
Suppose we have a table named customer and order that contains the following data:
Table: customer
Table: orders
See the below statement that explains advanced CTE example using the INNER JOIN clause.
WITH total_customer_2020 AS (
SELECT cust_id, name, occupation FROM customer
INNER JOIN orders USING (cust_id)
ORDER BY age
)
SELECT * FROM orders JOIN total_customer_2020 USING (cust_id); After executing, we will get the output as below:
MySQL Recursive CTE Example
The following examples explain the working of the recursive CTE. Consider the below statement which generates a series of first five odd numbers:
WITH RECURSIVE
odd_num_cte (id, n) AS
(
SELECT 1, 1
union all
SELECT id+1, n+2 from odd_num_cte where id < 5
)
SELECT * FROM odd_num_cte; After executing the above statement, it will give the output as below:
The above statement consists of two parts one is non-recursive, and another is recursive.
Non-recursive: SELECT 1, 1
This part will produce the initial rows with two columns as “id” and “n” and a single row.
Recursive: SELECT id+1, n+2 from odd_num_cte where id < 5
This part is responsible for adding rows to the previous output until the terminating condition (id < 5) will not be satisfied. When the id reached 5, the condition becomes false, and the recursion process is terminated.
The WITH clause uses
MySQL provides many contexts to use the WITH clause for creating CTE. Let’s discuss one by one in detail.
First, we can use the WITH clause at the beginning of SELECT, UPDATE, and DELETE query as below.
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ... Second, we can use the WITH clause at the beginning of a subquery or a derived table subquery as below:
SELECT ... WHERE id IN (WITH ... SELECT ...);
SELECT * FROM (WITH ... SELECT ...) AS derived_table; Third, we can use the WITH clause immediately preceding of SELECT statements that include a SELECT clause as below:
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ... Benefits of using CTE
- It provides better readability of the query.
- It increases the performance of the query.
- The CTE allows us to use it as an alternative to the VIEW concept
- It can also be used as chaining of CTE for simplifying the query.
- It can also be used to implement recursive queries easily.
Leave a Reply