Why is SQL known as a relation-based / functional language?

Question

We're learning that most languages are classified as either of the two, "relation based" or "high level". I've never used SQL before, but from reading its syntax it seems more like the imperative/high-level syntax than functional/relation-based (Lisp, Haskell)??

Or it could just be that my interpretation of my professor's lecture notes is wrong... but it definitely lists SQL as one of the relation-based languages (as opposed to high level), and it equates relation-based with functional... or perhaps it's that I don't understand why the fact that SQL deals with relational databases makes a functional language the way it should be implemented? (and why 'relation-based' equates with 'functional' when categorizing programming languages?)

Thanks :)

Answer 1

We're learning that most languages are classified as either of the two, "relation based" or "high level".

Those concepts are orthogonal. "Relation-based" means that the language's semantics are based on the concept of a relation, that is, a many-to-many association between two sets (relations are the mathematical foundation behind SQL tables). "High-level" means that the language contains a lot of abstractions that hide much of the underlying technical details (such as memory locations, CPU registers, disk access, bitwise operations, etc.). SQL is certainly relation based, as its main purpose is to describe relational data and operations upon it. SQL is also fairly high level; it does not provide any means to access bytes on disk directly, and it doesn't tell you any details about how it stores its data (at least standard SQL doesn't; most vendors provide extensions to the standard that can give you quite some information, but that's beside the point).

In fact, there are many more axes along which programming (and data) languages can be classified; a particularly interesting one is declarative vs. imperative. Declarative languages describe what something is; imperative languages describe how to do something. The DDL part of SQL is mostly declarative, despite the imperative-looking keywords ("CREATE TABLE", "DROP DATABASE", etc.), and even the data manipulation part (SELECT, UPDATE, INSERT, DELETE) is still pretty declarative. A very interesting property of SQL is that it is not Turing complete: you cannot write an unbounded loop in plain standard ANSI SQL.

Functional programming centers around a few core ideas:

• functions are first-class citizens (that is, they can be used as values, as inputs to other functions, and as output from other functions)
• higher-order functions (functions that operate on functions, or functions that return functions)
• purity (a pure function is one that has no side effects; a pure function cannot do any I/O, it cannot read nor modify any global state, and it cannot take non-const reference arguments. Pure functions are especially interesting because they will always produce the same output given the same inputs)

SQL certainly doesn't revolve around functions as the main tool for modeling things, but it does somewhat embrace the purity idea - the same query run on the same database will yield the same result, every time (except for ordering). Calling SQL a 'functional' language is a bit of a stretch though IMO.

Answer 2

SQL is not imperative because the process of HOW queries and relationships are resolved are not defined by the programmer, but rather by the compiler/optimizer/interpreter. SQL is a declarative language - In SQL, you declare relationships. This builds up a data structure (that again is not physically defined with the language but by its implementation) using inserts, updates, and deletes.

Use of the relations is then done using queries (SELECT statements), which are functional in that they do not have side effects.

The whole thing is wrapped around the relational model.

Answer 3

SQL is not really as much functional language as it is declarative. Functional languages, in general, emphasize declarative style over imperative to minimize side effects. This could lead some people to refer to SQL as functional, but it's not accurate. It's declarative with procedural elements.

Answer 4

Is it possible that your notes are scrambled?

I've never heard of programming languages as being divided between "relation based" and "high level". Low level/High level is usually used to distinguish assembler and C from languages that provide direct support for more abstract structures. Relations are a pretty abstract structure, so I'd say anything that supports relations is high-level by definition.

Pure SQL is usually describe as being a declaritive language, with some procedural bits tacked on by the various vendors. The fact that SQL doesn't support functions as variables seems to me to immediately disqualify it from being a functional language.

Answer 5

SQL is a relational, set-based language that has had procedural functionality tacked on.

I don't know if I would consider SQL functional, however it does have some aspects of functional languages. Modern variants of SQL (with the procedural bits) are definitely not functional.

Answer 6

I think, SQL is a syntactic sugar around relational algebra + something more. Relational algebra has a lot of power of functional languages, it indeed leverages functions of very high expressing power (selection, projection, renaming, join, union, intersection...). But s far as I know, basic treatment of relational algebra usually has no equivalent of lambda operator, although it can be extended with a recursion operator in a seamless way.

I think relation algebra is rather an algebraic language. SQL, with its subqueries, has moved from pure relational algebra towards a more functional style, but without a lambda operator, I think it is not a full functional language. I no not know whether it could be extended to a full functional language in a seamless way, I am not expert in this field. Haskell has some libraries with the goal of very high-level database languages.

Answer 7

I don't know all the subtilities of what it needs for a language to be qualified as functional but Sql Server introduced a very interresting way to work with functions. A special clause makes functions able to interact together in a query. It is called Apply. When I eexplainwd that to a former APL programmer he told me that a similar clause existed in APL for a similar goal. Apply clause allows to pass set of attributes from a table's row or table function's row as input to another function. That being said, I imposed a restriction on the type of table function to write to be considered functional. The must be declared as inline, which means as being expressed as a single select statement. This impose having no variables. Such queries with a lot of logic can be written provide you use common table expressions which then allow to turn expressions into column, a kind of unmutable variable which can be reused in other CTE. In the end the.function becomes a very large macro which makes the optimizer free to optimize the way it needs to.The only thing people lacks is some simple tricks to write conditional logic and declared some data supportinglogic in the query. Last some functions using the over clause are needed as a way ro carry on results as a value usable in a row from other rows but it would be a bit long toelaborate here.