Syntax\n------\n\nWITH [RECURSIVE] table_reference [(columns_list)] AS  (\n  SELECT ...\n)\n[CYCLE cycle_column_list RESTRICT]\nSELECT ...\n\nDescription\n-----------\n\nThe WITH keyword signifies a Common Table Expression (CTE). It allows you to refer to a subquery expression many times in a query, as if having a temporary table that only exists for the duration of a query.\n\nThere are two kinds of CTEs:\n\n Non-Recursive.\n Recursive (signified by the RECURSIVE keyword).\n\nYou can use table_reference as any normal table in the external SELECT part. You can also use WITH in subqueries, as well as with EXPLAIN and SELECT.\n\nPoorly-formed recursive CTEs can in theory cause infinite loops. The max_recursive_iterations system variable limits the number of recursions.\n\nCYCLE ... RESTRICT\n\nThe CYCLE clause enables CTE cycle detection, avoiding excessive or infinite loops,\\\nMariaDB supports a relaxed, non-standard grammar.\\\nThe SQL Standard permits a CYCLE clause, as follows:\n\n``sql\nWITH RECURSIVE ... (\n  ...\n)\nCYCLE <cycle column list>\nSET <cycle mark column> TO <cycle mark value> DEFAULT <non-cycle mark value>\nUSING <path column>\n`\n\nwhere all clauses are mandatory.\\\nMariaDB does not support this, but permits a non-standard relaxed grammar, as follows:\n\n`sql\nWITH RECURSIVE ... (\n  ...\n)\nCYCLE <cycle column list> RESTRICT\n`\n\nWith the use of CYCLE ... RESTRICT it makes no difference whether the CTE uses UNION ALL or UNION DISTINCT anymore. UNION ALL means "all rows, but without cycles", which is exactly what the CYCLE clause enables. And UNION DISTINCT means all rows should be different, which, again, is what will happen — as uniqueness is enforced over a subset of columns, complete rows will automatically all be different.\n\nCYCLE ... RESTRICT` is not available.\n\nExamples\n--------\n\nWITH t AS (SELECT a FROM t1 WHERE b >= 'c') \n  SELECT * FROM t2, t WHERE t2.c = t.a;\n\nURL: https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/common-table-expressions/with