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:
- 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.
- 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.
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.
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.