4

Is there some historical or practical reason why SQL (or more specifically T-SQL in my case) does not support the closure property in many areas where many other language families like C do?

(That is, the closure property as seen in math, where a real number time a real number is always a real number; nothing to do with closures in functional languages.)

Examples:

  • Result sets from stored procedures cannot be used in JOINs, but the are in the same table format as actual tables and views.
  • The grammar constrains users to SELECT-FROM-WHERE ordering, and additional ordering rules for GROUP BY, HAVING, etc., even though most of those operations can be performed in any order in a logically consistent manner (such as with LINQ in .NET).
  • Boolean expressions cannot be used interchangably with BIT values. So a statement like SELECT NOT x.IsSomething cannot be used and instead a long-winded CASE expression needs to be used.
  • 1
    The first and third bullets are quirks of the implementation. For the second, if the optimizer can pick an order that does a better job and still gets the right answer, why do you care? – Blrfl Jun 16 '16 at 16:29
  • I would ask the converse. If the optimizer can juggle operators around for efficiency gains, why does it need operators written in a fixed order? Allowing operations in any order can make source code closer resemble human language. – JamesFaix Jun 16 '16 at 17:29
  • 1
    What's the benefit? As a developer, I don't see SQL's syntax as any more constraining than having to put the initializer, condition, and inter-iteration expressions in the right order for a for loop in C-derived languages. There have been scores of attempts to build natural-language query systems in the 42 years SQL has been around, and to date none of them have provided a compelling reason for SQL to go away. – Blrfl Jun 16 '16 at 18:52
  • For loop clauses are typically less than 20 characters, are each independent expressions, and there is no compelling reason to create nested or chained expressions there. And that is one of the few places that C languages have arbitrary ordering of expressions, most of the time they are in execution order, which is completely up to the developer. SQL's order is like requiring math to be written with all addition clauses first, then multiplication, then some other operator...inside the same expression. – JamesFaix Jun 16 '16 at 21:43
  • This has become too much of a discussion to continue in comments, so I've written an answer. – Blrfl Jun 17 '16 at 13:58
6

1)

Sql does support relational closure, which means you can use base tables, table literals, views, subqueries, CTE's, table variables and table-valued functions interchangeably in queries, and nest them arbitrarily.

You can also use the resultset from a stored procedure in a query, as long as you execute the query and loads the result into a temp table or table variable beforehand. You just cant include the execution of a stored procedure as part of a query, for two reasons:

  1. A stored procedure may return none or multiple resultsets. It is not known at compile time what resultsets will be returned, so it is not possible to generate a query plan.
  2. Stored procedures may have side effects. This cannot logically be supported as part of a query. I mean, the procedure could drop the table it is joined to, or it could issue an external command to shut down the database.

A multi-statment table-valued function supports some of the same complex logic as stored procedures (variables, conditionals etc), but with the constraints that it cannot have side effects, and the schema of the result set is statically defined. For this reason table-valued functions can be used as subqueries, in contrast to stored procedures.

2)

You can use projection, filters and grouping in arbitrary order, if you nest subqueries. Having is just syntax sugar for filtering a subquery with a grouping. But the syntax with nested subqueries is admittedly more cumbersome. This is probably because SQL was designed for non-programmers, so a straightforward "english like" syntax was preferred to a more "programmer friendly" syntax.

Order by may only occur as the last clause of a query, but this actually is consistent with relational closure. Since a relation is by definition unordered, you cannot preserve order in a relations operation. So ordering has to be the last operation on the resultset. (The top clause is conceptually after order by though, even though it is first in the syntax. Since it takes order into consideration, it is not really a relational operator either)

In contrast, Linq is more flexibile because it allows ordering operations arbitrarily intermingled with projection, filtering and grouping. But this is because Linq is not relational. It operates on ordered sequences. Ordering is closed over ordered sequences, but not over relations.

In short: SQL does support relational algebra and closure, the syntax is just somewhat clunky by modern standards.

3)

SQL has three-valued logic, so a boolean is true, false or unknown. So it cannot be mapped directly to a bit-type, although it could theoretically be mapped to a nullable bit, with the unknown value mapping to null.

But there is a deeper issue. In tsql scalar expressions and boolean expressions are allowed at different positions in the syntax and are not interchangeable. So there is not way to get a boolean result as a value anyway!

