4 ms·

SQL only hangs around because it was the best thing available at the time when DBs first exploded into common use. It’s pretty declarative, which is a point in

SQL only hangs around because it was the best thing available at the time when DBs first exploded into common use. It’s pretty declarative, which is a point in its favor, but it’s not very amenable to query construction since it has AppleScript levels of “being like English.” Have fun writing something that can generate it while handling highly-adaptive and/or customizable requirements. I’m not going to call Datalog the definitive answer, but it’s a step in the right direction.

hsbauauvhabzb 4y ago
I’ve long felt that SQL was somehow backwards, but I don’t know what would be better. ‘Go and get two eggs’ Is better than ‘go to the fridge and get eggs, only two’.
- masklinn 4y ago
  > but I don’t know what would be better. Strictly following the semantics order of the querying, whether forward or reverse. The ordering issue of SQL is that it uses a mixed ordering, the physical layout of the query in text form doesn’t follow the logical execution of the query, which makes it very confusing. Comparing to short cutesy sentences is not helpful as a non-trivial query has dozens or hundreds of steps, it’s closer to a build book or checklist. Furthermore english is far from a paragon of clarity or regularity, and languages which try to follow it (SQL, AppleScript) tend to scale a lot worse with instruction sizes and complexity.
- afiori 4y ago
  After using moonshine MongoDB pipelines I would say that it is a fantastic model. You can sort of already do this in SQL with CTE* but a nice pipeline syntax would be welcome. * Actually I believe that you can do more with CTE as you can mix the various steps in arbitrary ways
- Deukhoofd 4y ago
  Wouldn't a better analogy for SQL be 'Get the top two eggs from the fridge'? It'd translate to select top 2 eggs from fridge;
  - zaphirplane 4y ago
    Select top(eggs) from fridge limit 2
  - jorams 4y ago
    That's not standard SQL. This was standardized pretty late, so different RDBMS have various syntaxes, but the standardized one is to add a clause like this: FETCH FIRST n ROWS ONLY In PostgreSQL and MySQL the usual syntax is a LIMIT clause, but PostgreSQL also supports the standard. MSSQL and some others use TOP n directly after SELECT. I think in some others you need to use a window function.
    - LikesPwsh 4y ago
      Top n is even deprecated in mssql, in favor of top(n).
chirau 4y ago
With complex queries and query optimization, I will take SQL over Datalog every single time
- Karrot_Kream 4y ago
  How come? I've only played around with Datalog. Any production war stories you have to share?
SCLeo 4y ago
Honestly, this really confuses me. On the surface, it seems what you said is true. How can a programming language that emulates natural language be good? However, in practice, SQL is surprisingly easy to write and expressive. I do prefer using ORMs for simpler queries because of intellisense and typing, but for complex queries, SQL is just better. I really don't understand why.
- aflag 4y ago
  Being english-like is just appearances. SQL is really just relational algebra (the traditional querying bit of it, at least) with some technical limitations. But in essence, it's relational algebra and it works well because the topic was studied a lot by many smart people and the language reflects these developments.
