5 ms·

Mangling the well-known quotation: SQL is the worst syntax, except all the others that have been tried. What are you comparing it to?

pygy_ 14y ago
QUEL was pretty clean. Far more consistent than SQL. It died, mostly because SQL was used by Oracle and IBM DB2 which were better marketed than Ingres. The DB world ended up standardizing on SQL because of this. Worse is better, once again. http://en.wikipedia.org/wiki/QUEL_query_languages http://en.wikipedia.org/wiki/QUEL_query_languages
- justinsb 14y ago
  It is cleaner, although it doesn't feel _that_ different to SQL to my eye. I guess if we're now thinking about replacing SQL then I'd want to get a lot in return. (I do know that I'm sort of moving the goalposts here - thank you for contributing the QUEL info)
- einhverfr 14y ago
  interestingly COPY survives in PostgreSQL though not quite in the same form as in Quel.
beagle3 14y ago
Kx system's kdb+/q query language. It looks almost like SQL; the main philosophical differences: a) it embraces order (that is, every query result has an implicit "running index" field (called i, starting at 0). This single handedly solves a lot of inconsistencies in practical SQL having to do with order, which is abhorred by the relational data model, thus not a first class concept, but is often required in practice, and thus inconsistently bolted on. b) columns can nest, and do not have to have the same type - which means that aggregations like count, sum, max, min and distinct are not special in any way. c) there's a simple underlying programming language, so if you have an intermediate select result that you need twice, you just give it a name by prepending a "name: " to the select. temp: select from grades where age>10; b1: select from temp where eyecolor=`blue; b2: select from temp where eyecolor=`brown; (compare to the mess that is correlated sub-queries, or alternatively, horrible "create temporary table x as ..."
- justinsb 14y ago
  I definitely see why (a) is good for time-series data, but I (personally) think that it shouldn't be a first-class part of a relational query language; I think most databases do support the ROW_NUMBER() function now though. I think (c) has been introduced as Common Table Expressions in SQL. I'm not sure I fully understand (b) - do you have a link so I can learn more? As you can probably guess, I'm very interested in this stuff!
  - beagle3 14y ago
    > good for time-series data, but I (personally) think that it shouldn't be a first-class part of a relational query language; First of all, there's some tautology here - a "relational database" (and similarly, relational calculus, relational algebra, etc), BY DEFINITION deal with sets of tuples ("relations") which are (again, by definition) unordered. So let's just ignore the word "relational" in this discussion. > I think most databases do support the ROW_NUMBER() function now though. Many do. But do compare (sql 2005): SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS ROW_NUMBER, COLUMNS FROM tablename ) foo WHERE ROW_NUMBER <= 10 To (kdb+ / q): select from tablename orderby asc sort_key where i<10; And usefulness of order goes why beyond time-series data (and sorting): let's say you have a tiered pricing scheme for widgets you sell: order_size | per_widget_cost ---------------------------- 1-9 | $2 10-49 | $1.9 50-199 | $1.8 Without embracing order, you: a) duplicate the range data (have a "from-count", "to-count" fields for each record, risking that you might have holes or overlaps) b) not duplicate data, but have crazy subselects (among all with count > from_count, select the one with maximum to_count) or stuff like that. When you actually have order, you have operator that embrace that order - e.g. kdb+/q's "bin" which finds the "bin" (as in "bucket", not as in binray) something fits in: select unit_price[from_count bin order_count] from table There are many other use cases involving running sums (e.g, you have a table of weights and priority; select the list of highest priority items whose weight sums to 100lbs or less). Order is really really missing in SQL, but it's one of those things people are not aware of because they've never used anything that does support it properly. nested columns just means what it sounds like: that you can put anything in a cell (including lists, tables, lists of tables, lists of lists of lists of tables). Many one-to-many relationships in sql that need additional tables can just be done within the same table in kdb+/q regarding Common Table Expressions - I wasn't aware of them, they do help a lot. The syntax is horrible, but I guess they do work... edit: more info on kdb+/q can be found in http://kx.com/q/d/kdb+1.htm http://kx.com/q/d/kdb+1.htm - you have to get to section 8 before they start discussing the query language, but it's short and to the point. There's a lot more in http://kx.com/q/d/ http://kx.com/q/d/ if you are interested.
jfb 14y ago
Tutorial D, for one. Or any language that isn't as hideously broken as SQL. * why bags? * where's my closure under composition? * THAT SYNTAX OH GOD THAT SYNTAX I'd be much happier with a more mathematical language, rather than the godawful "english-lite" 3GL misery
- einhverfr 14y ago
  By the way, the fundamental concern I have with SQL is the issue of ambiguity regarding NULLs. We are taught that NULL means one of two things, but really it means one of three: * Unknown value * Not applicable value * Value does not exist This is a big issue, because you would expect operators to treat these cases differently. known || unknown is obviously unknown, but known || not_applicable should probably be known, and known || does_not_exist should be known. In sane RDBMS's there is a possibility of magic values which provides a sane way to handle not_applicable (for example an empty string as distinct from NULL and yes I am calling into question the sanity of Oracle). However, you still have the fact that the first and third cases are ambiguous although you hope not in any given query (the third case implies a missing value from an outer join), the ambiguity could in fact happen. This is a fundamentally broken aspect of SQL. The problem with ambiguity is that if your data is ambiguous mathematically, then it cannot be reliably transformed using math.

