Procedural and non-procedural query language difference

Question

Going through the relational algebra, I encountered the term "procedural query language". So what is the difference between a procedural query language and a non-procedural query language?

Answer 1

In a procedural query language, like Relational Algebra, you write a query as an expression consisting of relations and Algebra Opertors, like join, cross product, projection, restriction, etc. Like in an arithmetical expression (e.g. 2 / (3 + 4)), the operators have an order (in the example, the addition is performed before the division). So for instance you join the results of two different projection, and then perform a restriction, etc. A language like this is called procedural since each expression establish a certain order of performing its operators.

On the contrary, query languages like Relational Calculus, and the well knwon SQL query language are called “non procedural” since they express the expected result only through its properties, and not the order of the operators to be performed to produce it. For instance, with an SQL expression like:

SELECT t1.b
FROM t1
WHERE t1.b > 10

we specify the we want all the tuples of relation t1 for which t1.b > 10 is true, and from these we want the value of t1.b, but we do not specify if first the projection must be performed, and then the restriction, or the restriction first and then the projection. Imagine a complex SQL query, with many joins, conditions, restrictions, etc. Many different orders of executing the query could be devised (and in effect the task of the query optimizer is that of devising an efficient order to perform these operations, so to trasform this declarative query into a procedural one).

Answer 2

There is a myth that relational algebra notations are procedural and relational calculus notations are not. But every relation expression corresponds to a certain calculus expression with the same tree structure. So it cannot be procedural when calculus is not. You can implement/execute a query in either notation per its expression tree--or not.

A (query) language is procedural when it has to use looping or otherwise relies on state. The alternative is often called declarative or functional.

Any database notation that updates the database is procedural, including SQL. But that's not "querying". Typically DBMSs have extensions to SQL that allow you to partially control query execution and/or data storage order in terms of implementations concepts; that's non-procedural. But that's not SQL.