- DaiPlusPlus 4y ago
  > SQL is surprisingly easy to write and expressive Counter-example: using only majority-support ISO SQL string functions and operators (SUBSTRING, CHARINDEX, LIKE, NOT LIKE, LEN, etc - but not any regular-expressions), for a string column that contains URI query-string style name+value pairs, e.g. "foo=bar&baz=qux", try to extract out the value for the key "baz" - note that the source column value could also be "&baz=qux&foo=bar&" or "baz=qux&foo=bar&". Doing that is trivial in most conventional programming languages: just use a couple of local-variables to store CHARINDEX values then pass those into SUBSTRING to get the desired "qux" value. ...but in SQL (at least to my knowledge) there's no concept of local-variables in a SELECT query, instead you need to pass values along via derived-tables or CTEs - or move the logic to a scalar UDF - or give up trying to be succint and do it with dozens of identical CHARINDEX call-sites and pray that the RDBMS is smart enough to memoize UDFs in a query step; none of those options are appealing even in their own right - so we're kinda stuck (and if you want to be succint you'll need not only UDF support, but also inlined and concurrent execution of UDFs otherwise your query performance will tank). Instead, what if ISO SQL had the ability to define in-query "mini" pure-functions, which represent a (monadic?) sequence of operations, including mutable local variables, over its input parameters - this would solve the verbosity/DRY problem entirely - and without needing you to split a query's logic into two (i.e. the main SELECT query but also many CREATE FUNCTION statements). I note that the existing CTE syntax could be adapted for defining those functions, for example a query to extract querystring values could look like this: ``` WITH FUNCTION getQueryStringValue( input varchar, key varchar ) RETURNS varchar NULL AS ( SET key = CONCAT( key, '=' ); LET keyIdx = CHARINDEX( haystack: input, needle: key ); IF keyIdx < 0 THEN RETURN NULL; LET valueIdx = keyIdx + LEN( key ); LET nextSeparatorIdx = CHARINDEX( haystack: input, needle: '&', startIndex: keyIdx ); IF nextSeparatorIdx < 0 THEN SET nextSeparatorIdx = LEN( input ); LET valueLen = nextSeparatorIdx - valueIdx; LET value = SUBSTRING( text: input, startIndex: valueIdx, length: valueLen ); RETURN value; ), cte AS ( SELECT getQueryStringValue( src.QueryStringA, 'foo' ) AS fooValue1, getQueryStringValue( src.QueryStringB, 'foo' ) AS fooValue2, getQueryStringValue( src.QueryStringC, 'foo' ) AS fooValue3 FROM src ) SELECT CONCAT( fooValue1, fooValue2, fooValue3 ) AS whatevs FROM cte ORDER BY someCol; ``` Thoughts?
inopinatus 4y ago
Let's not erase the work of Stonebraker et al whose efforts through the 70s were a lot closer to Codd's relational algebra than SQL. Ingres's query language, Quel, springs to mind: being directly based on the relational calculus it was far more readily composable than SQL (which might be charitably described as "borrowing ideas from the relational model") was or is. As with the videotape format war, the eventual industry standard was decided commercially: Oracle was by far the most aggressive, and promoted SQL.
- FrustratedMonky 4y ago
  Yes, we can have relational databases without SQL, there are better ways for composability. Some might argue that SQL itself isn't a mess, but it sure does help people make a mess of themselves.
ackfoobar 4y ago
> not very amenable to query construction I had a period of perferring MongoDB for this reason. Because the query is the bare AST in Mongo, dynamic query construction is simply manipulating some JSON. Then I used jooq and never looked back.

hsbauauvhabzb 4y ago

I’ve long felt that SQL was somehow backwards, but I don’t know what would be better. ‘Go and get two eggs’ Is better than ‘go to the fridge and get eggs, only two’.

masklinn 4y ago
> but I don’t know what would be better. Strictly following the semantics order of the querying, whether forward or reverse. The ordering issue of SQL is that it uses a mixed ordering, the physical layout of the query in text form doesn’t follow the logical execution of the query, which makes it very confusing. Comparing to short cutesy sentences is not helpful as a non-trivial query has dozens or hundreds of steps, it’s closer to a build book or checklist. Furthermore english is far from a paragon of clarity or regularity, and languages which try to follow it (SQL, AppleScript) tend to scale a lot worse with instruction sizes and complexity.
afiori 4y ago
After using moonshine MongoDB pipelines I would say that it is a fantastic model. You can sort of already do this in SQL with CTE* but a nice pipeline syntax would be welcome. * Actually I believe that you can do more with CTE as you can mix the various steps in arbitrary ways
Deukhoofd 4y ago
Wouldn't a better analogy for SQL be 'Get the top two eggs from the fridge'? It'd translate to select top 2 eggs from fridge;
- zaphirplane 4y ago
  Select top(eggs) from fridge limit 2
- jorams 4y ago
  That's not standard SQL. This was standardized pretty late, so different RDBMS have various syntaxes, but the standardized one is to add a clause like this: FETCH FIRST n ROWS ONLY In PostgreSQL and MySQL the usual syntax is a LIMIT clause, but PostgreSQL also supports the standard. MSSQL and some others use TOP n directly after SELECT. I think in some others you need to use a window function.
  - LikesPwsh 4y ago
    Top n is even deprecated in mssql, in favor of top(n).

chirau 4y ago

With complex queries and query optimization, I will take SQL over Datalog every single time

Karrot_Kream 4y ago
How come? I've only played around with Datalog. Any production war stories you have to share?

SCLeo 4y ago

Honestly, this really confuses me. On the surface, it seems what you said is true. How can a programming language that emulates natural language be good? However, in practice, SQL is surprisingly easy to write and expressive. I do prefer using ORMs for simpler queries because of intellisense and typing, but for complex queries, SQL is just better. I really don't understand why.

aflag 4y ago
Being english-like is just appearances. SQL is really just relational algebra (the traditional querying bit of it, at least) with some technical limitations. But in essence, it's relational algebra and it works well because the topic was studied a lot by many smart people and the language reflects these developments.
DaiPlusPlus 4y ago
> SQL is surprisingly easy to write and expressive Counter-example: using only majority-support ISO SQL string functions and operators (SUBSTRING, CHARINDEX, LIKE, NOT LIKE, LEN, etc - but not any regular-expressions), for a string column that contains URI query-string style name+value pairs, e.g. "foo=bar&baz=qux", try to extract out the value for the key "baz" - note that the source column value could also be "&baz=qux&foo=bar&" or "baz=qux&foo=bar&". Doing that is trivial in most conventional programming languages: just use a couple of local-variables to store CHARINDEX values then pass those into SUBSTRING to get the desired "qux" value. ...but in SQL (at least to my knowledge) there's no concept of local-variables in a SELECT query, instead you need to pass values along via derived-tables or CTEs - or move the logic to a scalar UDF - or give up trying to be succint and do it with dozens of identical CHARINDEX call-sites and pray that the RDBMS is smart enough to memoize UDFs in a query step; none of those options are appealing even in their own right - so we're kinda stuck (and if you want to be succint you'll need not only UDF support, but also inlined and concurrent execution of UDFs otherwise your query performance will tank). Instead, what if ISO SQL had the ability to define in-query "mini" pure-functions, which represent a (monadic?) sequence of operations, including mutable local variables, over its input parameters - this would solve the verbosity/DRY problem entirely - and without needing you to split a query's logic into two (i.e. the main SELECT query but also many CREATE FUNCTION statements). I note that the existing CTE syntax could be adapted for defining those functions, for example a query to extract querystring values could look like this: ``` WITH FUNCTION getQueryStringValue( input varchar, key varchar ) RETURNS varchar NULL AS ( SET key = CONCAT( key, '=' ); LET keyIdx = CHARINDEX( haystack: input, needle: key ); IF keyIdx < 0 THEN RETURN NULL; LET valueIdx = keyIdx + LEN( key ); LET nextSeparatorIdx = CHARINDEX( haystack: input, needle: '&', startIndex: keyIdx ); IF nextSeparatorIdx < 0 THEN SET nextSeparatorIdx = LEN( input ); LET valueLen = nextSeparatorIdx - valueIdx; LET value = SUBSTRING( text: input, startIndex: valueIdx, length: valueLen ); RETURN value; ), cte AS ( SELECT getQueryStringValue( src.QueryStringA, 'foo' ) AS fooValue1, getQueryStringValue( src.QueryStringB, 'foo' ) AS fooValue2, getQueryStringValue( src.QueryStringC, 'foo' ) AS fooValue3 FROM src ) SELECT CONCAT( fooValue1, fooValue2, fooValue3 ) AS whatevs FROM cte ORDER BY someCol; ``` Thoughts?

inopinatus 4y ago

Let's not erase the work of Stonebraker et al whose efforts through the 70s were a lot closer to Codd's relational algebra than SQL. Ingres's query language, Quel, springs to mind: being directly based on the relational calculus it was far more readily composable than SQL (which might be charitably described as "borrowing ideas from the relational model") was or is. As with the videotape format war, the eventual industry standard was decided commercially: Oracle was by far the most aggressive, and promoted SQL.

FrustratedMonky 4y ago
Yes, we can have relational databases without SQL, there are better ways for composability. Some might argue that SQL itself isn't a mess, but it sure does help people make a mess of themselves.

ackfoobar 4y ago

> not very amenable to query construction I had a period of perferring MongoDB for this reason. Because the query is the bare AST in Mongo, dynamic query construction is simply manipulating some JSON. Then I used jooq and never looked back.