SQL CTE (Common Table Expressions)

Table of contents

1. WITH
2. WITH RECURSIVE

---

WITH

Let’s you define a temporary result set that you can reference within the main query.

WITH cte_name AS [(column1, column2)] (
  SELECT column1, column2
  FROM tbl
)
SELECT *
FROM cte_name;

The column alias list (column1, column2) is optional. If you don’t specify it, the column names are taken from CTE query SELECT clause.

There should not be a semicolon ; after the CTE query.

---

WITH RECURSIVE

WITH RECURSIVE cte_name AS (
  -- Anchor member
  SELECT column1, column2
  FROM tbl
  WHERE base_condition
  UNION [ALL]  -- Without or with duplicates
  -- Recursive member
  SELECT column1, column2
  FROM `some joins referencing cte_name`
  WHERE recursive_condition
)
SELECT *
FROM cte_name;

Notice how the recursive member in CTE references cte_name itself.