top of page

Debut to MySQL CTE

In this blog, you will find out how to use MySQL CTE or common table expression to build complex queries in a more readable manner.

MySQL initiate the public table expression characteristic or CTE in short since version 8.0 so you should have MySQL 8.0 installed on your computer in order to exercise with the statements in this blog.

What is a common table expression or CTE

A common table expression is a named impermanent result set that live only within the execution scope of a respective SQL statement e.g.,SELECT, INSERT, UPDATE, or DELETE.

Related to a derived table, a CTE is not stored as an object and last only during the executing of a query. Contrary from a traced table, a CTE can be self-referencing (a recursive CTE) or can be documented multiple times in the same query. In step-up, a CTE render better legibility and performance in comparability with a derived table.

MySQL CTE syntax

The construction of a CTE includes the name, an elective column list, and a query that okay the CTE. After the CTE is outlined, you can use it like a view in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

The next elaborate the basic syntax of a CTE:

Attention that the number of columns in the query must be the same as the number of columns in the column_list. If you exclude the column_list, the CTE will use the column list of the query that precise the CTE

Simple MySQL CTE examples

The next example exemplify how to use a CTE for enquiry data from the customers table in example database. Note that this instance is only for the presentation purpose to make it easy for you to interpret the CTE concept.

In this illustration, 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 placed in the USA.

Later defining the customers_in_usa CTE, we documented it in the SELECT statement to select only customers located in California.

See another representation:

In this illustration, the CTE returns the top 5 sales rep in 2003. After that, we documented to the topsales2003 CTE to get extra info about the sales rep consider first name and last name.

A more modern MySQL CTE example

See the following illustration:

In this illustration, we have two CTEs in the said query. The first CTE ( sales-rep) gets the employees whose job titles are the sales representative. The second CTE ( customer_salesrep ) mention 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.

WITH clause use

There are some contexts that you can use the WITH clause to make common table expressions:

First, a WITH clause can be used at the beginning of SELECT, UPDATE, and DELETE statements:

Second, a WITH clause can be used at the opening of a subquery or a derived table subquery:

Third, a WITH clause can be used directly preceding SELECT of the statements that include a SELECT clause:

In this blog, you have well-educated how to use the MySQL CTE to make complex queries.

18 views0 comments

Recent Posts

See All

What are the future prospects of Java

According to a survey conducted, nearly 63% programmers mentioned that they will likely continue to work with Java along with coding with Python, SQL and HTML/CSS. In addition, the giants in the corpo

Deleting Duplicate Rows in MySQL

In this blog, you will study several ways to delete duplicate rows in MySQL. In this blog, I have shown you how to find duplicate values in a table. Once the duplicates rows are recognized, you may ne

Upload Data to MySQL tables using mysqlimport

Uploading quite a lot of rows of data from a text, csv, and excel file into a MySQL table is a repetitive task for sysadmins and DBAs who are handling MySQL database. This blog clarifies 4 applied exa


bottom of page