WITH CLAUSE: COMMON TABLE EXPRESSION

A Common Table Expression (CTE) in SQL is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH keyword and provide a way to break down complex queries into simpler, more readable, and reusable components.

CODE EXAMPLE

EXPLANATION

  • cte_name is the name of the Common Table Expression.
  • (column1, column2, ...) specifies the columns in the CTE, although it’s optional in many databases.

Recursive CTE

A recursive common table expression is one having a subquery that refers to its own name. 

  • WITH clause must begin with WITH RECURSIVE if any CTE in the WITH clause refers to itself .  (If no CTE refers to itself, RECURSIVE is permitted but not required.)

If you forget RECURSIVE for a recursive CTE, this error is a likely result:

EXAMPLE

When executed, the statement produces this result, a single column containing a simple linear sequence:

Example

CTE: https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/with.html

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top