WITH clause

SQL standard defines the following syntax for WITH clause:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"
    [search_clause]
    [cycle_clause]

search_clause:
  SEARCH {DEPTH | BREATH} FIRST BY column_name_list SET identifier

cycle_clause:
  CYCLE column_name_list
    SET identifier TO expr
    DEFAULT expr USING identifier

column_name_list:
  identifier ["," ...]

No dialect fully supports the standard:

BigQuery:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier AS "(" query_expression ")"

DB2:

WITH common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

DB2i:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")" [search_clause] [cycle_clause]

search_clause:
  SEARCH {DEPTH | BREATH} FIRST BY column_name_list SET identifier

cycle_clause:
  CYCLE column_name_list
    SET identifier TO expr
    DEFAULT expr
    [USING identifier]

DuckDB:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier [ "(" column_name_list ")" ]
  [ USING KEY "(" column_name_list ")" ]
  AS [ [ NOT ] MATERIALIZED ] { query_expression | "(" query_expression ")" }

Hive:

WITH common_table_expression ["," ...]

common_table_expression:
  identifier AS "(" query_expression ")"

MariaDB supports just a single common_table_expression:

WITH [RECURSIVE] common_table_expression

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"
  [CYCLE column_name_list RESTRICT]

MySQL:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

N1QL:

WITH common_table_expression ["," ...]

common_table_expression:
  identifier AS "(" query_expression ")"

PL/SQL:

WITH [plsql_declarations] [common_table_expression ["," ...]]

plsql_declarations:
  function_declaration | procedure_declaration

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"
  [search_clause]
  [cycle_clause]

search_clause:
  SEARCH {DEPTH | BREATH} FIRST BY {column_alias ["," ...]} SET identifier

column_alias:
  identifier [ASC | DESC] [NULLS FIRST | NULLS LAST]

cycle_clause:
  CYCLE column_name_list
    SET identifier TO expr
    DEFAULT expr

PostgreSQL:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS [[NOT] MATERIALIZED] "(" query_expression ")"
    [search_clause]
    [cycle_clause]

search_clause:
  SEARCH {DEPTH | BREATH} FIRST BY column_name_list SET identifier

cycle_clause:
  CYCLE column_name_list SET identifier USING identifier

Redshift:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

SingleStoreDB:

WITH common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

Snowflake:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

Spark:

WITH common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

SQLite:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS [[NOT] MATERIALIZED] "(" query_expression ")"

TiDB:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

Transact-SQL:

WITH common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"

Trino:

WITH [RECURSIVE] common_table_expression ["," ...]

common_table_expression:
  identifier ["(" column_name_list ")"] AS "(" query_expression ")"