Select statements can optionally include a with clause that generates and names additional tables used in computing the final result. The full syntax of a select statement, not including unions, has the following form:

with [tables] select [columns] from [names] where [condition] order by [order]
    

We have already demonstrated the allowed values for [columns] and [names]. [condition] and [order] are expressions that can be evaluated for an input row. The [tables] portion is a comma-separated list of table descriptions of the form:

[table name]([column names]) as ([select statement])
    

Any select statement can be used to describe a table within [tables].

For instance, the with clause below declares a table states containing cities and their states. The select statement computes pairs of cities within the same state.

sqlite> with
       ...>   states(city, state) as (
       ...>     select "Berkeley",  "California"    union
       ...>     select "Boston",    "Massachusetts" union
       ...>     select "Cambridge", "Massachusetts" union
       ...>     select "Chicago",   "Illinois"      union
       ...>     select "Pasadena",  "California"
       ...>   )
       ...> select a.city, b.city, a.state from states as a, states as b
       ...>        where a.state = b.state and a.city < b.city;
    Berkeley|Pasadena|California
    Boston|Cambridge|Massachusetts
    

A table defined within a with clause may have a single recursive case that defines output rows in terms of other output rows. For example, the with clause below defines a table of integers from 5 to 15, of which the odd values are selected and squared.

sqlite> with
       ...>   ints(n) as (
       ...>     select 5 union
       ...>     select n+1 from ints where n < 15
       ...>   )
       ...> select n, n*n from ints where n % 2 = 1;
    5|25
    7|49
    9|81
    11|121
    13|169
    15|225
    

Multiple tables can be defined in a with clause, separated by commas. The example below computes all Pythagorean triples from a table of integers, their squares, and the sums of pairs of squares. A Pythagorean triple consists of integers $a$ , $b$ , and $c$ such that $a^2 + b^2 = c^2$ .

sqlite> with
       ...>   ints(n) as (
       ...>     select 1 union select n+1 from ints where n < 20
       ...>   ),
       ...>   squares(x, xx) as (
       ...>     select n, n*n from ints
       ...>   ),
       ...>   sum_of_squares(a, b, sum) as (
       ...>     select a.x, b.x, a.xx + b.xx
       ...>            from squares as a, squares as b where a.x < b.x
       ...>   )
       ...> select a, b, x from squares, sum_of_squares where sum = xx;
    3|4|5
    6|8|10
    5|12|13
    9|12|15
    8|15|17
    12|16|20
    

Designing recursive queries involves ensuring that the appropriate information is available in each input row to compute a result row. To compute Fibonacci numbers, for example, the input row needs not only the current but also the previous element in order to compute the next element.

sqlite> with
       ...>   fib(previous, current) as (
       ...>     select 0, 1 union
       ...>     select current, previous+current from fib
       ...>     where current <= 100
       ...>   )
       ...> select previous from fib;
    0
    1
    1
    2
    3
    5
    8
    13
    21
    34
    55
    89
    

These examples demonstrate that recursion is a powerful means of combination, even in declarative languages.

Building strings. Two strings can be concatenated into a longer string using the || operator in SQL.

sqlite> with wall(n) as (
      ....>   select 99 union select 98 union select 97
      ....> )
      ....> select n || " bottles" from wall;
    99 bottles
    98 bottles
    97 bottles
    

This feature can be used to construct sentences by concatenating phrases. For example, one way to construct an English sentence is to concatenate a subject noun phrase, a verb, and an object noun phrase.

sqlite> create table nouns as
      ....>   select "the dog" as phrase union
      ....>   select "the cat"           union
      ....>   select "the bird";
    sqlite> select subject.phrase || " chased " || object.phrase
      ....>        from nouns as subject, nouns as object
      ....>        where subject.phrase != object.phrase;
    the bird chased the cat
    the bird chased the dog
    the cat chased the bird
    the cat chased the dog
    the dog chased the bird
    the dog chased the cat
    

As an exercise, use a recursive local table to generate sentences such as, "the dog that chased the cat that chased the bird also chased the bird."