WITH
Defines a named subquery (a common table expression, CTE) that the main query can use like a table.
WITH [RECURSIVE] name_cte [(column[, ...])] AS (subquery) main_queryParameters
- RECURSIVEkeywordoptional
- Allows the table expression to refer to itself
- name_ctename
- Name of the table expression
- columnnameoptional
- Column names of the table expression, taken from subquery by default
- subquerysubquery
- Subquery whose result is available by the name name_cte
- main_querysubquery
- Main query that refers to name_cte like a table
Return value
A temporary named result set available only within this query
Examples
Number of trips per company
WITH company_trips AS (
SELECT company,
COUNT(*) AS trips
FROM Trip
GROUP BY company
)
SELECT Company.name,
company_trips.trips
FROM company_trips
JOIN Company ON Company.id = company_trips.company
ORDER BY company_trips.trips DESC;Numbers 1 to 5 with RECURSIVE
WITH RECURSIVE numbers (n) AS (
SELECT 1
UNION ALL
SELECT n + 1
FROM numbers
WHERE n < 5
)
SELECT n
FROM numbers;String grows in recursion
WITH RECURSIVE stars (n, line) AS (
SELECT 1,
CAST('*' AS CHAR(10))
UNION ALL
SELECT n + 1,
CONCAT(line, '*')
FROM stars
WHERE n < 5
)
SELECT n,
line
FROM stars;Details
You can list several table expressions after one WITH, separated by commas, and each of them can refer to the previous ones.
A recursive expression consists of an initial query and a recursive part joined with UNION ALL or UNION (which also removes duplicates), and it must have a stop condition. By default, the recursive part runs at most 1000 times (the cte_max_recursion_depth variable), after which the query fails with an error.
Column types are set by the initial query. If the recursive part makes a string longer, widen the initial value in advance: CAST('*' AS CHAR(10)), otherwise you get a «Data too long» error.