1.0. Introduction:
MySQL 8.0 was released with awesome features. One of its most prominent features is CTE (Common Table Expression).
The Common Table Expression can be used to construct complex queries in a more readable manner. In this blog, I have described how the CTE works with some useful examples.
1.1 What is CTE?
A Common Table Expression (CTE) is the result set of the query, which exists temporarily and uses only within the context of a larger query.
The CTE provides better readability and performance in comparison with a derived table.
In a normal query the temporary result set that exists only within the execution scope of a single SQL statement.
Example:
Graphical View:
When Using CTE:
- The CTE can be self-referencing or it can be referenced multiple times in the same query.
- While comparing the normal query it will give better performance.
Example :
Graphical View :
Reusability:
Just like database views and derived tables, CTE enables users to easily write and maintain complex queries with better readability and simplification. This reduction in complexity is achieved by deconstructing ordinarily complex queries into simple blocks to be used and reused if necessary.
1.2 CTE Syntax:
- Initiate a CTE using “WITH”.
- Provide a name for the result soon-to-be defined query.
- After assigning a name, follow with “AS”.
- Specify column names (optional step).
- Define the query to produce the desired result set.
- If multiple CTEs are required, initiate each subsequent expression with a comma.
- Reference the above-defined CTE(s) in a subsequent query.
Thumb Rules :
- The number of columns in the query 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 query that defines the CTE.
1.3 Use Cases:Needing to reference a derived table multiple times in a single query.
- An alternative to creating a view in the database.
- Performing the same calculation multiple times over across multiple query components.
Example:
CTE Structure :
Output :
In this example name of the CTE is country_surface, the query that defines the CTE returns two columns Region and SurfaceArea.
1.4 WITH clause usages :
There are three usages WITH clause to make common table expressions.
1) A WITH clause can be used at the beginning of SELECT, UPDATE, and DELETE statements.
Source to update other tables :
The CTE provides a simple solution to update the other tables.
Example :
2) A WITH clause can be used at the beginning of a subquery or a derived table subquery.
3) A WITH clause can be used immediately preceding SELECT of the statements that include a SELECT clause.
Example :
1.5 Limiting CTE :
- It is important for recursive CTEs that the SELECT part include a condition to terminate the recursion.
- You can force termination by placing a limit on CTE execution time.
- max_execution_time it enforces an execution timeout for SELECT statements executed within the current session.
- The default max_execution_time is 0 .The variables only apply to read-only SELECT statements.
1.6 CTE Optimizer Hints :
We can include the max_execution_time an optimizer hint within the CTE statement.
1.7 Conclusion :
- The Common Table Expressions is one of the long-awaited features in MySQL 8.
- It will reduce more complexity. The purpose of CTEs is to simplify the writing of complex SQL queries.
- You can always recognize them by the “With” keyword at the start of the SQL statement.
Featured Image Courtesy : Photo by Jeremy Thomas on Unsplash