In this tutorial, we will see some widely asked and most important interview questions on the MySQL CTE topic. If you are applying for the SQL developer role, then it is very necessary to be prepared with the CTE concept because it plays an important role along with JOINs and stored programs in MySQL. If you want to read an in-detail guide about MySQL CTE, then here it is.
Let’s get started with the questions and their answers.
Also check: MySQL Window Functions Interview Questions
1. What is CTE? In which version of MySQL, was it added?
In SQL, CTE stands for common table expression. It allows you to write a query that brings the temporary result set which has an existence within the execution scope of that SQL statement, i.e., INSERT, UPDATE, SELECT or DELETE. It is somewhat like a derived table but both are different on some points.
Common Table Expressions are added in MySQL version 8.0.
2. How is CTE different from the derived table?
Common table expressions can be self-referencing, i.e., a particular CTE can call itself multiple times, unlike the derived tables. In the derived table, we cant self reference it.
3. How is CTE different from the subquery?
Similar to the derived tables, the subquery cannot be used recursively. That means you can not reference the subquery to itself. Whereas, CTEs can be recursive.
4. Can we use multiple CTEs in a single query in MySQL?
Yes. We can write multiple common table expressions in a single query. However, we must write the WITH clause only once at the top of the query. Also, if any of the CTEs is recursive then you must write a RECURSIVE keyword only once.
Note that, a statement can contain multiple WITH clauses, however, they must be at different levels as per the official documentation.
5. What is a recursive CTE?
A CTE is called to be recursive when it returns the result subset and references itself to find the next result set. When there is no result remaining, it stops the execution. The recursive CTE starts with the WITH RECURSIVE clause. For example-
WITH RECURSIVE cte_name AS (
cte_definition (the anchor member)
UNION ALL
cte_definition (the recursive member)
)
SELECT *
FROM cte_name;Code language: SQL (Structured Query Language) (sql)Here, the above query is defined as recursive with the keyword RECURSIVE. Then the query finds the result until the last row is found. Finally, when there is no row available in the table, it stops the execution and the SELECT statement will execute.
6. Are duplicate column names allowed in the CTE?
No. Duplicate column names are not allowed in common expression tables in MySQL. When you try to specify the same column name twice, you will get an error. Let’s try it.
First, let’s run the CTE without specifying duplicate column names.
WITH CTE1 AS (SELECT * FROM departments)
SELECT * FROM CTE1;Code language: SQL (Structured Query Language) (sql)
Now let’s specify the ‘name’ column twice.
WITH CTE1 AS (SELECT name,name FROM departments)
SELECT * FROM CTE1;Code language: SQL (Structured Query Language) (sql)
As you can see, we got an error “duplicate column name ‘name'”.
7. Can you DELETE the records from the CTE
No. In MySQL, you can not update or delete the data from the table using CTE. However, this feature is available in other database management systems such as SQL servers, MariaDB, etc.
8. Does CTE use TempDB internally?
A CTE is only a language construct and is not reliant on the way a query is executed. As a nice derived table or subquery, that is what it is.
This indicates that all CTEs may be written inline, with the exception of recursive CTEs (see later). The only purpose for using the CTE code is readability. If you use the CTE more than twice, it becomes defensive since you don’t want to make a mistake that would change the derived table each time.
A CTE will be executed twice or more if it is utilized more than once. It won’t be run only once and stored in the TempDB cache.
Just as if the code were inline, it may or might not.
Conclusion
In this tutorial, we have seen some of the important questions on the MySQL common table expression. Note that, common table expressions work differently for different database management systems. So, it is very important to understand the concept of CTE in MySQL.