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_query- [ ]optional part
- ...can be repeated
- RECURSIVE
- Allows the table expression to refer to itselfKeywordOptional
- name_cte
- Name of the table expressionName
- column
- Column names of the table expression, taken from subquery by defaultNameOptional
- subquery
- Subquery whose result is available by the name name_cteSubquery
- main_query
- Main query that refers to name_cte like a tableSubquery
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.