WITH (Common Table Expressions) ( SELECT 1 AS n, 1 AS p, -1 AS q UNION ALL SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5 ) SELECT * FROM cte; Because  p  in one row is derived from  q  in the previous row, and vice versa, the positive and negative values swap positions in each successive row of the output: Some syntax constraints apply within recursive CTE subqueries: Prior to MySQL 8.0.19, the recursive  SELECT  part of a recursive CTE also could not use a  LIMIT clause. This restriction is lifted in MySQL 8.0.19, and  LIMIT  is now supported in such cases, along with an optional  OFFSET  clause. The effect on the result set is the same as when using  LIMIT  in the outermost  SELECT , but is also more efficient, since using it with the recursive  SELECT  stops the generation of rows as soon as the requested number of them has been produced. These constraints do not apply to the nonrecursive  SELECT  part of a recursive CTE. The prohibition on DISTINCT  applies only to  UNION  members;  UNION DISTINCT  is permitted. These constraints come from the SQL standard, other than the MySQL-specific exclusions of  ORDER BY , LIMIT  (MySQL 8.0.18 and earlier), and  DISTINCT . For recursive CTEs,  EXPLAIN  output rows for recursive  SELECT  parts display  Recursive  in the  Extra column. Cost estimates displayed by  EXPLAIN  represent cost per iteration, which might differ considerably from total cost. The optimizer cannot predict the number of iterations because it cannot predict at what point the WHERE  clause becomes false. CTE actual cost may also be affected by result set size. A CTE that produces many rows may require an internal temporary table large enough to be converted from in-memory to on-disk format and may 2713 •The recursive  SELECT  part must reference the CTE only once and only in its  FROM  clause, not in any subquery. It can reference tables other than the CTE and join them with the CTE. If used in a join like this, the CTE must not be on the right side of a  LEFT JOIN . • ORDER BY • DISTINCT •Window functions • GROUP BY •Aggregate functions such as  SUM() •The recursive  SELECT  part must not contain these constructs: +------+------+------+ | n | p | q | +------+------+------+ | 1 | 1 | -1 | | 2 | -2 | 2 | | 3 | 4 | -4 | | 4 | -8 | 8 | | 5 | 16 | -16 | +------+------+------+