EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL CTE
 

MySQL CTE

Priya Pedamkar
Article byPriya Pedamkar

Updated May 12, 2023

MySQL-CTE

 

 

Definition of MySQL CTE

CTE stands for Common Table Expressions. In MySQL, it is a temporary result set that has a range within a single SQL statement. A CTE can be referenced in any place within that statement multiple times. Generally, the ‘WITH’ clause is used to define CTE. The common table expressions do not exist as a separate statements. Instead, you must always use it along with SELECT, INSERT, UPDATE, or DELETE statements, which immediately follow the CTE. A CTE is similar in function and scope to a derived table in SQL.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Syntax of MySQL CTE

As mentioned earlier, users can use a Common Table Expression in SELECT, INSERT, UPDATE, or DELETE statements. The simple syntax of defining CTE will be:

with cte_name (column_names) as (
query )

Here the cte_name is the name of the common table expression. The cte_name is followed by the columns to be selected. Further, the condition to select the columns is defined within the braces. So, now we will try to put this in our table and discuss further details of common table expressions.

We will be considering the Country table from the sample database. We have 239 countries listed in this table. The below-given screenshot is a part of the table. Let’s try to write a query with CTE from this big table for easy access to a defined temporary data set.

select * from country order by Continent;

MySQL CTE-1

How does CTE work in MySQL?

Let’s first look at one simple SELECT query with the CTE defined in it. Our scenario is to identify countries belonging to the Middle East region from the Asian continent. So, we must consider two conditions: firstly, Continent = ‘Asia’ and then Region = ‘Middle East’. For this scenario, the query will be as below.

Query:

WITH Asian_countries as (
SELECT *
FROM country
WHERE Continent = 'Asia' )
SELECT *
FROM Asian_countries
WHERE Region = 'Middle East'
ORDER by Name;

In the first part of the query, users define the CTE. The CTE name for this query is Asian_countries, which holds the list of countries from the original table COUNTRY with the condition as Continent =’ Asia’. And in the second part of the query, we are picking up the details of countries with Region = ‘Middle East’, in the CTE Asian_countries.

Output:

MySQL CTE-2

Suppose we have the scenario to select specific columns from this example. In the above query, we set all columns in the table.

Query:

WITH Asian_countries as (
SELECT Code, Name, Continent , Region
FROM country
WHERE Continent = 'Asia' )
SELECT Code, Name
FROM Asian_countries
WHERE Region = 'Middle East'
ORDER by Name;

Here the CTE is Asian_countries. You should create the temporary table or common table expression to hold data from the table COUNTRY with the condition Continent = ‘Asia’. Such details as country code, country name, region, and the continent will be stored in the common table expression Asian_countries for those countries with Asia. The common expression “Asian_countries” will store a continent from the “COUNTRY” table.

In the second part of the query, users are instructed to select the country_code and country_name of countries with a Region value of ‘Middle East’ from the Common Table Expression, sort them in ascending order based on the country_name, and display the result. The expected output is a list with columns for Code and Name, arranged in alphabetical order based on the Name column. The list should include only countries from the Asian continent and the Middle East region.

Output:

Output-3

There are specific contexts to which the use of the WITH clause is restricted. Below are the scenarios:

  • Users typically use a clause at the beginning of SELECT, UPDATE, and DELETE statements.
WITH … SELECT …
WITH … UPDATE …
WITH … DELETE …
  • In statements that include a SELECT statement preceding the SELECT statement.
INSERT … WITH … SELECT …
REPLACE … WITH … SELECT …
CREATE TABLE …WITH … SELECT …
  • Users cannot use the WITH statement multiple times at the same level. Instead, they should use a single WITH clause and add multiple CTEs, separated by a comma.

Incorrect usage:

WITH cte_name1 as (….), WITH cte_name2 as (….) SELECT ….

Correct usage:

WITH cte_name1 as (….), cte_name2 as (….) SELECT …

Users should note that they can reference the same CTE within another CTE or by itself. When a CTE refers to itself, it becomes a recursive CTE. Let’s try to understand a simple recursive CTE with the below example:

Query:

WITH RECURSIVE trial (count) AS
(
SELECT 100
UNION ALL
SELECT count + 50 FROM trial WHERE count < 500
)
SELECT * FROM trial;

The CTE name in this query is ‘trial’. And we have three parts in the CTE sub-query as follows.

  • SELECT … : this part initializes the first row and is a non-recursive part of the query.
  • UNION ALL: This part separates the non-recursive and recursive parts of the sub-query. It can be either UNION ALL or UNION DISTINCT.
  • SELECT …: This part displays the further rows and is the recursive part of the sub-query. The example has this part as -SELECT count + 50 FROM trial WHERE count < 500- where the value ‘count’ is to be updated by 50 until it reaches the value 500.

Let’s have a look at the output of this query.

Output:

Output-4

We can see that the output has a single column ‘count’ with values from 100 to 500, each updated by 50. We need to remember that both the recursive and non-recursive portions of the sub-query must have the same column widths for all columns because the output column width is dependent upon the non-recursive part of the sub-query. People mainly use Recursive CTEs to initiate series like the Fibonacci series, search, or traverse through any data.

Conclusion

MySQL added Common Table Expressions or CTEs to reduce the complexity of SQL queries. CTEs generate a temporary result set that users can access within the same SQL query as a reference or to act upon. The WITH clause mainly defines CTEs, and users can define multiple CTEs in the same WITH clause.

Recommended Articles

We hope that this EDUCBA information on “MySQL CTE” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MySQL IN Operator
  2. Unique Key in MySQL
  3. MySQL Subquery
  4. MySQL Constraints

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW