Common Table Expressions (WITH) in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Common Table Expression (CTE) in SQL?

A Common Table Expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It helps organize complex queries by breaking them into simpler parts.

beginner

How do you start a CTE in SQL?

You start a CTE with the keyword WITH, followed by the CTE name, optional column names, and the query that defines it inside parentheses.

intermediate

Can a CTE refer to itself in MySQL?

Yes, in MySQL 8.0 and later, CTEs can be recursive, meaning they can refer to themselves to perform repeated operations like traversing hierarchical data.

intermediate

Why use a CTE instead of a subquery?

CTEs improve readability by naming parts of a query, making complex queries easier to understand and maintain. They can also be referenced multiple times within the main query.

beginner

Write a simple example of a CTE that selects all employees with salary over 50000.

WITH HighEarners AS (SELECT * FROM employees WHERE salary > 50000) SELECT * FROM HighEarners;

What keyword starts a Common Table Expression in SQL?

A WITH

B SELECT

C FROM

D JOIN

The keyword WITH is used to start a CTE.

Which of the following is a benefit of using CTEs?

A They speed up database backups.

B They permanently store data.

C They replace indexes.

D They improve query readability.

CTEs help make complex queries easier to read and maintain.

Can a CTE be recursive in MySQL 8.0+?

A Only if you use a stored procedure.

B Yes, it can refer to itself.

C Only in MySQL 5.x versions.

D No, recursion is not allowed.

MySQL 8.0 and later support recursive CTEs.

How do you reference a CTE inside the main query?

A By its name defined after WITH.

B By using the keyword SUBQUERY.

C By using the keyword TEMP.

D By using the table name only.

You use the CTE name defined after WITH to reference it.

Which statement is true about CTEs?

A CTEs permanently store data in the database.

B CTEs replace the need for indexes.

C CTEs can be used multiple times in the same query.

D CTEs are only used for deleting data.

CTEs are temporary and can be referenced multiple times within the query.

Explain what a Common Table Expression (CTE) is and why it is useful in SQL queries.

Think about how breaking a big task into smaller parts helps.

Describe the syntax of a CTE and how you use it in a SELECT statement.

Start with WITH, then name, then the query, then use the name in your main query.