Besides a declarative language, is SQL a functional language?

Question

SQL was designed as a declarative language, in sense that you tell what you want to get and the SQL engine decides how.

However, SQL operates on sets, and the results of the functions can be first class sets in Oracle, SQL Server and PostgreSQL.

One can say that SQL is functional language, as long as a function takes a set as its input and produces a set as its output.

That is, you can write something like this:

SELECT  *
FROM    mytable t
JOIN    myfunction(x) f
ON      f.col1 = t.col2

, or even this:

SELECT  *
FROM    mytable t
CROSS APPLY
        myfunction(t.col2) f

(in SQL Server)

or this:

SELECT  t.*, myfunction(t.col2)
FROM    mytable t

(in PostgreSQL)

This is not a part of SQL standard, though.

Just like a C++ compiler tries to find an optimal way to multiply two floats (in terms of plain algebra), SQL optimizer tries to find an optimal ways to multiply two sets (in terms of relational algebra).

In C++, you just write a * b and rely on compiler to generate an optimal assembly for this.

In SQL, you write SELECT * FROM a NATURAL JOIN b and rely on optimizer.

However, with all SQL's declared declarativity (no pun intended), most real optimizers are able to do only very basic query rewrites.

Say, no optimizer I'm aware of is able to use same efficient plan for this query:

SELECT  t1.id, t1.value, SUM(t2.value)
FROM    mytable t1
JOIN    mytable t2
ON      t2.id <= t1.id
GROUP BY
        t1.id, t1.value

and for this one:

SELECT  id, value, SUM(t1.value) OVER (ORDER BY id)
FROM    mytable

, to say nothing of more complex queries.

That's why you still need to formulate your queries so that they use an efficient plan (while still producing the same result), thus making SQL quite a bit less of a declarative language.

I recently made post in my blog on this:

• Double-thinking in SQL

No, SQL is not a functional language. The paradigm is somewhat different. Note that there are other types of declarative programming languages other than functional - the canonical example being logic programming and PROLOG.

Technically, Relational Algebra (the theoretical basis of SQL) is not actually turing complete. Although modern SQL dialects add enough procedural features so that one can implement stored procedures and are turing complete at this level, a single SQL query is not a turing complete computation. Relational Algebra has the property of godel completeness. Godel completeness implies the ability to express any computation that can be defined in terms of first order predicate calculus - basically what you would know as ordinary logical expressions.

Are functions first-class objects in SQL? hardly. So I'd say no.

I think SQL and functional languages are very different from each other. In a functional language computation is done by evaluating functions. Functions do not mutate state. All they do is compute a value from their arguments. I other words, functions do not cause side-effects. Functional languages are general purpose.

SQL is a language designed for dealing with relational database management systems. It can be viewed as a Domain Specific Language. It is designed to work on "sets" of data. It can mutate global state (i.e, the database) by using commands like UPDATE. There is no concept of functions getting evaluated to a value. As far as I understand, SQL is not even Turing complete.

Declarative and functional? That would be a spreadsheet.