The SQL standard did not initially support booleans as a first-class type and boolean expressions as interchangable with scalar expressions, but support was introduced in SQL:1999. SQL Server still does not support this though, but it is not a fundamental limitation of SQL anymore.

  • 1
    Stored procedures can return n tables, but functions can return m x n values, and you can write WHERE table.column = (SELECT * FROM function()). It's just a runtime error if it returns more than one value. The side effects could be a bigger issue though. – Andrew Jun 16 '16 at 17:52
  • @AndrewPiliser: A (table-valued) function returns a single table which is exactly the same a view, table or subquery, which is why they are interchangeable. Stored procedures are different. – JacquesB Jun 16 '16 at 18:03
  • I just don't buy the "human readable" pitch on SQL. Sure I'm biased, but humans tend to construct sentences with lots of different structures, not just Subject-Verb-Object (not that that's a direct analog to SELECT-FROM-WHERE). – JamesFaix Jun 16 '16 at 21:45
  • 2
    @JamesFaix remember, SQL was developed around 40 years ago at a time when COBOL and FORTRAN were still popular (even if declining). While it has received updates in the form of new ANSI standards and vendor extensions, it is still the same basic language, based on relational algebra, and designed for non-programmers to be able use its major functions (define tables, manage data). – user22815 Jun 17 '16 at 14:08
4

Is there some historical or practical reason why SQL (or more specifically T-SQL in my case) does not support the closure property in many areas where many other language families like C do?

The historical reason is that Codd first defined the concept in 1970 and it wasn't turned into what we would now call SQL until 1974. Many of the things that would make doing it in a mathematically-closable style reasonable to do in actual software were being described for the first time in academic papers at about the same time. In other words, people weren't thinking that way. C hadn't appeared yet, and the fact that things could work out that way in other languages had a lot to do with the fact that you didn't filter, sort or group the results of arithmetic espressions.

The practical reason is that SQL could be implemented in a way to get the job done without taxing the computing resources available during that era. As I said in a comment, SQL is still powerful enough to get the job done that most developers don't see the syntax as an impediment.

About your specific complaints:

Result sets from stored procedures cannot be used in JOINs, but the are in the same table format as actual tables and views.

ISO-standard SQL has an optional part (ISO 9075-4) that defines stored procedures which I don't think anyone implements to the letter. That makes stored procedures and the ability to treat function results as tables an extension subject to the limitations of the implementation. There are other databases that can handle this without batting an eyelash.

Boolean expressions cannot be used interchangably with BIT values.

Again, this is a limitation of the implementation. There is no BIT type in ISO SQL as Transact-SQL defines it. T-SQL's BIT is an integer type that can represent the values 0 and 1; ISO SQL has BIT(n) which represents an array of bits of length n. ISO SQL-99 introduced BOOLEAN, and I don't think Microsoft ever implemented it. T-SQL has the notion of a Boolean value allowed as part of the query grammar in very specific places, but it is not a first-class, storable type. That's entirely Microsoft's doing.

...So a statement like SELECT NOT x.IsSomething cannot be used and instead a long-winded CASE expression needs to be used.

NOT (x.IsSomething = 1) would be perfectly valid equivalent to NOT x.IsSomething. This is a side effect of T-SQL's BIT type being a very short integer and SQL Server's inability to cast an integer into a Boolean value based on its zero-ness or non-zero-ness. PostgreSQL, for example, takes care of it automagically, but Oracle doesn't and makes you state your intentions by using a comparison operator. Both approaches have their merits and problems. I'd have to go dig through the standard to see if ISO favors one behavior over the other.

The grammar constrains users to SELECT-FROM-WHERE ordering, and additional ordering rules for GROUP BY, HAVING, etc., even though most of those operations can be performed in any order in a logically consistent manner (such as with LINQ in .NET).

I'm not particularly familiar with LINQ, but a cursory look at the API documentation for providers seems to reveal that internally, it deals with all of those things in groups much the same way that SQL does. The fact that you can compose a query in any order you like is just a nice veneer, and there are libraries out there that can assemble a SQL query the same way.

There are, as JacquesB points out in his answer, ways to do the kinds of things you're after in SQL. Whether they meet your standard for design purity is up to you; the practical reality remains that for most people, SQL gets the job done.

  • 1
    You bring up a critical point: SQL is absolutely ancient in origin by the standards of our profession. Many of the features we take for granted in modern languages were the domain of Doctoral-level research back in the 1970s, if not later, far from being available in mainstream languages and compilers/interpreters. Back then, simply parsing a SQL query was difficult enough, let alone supporting the features the question brings up. Barring a total redesign of an extremely widespread language, some things are simply not feasible. – user22815 Jun 17 '16 at 14:11
  • The trick with Linq is that projection is implicit, ie. each operation is implicitly select * from... if no explicit projection is specified. That way you can chain projection, filtering and grouping arbitrarily since they are transformed into nested subqueries. You can do the same in sql by nesting subqueries, the sql syntax is just more clunky because you need explicit select * from... in each subquery. – JacquesB Jun 18 '16 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.