Factorial in SQL

Example for versions Oracle 10g SQL, Oracle 11g SQL

Pure SQL doesn’t support loops, recursions or user-defined functions. This example shows one possible workaround which uses

  • pseudocolumn level to construct a pseudotable containing numbers 1 through 16,
  • aggregate function sum which allows to sum the elements of a set
  • and math functions exp and ln to replace multiplication (required to calculate the factorial) with addition (provided by SQL).

Note that 0! will not be present in the return, since trying to calculate ln(0) produces an exception. 

select t2.n || '! = ' || round(exp(sum(ln(t1.n))))
  from 
  ( select level n
      from dual
   connect by level <= 16) t1,
  ( select level n
      from dual
   connect by level <= 16) t2
 where t1.n<=t2.n
 group by t2.n
 order by t2.n