Question

In recent versions of MySQL (8.0+), what's the correct syntax to declare a CTE (Common Table Expression)?

a) WITH RECURSIVE
b) USE CTE
c) DECLARE CTE
d) COMMON TABLE EXPRESSION

Answer 1

In MySQL 8.0, the declaration of a Common Table Expression (CTE) uses 'WITH RECURSIVE' for recursive CTEs and 'WITH' for non-recursive ones. A CTE begins with the WITH keyword, followed by the CTE name, the AS keyword, and the SELECT statement that defines the CTE's result set.

Step-by-step explanation:

In the MySQL 8.0+ versions, the correct syntax to declare a Common Table Expression (CTE) is WITH RECURSIVE for recursive CTEs and simply WITH for non-recursive CTEs. Recursive CTEs are used for queries that require recursion, such as hierarchical data fetching or complex computations that can be broken down into a recursive process. For non-recursive queries, you would simply use the WITH clause without the RECURSIVE keyword.

The basic structure of a CTE looks like this:

WITH CTE_name AS (
SELECT ... -- your query here
)
SELECT * FROM CTE_name;

The CTE starts with the WITH keyword, followed by a name for the CTE and the AS keyword. The SELECT statement within the parentheses defines the result set of the CTE which can then be used in the main query after the CTE declaration.