Further: SQL is not composable. If I have a fragment of SQL in my hand, there is no reasonable way to insert that into a larger expression. You can further tell this is a problem by the way any nontrivial SQL function degenerates into an enormous hellstorm of SQL that any Haskell programmer will see as full of redundancy. This is because SQL is almost immune to abstraction. (I have to specify the "Haskell programmer" bit because of the characteristics of this sort of language is that working in it directly for a long time causes you to think natively in that language and thereby miss the opportunities for abstraction that you no longer see. FWIW, Erlang also bothers me this way.) Yes, the "bad programmer" issue is in play too, but as a good programmer who has tried to write good stored procedures and has experience in numerous other languages I can not help but notice how much SQL fights me on this front.
SQL is very difficult to debug in general, which combines rather poorly with the previous characteristic. Perhaps there are nonstandard extensions in Oracle that make it easier but there's nothing I know of in the core language.
SQL has fooled people into thinking it is equivalent to the Relational Model, when in fact as kunley points out elsewhere it is merely inspired by it.
SQL dates from the 1970s and it shows. There are syntax quirks around every corner. The behavior of NULL is sensible, but it is merely one of several sensible choices and there are reasons why the SQL choice is not the one that has "won". Why is "value IN ()" a syntax error? It's perfectly reasonable. What's with the syntactic drama around subselects, which ought to be a core part of the better language that we really ought to be using? (A language in which the equivalent is probably going to be so simple we won't even believe people had a special syntax for it.) What's with the rigid specification of the order of GROUP BY and ORDER BY and everything else, when those should simply be instances of some sort of combinator that applies in order, in a sensible manner. Why is SELECT used both for data queries and aggregate queries when in fact the two operations are so different they should have entirely different keywords? Why is it so hard to mix an aggregate and non-aggregate query together when it's obvious to me exactly what I want?
Why can't my columns contain tables themselves, recursively? The fact that there are databases that can do this proves it's not impossible.
SQL needs to be replaced by a combinator-based language that backs to a richer data store that is more sensible, both from a relational model point of view and from a recursive point of view. LINQ points the way, but still is hobbled by LINQ-to-SQL being the flagship LINQ product. (And I don't think it's complete as-is.)
> Further: SQL is not composable. If I have a fragment of SQL in my hand, there is no reasonable way to insert that into a larger expression.
This is really annoying. If I have a stored proc for getting data w/ 3 optional parameters for filtering it, I have to either use dynamic SQL (building the query by string concatenation), or have 8 different versions of the query for each combination of filter present/not present. The dynamic SQL part might not even work with bind parameters, because EXECUTE IMMEDIATE / sp_executesql cannot take a dynamic number of bind parameters (0 to 3 bind parameters, depending on the filter).
I have to wonder why none of the database vendors ever created a syntax-tree API for SQL queries. They have the parser right there, so why can't they do something like
q := PARSE(SELECT x, y, z FROM sometable);
q.ADD_CONDITION(PARSE(sometable.value = some_dynamic_value));
EXECUTE q;
"But why do you want columns that contain tables recursively?"
This is the sort of question you ask because you've become so used to the way SQL does things that you can't even think outside the box. (No offense; this is SQL's fault, not yours.) If I recall correctly, the Relational Model fully permits this. And once you start thinking about it, you can see that there are numerous places where you're basically trying to do things like this, and it would be much easier if you could have tables in rows, but instead you have to put a series of weird hacks in. Anytime you have a table that participates in multiple many-to-many relationships, it would be a lot cleaner if SQL could just say "Look, this ID has no meaning except inasmuch as it identifies another set of rows somewhere else, so how about we just cut to the chase and say that those rows are just here?" The joins could be the default and magic access to the ID could be the thing you have to type extra to get.
"Aggregate and non-aggregate mixture is supported in the standard and Oracle with the analytical expressions (if I got your meaning correctly)."
What I mean was more a syntactic issue than a question of whether it's possible; there's very poor separation between the two, so combining them is even harder.
(And crap, the design for my combinator-based language is starting to coalesce in my head. Damn it, that wasn't supposed to happen, I was being vague on purpose....)
Why not columns that contain tables? It would certainly make things easier. e.g. Get a list of customers with their orders:
SELECT ... FROM customers LEFT JOIN orders ...;
To get this into a presentable table, I would have to keep track of the previous customer's ID and only start a new row if the current customer ID is not equal to the previous customer ID. If table-valued columns were available, each customer row would have a column containing a table of their orders.
I agree with you generally, but you seem to imply query performance tuning hasn't been done properly with the major database vendors, but maybe I misinterpreted you?
I don't see where I talked about performance at all. Performance of an implementation is not entirely unrelated to the specification, but they are usually related in really weird, counter-intuitive ways.
SQL is very difficult to debug in general, which combines rather poorly with the previous characteristic. Perhaps there are nonstandard extensions in Oracle that make it easier but there's nothing I know of in the core language.
SQL has fooled people into thinking it is equivalent to the Relational Model, when in fact as kunley points out elsewhere it is merely inspired by it.
SQL dates from the 1970s and it shows. There are syntax quirks around every corner. The behavior of NULL is sensible, but it is merely one of several sensible choices and there are reasons why the SQL choice is not the one that has "won". Why is "value IN ()" a syntax error? It's perfectly reasonable. What's with the syntactic drama around subselects, which ought to be a core part of the better language that we really ought to be using? (A language in which the equivalent is probably going to be so simple we won't even believe people had a special syntax for it.) What's with the rigid specification of the order of GROUP BY and ORDER BY and everything else, when those should simply be instances of some sort of combinator that applies in order, in a sensible manner. Why is SELECT used both for data queries and aggregate queries when in fact the two operations are so different they should have entirely different keywords? Why is it so hard to mix an aggregate and non-aggregate query together when it's obvious to me exactly what I want?
Why can't my columns contain tables themselves, recursively? The fact that there are databases that can do this proves it's not impossible.
SQL needs to be replaced by a combinator-based language that backs to a richer data store that is more sensible, both from a relational model point of view and from a recursive point of view. LINQ points the way, but still is hobbled by LINQ-to-SQL being the flagship LINQ product. (And I don't think it's complete as-is.)