CTE

Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary result set that you can reference within another SELECT, INSERT, UPDATE, or DELETE statement. They are defined using the WITH clause.

See WITH keyword for more details.

Recursive CTEs

AlaSQL supports recursive CTEs, which are useful for hierarchical data or generating sequences.

Syntax:

WITH RECURSIVE cte_name (column_names) AS (
    -- Anchor member
    SELECT ...
    UNION ALL
    -- Recursive member
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

Fun Example: ASCII Christmas Tree

You can use recursive CTEs to generate patterns!

WITH RECURSIVE tree (n, line) AS (
    -- Top of the tree
    SELECT 1, '      *      '
    UNION ALL
    -- Recursive part to build the tree body
    SELECT n + 1,
           SPACE(6 - n) + REPEAT('*', 2 * n + 1) + SPACE(6 - n)
    FROM tree
    WHERE n < 6
)
SELECT line FROM tree
UNION ALL
-- The trunk
SELECT '      |      ';

Output:

      *      
     ***     
    *****    
   *******   
  *********  
 *********** 
      |      

Another Example: Generating a Sequence

WITH RECURSIVE seq (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5
)
SELECT * FROM seq;

Output:

1
2
3
4
5