An Introduction to MySQL CTE
Summary: in this tutorial, you will learn how to use MySQL CTE or common table expression to construct complex queries in a more readable manner.
What is a common table expression or CTE?
A common table expression is a named temporary result set that exists only within the execution scope of a single SQL statement e.g.,SELECT
, INSERT
, UPDATE
, or DELETE
.
Similar to a derived table, a CTE is not stored as an object and lasts only during the execution of a query.
Unlike a derived table, a CTE can be self-referencing (a recursive CTE) or can be referenced multiple times in the same query. In addition, a CTE provides better readability and performance in comparison with a derived table.
MySQL CTE syntax
The structure of a CTE includes the name, an optional column list, and a query that defines the CTE. After the CTE is defined, you can use it as a view in a SELECT
, INSERT
, UPDATE
, DELETE
, or CREATE VIEW
statement.
The following illustrates the basic syntax of a CTE:
WITH cte_name (column_list) AS (
query
)
SELECT * FROM cte_name;
Notice that 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
Simple MySQL CTE examples
We’ll use the customers
table from the sample database for demonstration:
The following example illustrates how to use a CTE for querying data from the customers
the table in the sample database.
Note that this example is only for the demonstration purpose to make it easy for you to understand the CTE concept.
WITH customers_in_usa AS (
SELECT
customerName, state
FROM
customers
WHERE
country = 'USA'
) SELECT
customerName
FROM
customers_in_usa
WHERE
state = 'CA'
ORDER BY customerName;
In this example, the name of the CTE is customers_in_usa
, the query that defines the CTE returns two columns customerName
and state
. Hence, the customers_in_usa
CTE returns all customers located in the USA.
After defining the customers_in_usa
CTE, we referenced it in the SELECT
statement to select only customers located in California.
See the following example:
WITH topsales2003 AS (
SELECT
salesRepEmployeeNumber employeeNumber,
SUM(quantityOrdered * priceEach) sales
FROM
orders
INNER JOIN
orderdetails USING (orderNumber)
INNER JOIN
customers USING (customerNumber)
WHERE
YEAR(shippedDate) = 2003
AND status = 'Shipped'
GROUP BY salesRepEmployeeNumber
ORDER BY sales DESC
LIMIT 5
)
SELECT
employeeNumber,
firstName,
lastName,
sales
FROM
employees
JOIN
topsales2003 USING (employeeNumber);
In this example, the CTE returns the top 5 sales rep in 2003. After that, we referenced to the topsales2003
CTE to get additional information about the sales rep including first name and last name.
A more advanced MySQL CTE example
See the following example:
WITH salesrep AS (
SELECT
employeeNumber,
CONCAT(firstName, ' ', lastName) AS salesrepName
FROM
employees
WHERE
jobTitle = 'Sales Rep'
),
customer_salesrep AS (
SELECT
customerName, salesrepName
FROM
customers
INNER JOIN
salesrep ON employeeNumber = salesrepEmployeeNumber
)
SELECT
*
FROM
customer_salesrep
ORDER BY customerName;
In this example, we have two CTEs in the same query. The first CTE ( salesrep
) gets the employees whose job titles are the sales representative. The second CTE ( customer_salesrep
) references the first CTE in the INNER JOIN
clause to get the sales rep and customers of whom each sales rep is in charge.
After having the second CTE, we query data from that CTE using a simple SELECT
statement with the ORDER BY
clause.
The WITH
clause usages
There are some contexts in that you can use the WITH
clause to make common table expressions:
First, a WITH
the clause can be used at the beginning of SELECT
, UPDATE
, and DELETE
statements:
WITH ... SELECT ...
WITH ... UPDATE ...
WITH ... DELETE ...
Second, a WITH
the clause can be used at the beginning of a subquery or a derived table subquery:
SELECT ... WHERE id IN (WITH ... SELECT ...);
SELECT * FROM (WITH ... SELECT ...) AS derived_table;
Third, a WITH
the clause can be used immediately preceding SELECT
of the statements that include a SELECT
clause:
CREATE TABLE ... WITH ... SELECT ...
CREATE VIEW ... WITH ... SELECT ...
INSERT ... WITH ... SELECT ...
REPLACE ... WITH ... SELECT ...
DECLARE CURSOR ... WITH ... SELECT ...
EXPLAIN ... WITH ... SELECT ...
In this tutorial, you have learned how to use MySQL CTE to simplify complex queries.
0 Comments
CAN FEEDBACK
Emoji