Equivalent of recursive CTEs

Let’s consider a simple query generating a Fibonacci sequence (in a Fibonacci sequence, every number is the sum of the two preceding ones, and the first two numbers are 1 and 1):

SELECT IF(X=1, Fn_1, Fn_2) F
FROM(
  SELECT @I := @I + @J Fn_1, @J := @I + @J Fn_2
  FROM
    (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)a,
    (SELECT 0 dummy UNION ALL SELECT 0 UNION ALL SELECT 0)b,
    (SELECT @I := 1, @J := 1)IJ
)T,
  /*A fake table ensuring single-column output of the sequence*/
  (SELECT 1 X UNION ALL SELECT 2)X;

This query generates 18 Fibonacci numbers (excluding the first two):

Now, let’s analyze how it works.

Lines 5) and 6) generate 9 rows. So far, there is nothing extraordinary here.

In line 7), two variables @I, @J are declared and assigned the value 1.

The third line does the following: initially, the variable @I is assigned the sum of values of two variables. Then, the same expression is assigned to the variable @J – but with the changed value of @I.

In other words, values in the SELECT statement are calculated from left to right – also see the note at the beginning of this article.

Take into account that the variables are assigned new values for each of our 9 data records, that is, when processing of a new row begins @I and @J contain values calculated for the preceding row.

To fulfill this task in other DBMSs, we’d have to resort to a recursive query!

As mentioned before, the order the records are processed in by the SELECT statement depends on custom sorting. Here is a simple illustration of how custom row numbering is done:

SELECT val, @I:=@I+1 Num
FROM
  (SELECT 30 val UNION ALL SELECT 20 UNION ALL SELECT 10 UNION ALL SELECT 50)a,
  (SELECT @I := 0)I
ORDER BY val;