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