An Introduction to MySQL CTE

An Introduction to MySQL CTE

 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.

MySQL introduced the common table expression or CTE  feature in version 8.0 so you should have MySQL 8.0+ in order to practice with the statements in this tutorial.

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.,SELECTINSERTUPDATE, 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 SELECTINSERTUPDATEDELETE, 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 SELECTUPDATE, 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.

Reactions

Post a Comment

0 Comments

close