pygy_ 14y ago

QUEL was pretty clean. Far more consistent than SQL. It died, mostly because SQL was used by Oracle and IBM DB2 which were better marketed than Ingres. The DB world ended up standardizing on SQL because of this. Worse is better, once again. http://en.wikipedia.org/wiki/QUEL_query_languages http://en.wikipedia.org/wiki/QUEL_query_languages

justinsb 14y ago
It is cleaner, although it doesn't feel _that_ different to SQL to my eye. I guess if we're now thinking about replacing SQL then I'd want to get a lot in return. (I do know that I'm sort of moving the goalposts here - thank you for contributing the QUEL info)
einhverfr 14y ago
interestingly COPY survives in PostgreSQL though not quite in the same form as in Quel.

beagle3 14y ago

Kx system's kdb+/q query language. It looks almost like SQL; the main philosophical differences: a) it embraces order (that is, every query result has an implicit "running index" field (called i, starting at 0). This single handedly solves a lot of inconsistencies in practical SQL having to do with order, which is abhorred by the relational data model, thus not a first class concept, but is often required in practice, and thus inconsistently bolted on. b) columns can nest, and do not have to have the same type - which means that aggregations like count, sum, max, min and distinct are not special in any way. c) there's a simple underlying programming language, so if you have an intermediate select result that you need twice, you just give it a name by prepending a "name: " to the select. temp: select from grades where age>10; b1: select from temp where eyecolor=`blue; b2: select from temp where eyecolor=`brown; (compare to the mess that is correlated sub-queries, or alternatively, horrible "create temporary table x as ..."

justinsb 14y ago
I definitely see why (a) is good for time-series data, but I (personally) think that it shouldn't be a first-class part of a relational query language; I think most databases do support the ROW_NUMBER() function now though. I think (c) has been introduced as Common Table Expressions in SQL. I'm not sure I fully understand (b) - do you have a link so I can learn more? As you can probably guess, I'm very interested in this stuff!
- beagle3 14y ago
  > good for time-series data, but I (personally) think that it shouldn't be a first-class part of a relational query language; First of all, there's some tautology here - a "relational database" (and similarly, relational calculus, relational algebra, etc), BY DEFINITION deal with sets of tuples ("relations") which are (again, by definition) unordered. So let's just ignore the word "relational" in this discussion. > I think most databases do support the ROW_NUMBER() function now though. Many do. But do compare (sql 2005): SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY sort_key ASC) AS ROW_NUMBER, COLUMNS FROM tablename ) foo WHERE ROW_NUMBER <= 10 To (kdb+ / q): select from tablename orderby asc sort_key where i<10; And usefulness of order goes why beyond time-series data (and sorting): let's say you have a tiered pricing scheme for widgets you sell: order_size | per_widget_cost ---------------------------- 1-9 | $2 10-49 | $1.9 50-199 | $1.8 Without embracing order, you: a) duplicate the range data (have a "from-count", "to-count" fields for each record, risking that you might have holes or overlaps) b) not duplicate data, but have crazy subselects (among all with count > from_count, select the one with maximum to_count) or stuff like that. When you actually have order, you have operator that embrace that order - e.g. kdb+/q's "bin" which finds the "bin" (as in "bucket", not as in binray) something fits in: select unit_price[from_count bin order_count] from table There are many other use cases involving running sums (e.g, you have a table of weights and priority; select the list of highest priority items whose weight sums to 100lbs or less). Order is really really missing in SQL, but it's one of those things people are not aware of because they've never used anything that does support it properly. nested columns just means what it sounds like: that you can put anything in a cell (including lists, tables, lists of tables, lists of lists of lists of tables). Many one-to-many relationships in sql that need additional tables can just be done within the same table in kdb+/q regarding Common Table Expressions - I wasn't aware of them, they do help a lot. The syntax is horrible, but I guess they do work... edit: more info on kdb+/q can be found in http://kx.com/q/d/kdb+1.htm http://kx.com/q/d/kdb+1.htm - you have to get to section 8 before they start discussing the query language, but it's short and to the point. There's a lot more in http://kx.com/q/d/ http://kx.com/q/d/ if you are interested.

jfb 14y ago

Tutorial D, for one. Or any language that isn't as hideously broken as SQL. * why bags? * where's my closure under composition? * THAT SYNTAX OH GOD THAT SYNTAX I'd be much happier with a more mathematical language, rather than the godawful "english-lite" 3GL misery

einhverfr 14y ago
By the way, the fundamental concern I have with SQL is the issue of ambiguity regarding NULLs. We are taught that NULL means one of two things, but really it means one of three: * Unknown value * Not applicable value * Value does not exist This is a big issue, because you would expect operators to treat these cases differently. known || unknown is obviously unknown, but known || not_applicable should probably be known, and known || does_not_exist should be known. In sane RDBMS's there is a possibility of magic values which provides a sane way to handle not_applicable (for example an empty string as distinct from NULL and yes I am calling into question the sanity of Oracle). However, you still have the fact that the first and third cases are ambiguous although you hope not in any given query (the third case implies a missing value from an outer join), the ambiguity could in fact happen. This is a fundamentally broken aspect of SQL. The problem with ambiguity is that if your data is ambiguous mathematically, then it cannot be reliably transformed using math.