Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
1.1.
Syntax Of MySQL CTE
1.2.
MySQL Recursive CTE 
1.2.1.
Syntax Of Recursive CTE
1.3.
MySQL CTE Example
1.4.
MySQL Recursive CTE Example
2.
CTE From Multiple Tables
3.
Frequently Asked Questions
4.
Key Takeaways
Last Updated: Jun 28, 2024
Easy

MYSQL CTE

Author Divyansh Jain
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

In MySQL, every statement or query generates a temporary result or relation. A CTE, or common table expression, is used to identify the temporary results set that occurs within the execution scope of a specific statement, such as CREATE, INSERT, SELECT, UPDATE, DELETE, and so on.

Some of the important points about CTE are as follows:

  • The WITH clause is used to define it.
  • The WITH clause enables us to specify several CTEs in a single query.
  • A CTE can refer to other CTEs in the same WITH clause, but those CTEs must be defined first.
  • CTE's execution scope is limited to the statement in which it is utilized.

Syntax Of MySQL CTE

A CTE's structure consists of the name, an optional column list, and a query that specifies the CTE. Once the CTE has been defined, it can be used as a view in a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW query.

The basic syntax of a CTE is illustrated below:

WITH name_of_cte (column_list) AS(
        query 
)
SELECT * FROM name_of_cte; 

It is important to note that the number of columns in the query must match the number of columns in the column_list. If the column list is not specified, the CTE will utilize the column_list of the query that defines the CTE.

MySQL Recursive CTE 

A CTE, as opposed to a derived table, is a subquery that can be self-referencing by using its own name. It is sometimes referred to as recursive CTE and can be used numerous times in the same query.

Some key points about the recursive CTE are as follows:

  • The WITH RECURSIVE clause is used to define it.
  • A terminating condition must be present in a recursive CTE.
  • The recursive CTE will be used for series generation and traversal of hierarchical or tree-structured data.

Syntax Of Recursive CTE

The basic syntax of recursive CTE in MySQL is as follows:

WITH RECURSIVE name_of_cte (name_of_column)AS(
        Query(anchor member) 
        UNION ALL
        Query(recursive member) 
)
SELECT * FROM name_of_cte; 

 

MySQL CTE Example

Now assumes we need to create a table in the database of an employee's salary - 

Create table salary_table(
ID INT, 
NAME CHAR(20), 
SALARY INT
);

We will have to feed the data in the table using the following command-

INSERT INTO salary_table values
(51,’Ram’,50000),
(52,’ Shyam’,60000)

Use of CTE has been shown below-

WITH 
table1 AS (SELECT ID, NAME, SALARY FROM salary_table)
SELECT * FROM table1;

So this is how we can create a CTE table temporarily for use. 

 

MySQL Recursive CTE Example

Now the following query demonstrates the usage of the Recursive WITH statement-

WITH RECURSIVE tab AS(
SELECT SALARY FROM salary_table
UNION ALL
SELECT SALARY FROM salary_table WHERE SALARY > 55000
)
SELECT * FROM tab;

You must use the UNION or UNION ALL command to connect the anchor and recursive members. The recursive member, as the name implies, is the recursive part of CTE that will refer to the CTE itself.

In the above code, In the recursive member, we are selecting the column – Salary from the table salary_table where the column Salary is greater than 55000. 

So this is how we can use recursive CTE in MySQL. 

CTE From Multiple Tables

You can also combine many tables to create a Common Table Expression.

Example:

First of all we created a table of students. In this table, we are going to give three columns that are ID, NAME, and AGE. 

CREATE TABLE STUDENT(
ID INT NOT NULL, 
NAME CHAR(30) NOT NULL, 
AGE INT
);

Now we will feed some data into the tables. 

INSERT INTO STUDENT VALUES
(51,’ Ram”,23),
(52,’ Mayank’,45);

The result is as follows. 

Now, we will create another table and fill it as shown below-

CREATE TABLE MARKS(
ID INT NOT NULL, 
MATH INT NOT NULL, 
PHYSICS INT NOT NULL,
CHEMISTRY INT NOT NULL
);

 

INSERT INTO MARKS VALUES
(51,8,7,9),
(52,6,7,5);

From the code below we can get CTE using two tables combined. 

Input:

WITH 
table1 AS (SELECT ID, NAME FROM STUDENT),
table2 AS (SELECT MATH, PHYSICS,CHEMISTRY FROM MARKS)
SELECT * FROM table1 JOIN table2;

As a result, we will get a table(CTE) from the Command Line Client that is shown below -

IDNAMEMATHPHYSICSCHEMISTRY
52Mayank879
51Ram879
52Mayank675
51Ram675

We have Created two CTE (table1 and table2) and then we have created a CTE from these two hence this merges the CTE from two tables.

See U GIF by TikTok

Source: TikTok

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Frequently Asked Questions

  1. Is CTE more efficient than subquery?
    In theory, the performance of CTEs and subqueries should be the same because they both offer the query optimizer with the same information. One distinction is that a CTE that is utilized more than once may be easily identified and calculated only once. The results might then be saved and read repeatedly.
  2. Can we implement CTE more than once?
    A CTE, as opposed to a derived table, behaves more like an in-line view and can be accessed several times in the same query. Using a CTE simplifies the reading and maintenance of complex queries. Because a CTE can be used several times in a query, the syntax can be simplified.
  3. What exactly is Union in MySQL?
    The UNION ALL operator in MySQL is used to join the result sets of two or more SELECT operations. It returns all results from the query without removing duplicate entries from the multiple SELECT statements.
  4. Are CTEs faster than temp tables?
    CTE will perform substantially worse than temporary tables when connecting many tables with millions of rows of entries in each. This is something I've witnessed firsthand. CTEs perform noticeably slower. CTEs are also slower to execute since the results are not cached.
  5. What exactly is a temporary table in MySQL?
    In MySQL, a temporary table is a type of table that allows you to store a temporary result set that you can reuse multiple times in a single session. A temporary table is used when it is hard or expensive to query data that requires a single SELECT statement with the JOIN clauses.

Key Takeaways

Now I think you got a brief knowledge about the Mysql CTE and its usage and you can use it without any problem. Hope you learned something. But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. 

Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job. Also, check out our articles on MySql Data TypesMySql Connection, and MySql Variables.

Happy Learning Ninja :) 

Live masterclass