10 ms·

I view SQL right now similar to the way I viewed C++ in early 2000s: I hate it, but there's little point in complaining because it's so ubiquitous. More robus

I view SQL right now similar to the way I viewed C++ in early 2000s: I hate it, but there's little point in complaining because it's so ubiquitous. More robust criticism is provided here (https://carlineng.com/?postid=sql-critique#blog https://carlineng.com/?postid=sql-critique#blog), which pulls on an interview here (https://www.red-gate.com/simple-talk/opinion/opinion-pieces/chris-date-and-the-relational-model/ https://www.red-gate.com/simple-talk/opinion/opinion-pieces/...) The quote I usually drag out is from Chris Date, who helped pioneer relational DBs: "At the same time, I have to say too that we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)." As an example of a language that does it better, I think kusto-query-language (KQL, https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...) has been a dream to work with. (disclaimer, Kusto is a Microsoft product, and I'm a Microsoft employee).

wvenable 2y ago
I kind of disagree; the problem with SQL is that fundamentally it's actually pretty good. So alternatives either tend to be too radical (throwing out the baby with the bathwater) or simply not enough of an improvement to gain any momentum. I feel like rational database querying is effectively solved and there's little point in re-litigating it. But still I'd be happy to switch to the perfect replacement if someone develops it.
- vkazanov 2y ago
  What does "good" mean in this context? SQL is not modular, most features are highly context-dependent and there numerous handguns. Sql might be ok for trivial things, as in OLTP that programmers tend to work with. But anything even slightly more advanced is... not nice. And the standard is unique in its uselessness. The underlying relational algebra model is brilliant thought.
  - rangerelf 2y ago
    > SQL is not modular, most features are highly context-dependent... Examples? > SQL might be OK for trivial things, as in OLTP... What is the threshold for triviality? I've seen understandable fairly complex queries, but they're not mind-twisters by any means; if you know what you need, and understand your data, >>and are not a layperson regarding databases<< it's doable without much sweat. > But anything even slightly more advanced is ... not nice Again, what is the threshold, or at least what is your threshold, for triviality vs. non-trivial? You say it's "unique in its uselessness" but "the underlying relational algebra model is brilliant", can you explain a bit further what you mean by that?
  - wvenable 2y ago
    Good means it gets the job done in a fairly logical and readable way. SQL queries are not giant programs and shouldn't be. I've written some very advanced queries with plenty of common table expressions, subselects, etc. Could it be more modular? Sure. Could the syntax be better? Yes. But would that radically change how queries are written? Not really. The worst SQL I've ever seen is when someone attempts to program it imperatively. It takes a different mentality to write SQL then to write imperative code.
    - randomdata 2y ago
      > But would that radically change how queries are written? Not really. Rust didn't radically change how applications are written as compared to C, but that didn't stop us. Nor should it. Any improvement is worthwhile. It doesn't need to be radical. But, like another commenter points out, SQL is like Javascript. Both having ecosystems so horrendously conceived that they have ensured there is no good path to replacing/augmenting them.
      - wvenable 2y ago
        Rust hasn't swept the world yet and it helps prevent real bugs and security issues. A better query language may make it fractionally easier to write database queries but you have to toss out a half-century of experience. It's not worth it for marginal gains. This isn't even opinion, this is the reason it has never happened. I think I agree that SQL is like JavaScript. If JavaScript wasn't as expressively powerful as it is, it would have been replaced a long time ago. But it's actually good enough, despite it's quirks, that there doesn't exist a language better enough to make it worth replacement. It's possible such a language might never exist. And both SQL and JavaScript continue to improve sometimes directly stealing ideas from potential competitors.
- deleted 2y ago
  [deleted]
- fbdab103 2y ago
  I think the fundamental problem is that if you want to talk to databases, you have to speak SQL. Like Javascript on the web, you are stuck with what the platform provides. Both languages have significant deficiencies, yet is/was the only game in town. Sure, there are some languages which can compile down to SQL, but like Typescript, every once and a while, you find some edge case where the transpilation fails you and you might as well be an expert in SQL. I love all of the ideas of PRQL, but I do not know if I would be painting myself into a corner adopting something that will go the way of CoffeeScript.
deleted 2y ago
[deleted]
barryrandall 2y ago
I only ever need to know KQL when something isn't working well, which turns 1 problem into 2: the original problem, and how to express exactly what I need in a language that, when things are going well, I forget quickly.
- bbkane 2y ago
  I've definitely had my troubles with KQL, but I still find it more memorable than SQL. Interesting that some folks feel the opposite. My most recent favorite KQL trick for debugging from logs is `autocluster`. Maybe you'll also find it useful when you're in trouble :)
zepolen 2y ago
I love SQL, I think it's fantastic, it's also the one skill I learned 30 years ago that still applies today and that I, personally, have used across at least six different databases. The critiques in that link show a fundamental lack of understanding, eg. it poses that the following query should be allowed and wonders why sql complains about fetching more than one row since avg requires more than one row, but the reality is that what they are describing is avg(rows of rows) rather than avg(rows) which obviously doesn't make sense: SELECT AVG( SELECT SUM(amount) FROM purchases GROUP BY customer ) But even worse, the article says that the only solution is using a CTE(!?) and doesn't mention the obvious use of a subquery: SELECT AVG(total) FROM ( SELECT SUM(amount) total_per_customer FROM purchases GROUP BY customer ); In my experience I've seen that usually the people that find it difficult to understand SQL are also the people that will be writing .fetch_by_id functions instead of .fetch_by_filters, ie. they consider database tables and data in general as 1d arrays in imperative programming and only think in those terms rather than the 2d sets they are. I'd wonder how their mind would melt if they'd tried to understand querying for 3d data, eg. temporal databases. Also I fail to see how KQL is any different to SQL, it's pretty much the same thing with the exception that the selection comes afterwards, is that the factor that makes you hate SQL? From the link you posted: StormEvents | where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01)) | where State == "FLORIDA" | count vs FROM StormEvents WHERE StartTime BETWEEN '2007-11-01' AND '2007-12-01' AND State = 'FLORIDA' SELECT count(*) and StormEvents | where DamageCrops > 0 | summarize MaxCropDamage=max(DamageCrops), MinCropDamage=min(DamageCrops), AvgCropDamage=avg(DamageCrops) by EventType | sort by AvgCropDamage vs FROM StormEvents WHERE DamageCrops > 0 SELECT max(DamageCrops) MaxCropDamage, min(DamageCrops) MinCropDamage, avg(DamageCrops) AvgCropDamage GROUP BY EventType ORDER BY AvgCropDamage
- bradford 2y ago
  To be clear, I don't find SQL difficult to understand. I've used it for 20+ years and I can always get the query to generate my desired output. But I often find that the language is a hindrance, and that I can more efficiently reach my desired output using modern languages. for example, here's the KQL equivalent to the 'average of sums' query: purchases | summarize total_per_customer=sum(amount) by customer | summarize avg(total_per_customer) I find this more elegant, and I'd prefer authoring it over any of the equivalent SQL solutions previously mentioned.
School-Cotton 2y ago
https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/ is another great takedown of SQL.

wvenable 2y ago

I kind of disagree; the problem with SQL is that fundamentally it's actually pretty good. So alternatives either tend to be too radical (throwing out the baby with the bathwater) or simply not enough of an improvement to gain any momentum. I feel like rational database querying is effectively solved and there's little point in re-litigating it. But still I'd be happy to switch to the perfect replacement if someone develops it.

vkazanov 2y ago
What does "good" mean in this context? SQL is not modular, most features are highly context-dependent and there numerous handguns. Sql might be ok for trivial things, as in OLTP that programmers tend to work with. But anything even slightly more advanced is... not nice. And the standard is unique in its uselessness. The underlying relational algebra model is brilliant thought.
- rangerelf 2y ago
  > SQL is not modular, most features are highly context-dependent... Examples? > SQL might be OK for trivial things, as in OLTP... What is the threshold for triviality? I've seen understandable fairly complex queries, but they're not mind-twisters by any means; if you know what you need, and understand your data, >>and are not a layperson regarding databases<< it's doable without much sweat. > But anything even slightly more advanced is ... not nice Again, what is the threshold, or at least what is your threshold, for triviality vs. non-trivial? You say it's "unique in its uselessness" but "the underlying relational algebra model is brilliant", can you explain a bit further what you mean by that?
- wvenable 2y ago
  Good means it gets the job done in a fairly logical and readable way. SQL queries are not giant programs and shouldn't be. I've written some very advanced queries with plenty of common table expressions, subselects, etc. Could it be more modular? Sure. Could the syntax be better? Yes. But would that radically change how queries are written? Not really. The worst SQL I've ever seen is when someone attempts to program it imperatively. It takes a different mentality to write SQL then to write imperative code.
  - randomdata 2y ago
    > But would that radically change how queries are written? Not really. Rust didn't radically change how applications are written as compared to C, but that didn't stop us. Nor should it. Any improvement is worthwhile. It doesn't need to be radical. But, like another commenter points out, SQL is like Javascript. Both having ecosystems so horrendously conceived that they have ensured there is no good path to replacing/augmenting them.
    - wvenable 2y ago
      Rust hasn't swept the world yet and it helps prevent real bugs and security issues. A better query language may make it fractionally easier to write database queries but you have to toss out a half-century of experience. It's not worth it for marginal gains. This isn't even opinion, this is the reason it has never happened. I think I agree that SQL is like JavaScript. If JavaScript wasn't as expressively powerful as it is, it would have been replaced a long time ago. But it's actually good enough, despite it's quirks, that there doesn't exist a language better enough to make it worth replacement. It's possible such a language might never exist. And both SQL and JavaScript continue to improve sometimes directly stealing ideas from potential competitors.
deleted 2y ago
[deleted]
fbdab103 2y ago
I think the fundamental problem is that if you want to talk to databases, you have to speak SQL. Like Javascript on the web, you are stuck with what the platform provides. Both languages have significant deficiencies, yet is/was the only game in town. Sure, there are some languages which can compile down to SQL, but like Typescript, every once and a while, you find some edge case where the transpilation fails you and you might as well be an expert in SQL. I love all of the ideas of PRQL, but I do not know if I would be painting myself into a corner adopting something that will go the way of CoffeeScript.

deleted 2y ago

[deleted]

barryrandall 2y ago

I only ever need to know KQL when something isn't working well, which turns 1 problem into 2: the original problem, and how to express exactly what I need in a language that, when things are going well, I forget quickly.

bbkane 2y ago
I've definitely had my troubles with KQL, but I still find it more memorable than SQL. Interesting that some folks feel the opposite. My most recent favorite KQL trick for debugging from logs is `autocluster`. Maybe you'll also find it useful when you're in trouble :)

zepolen 2y ago

I love SQL, I think it's fantastic, it's also the one skill I learned 30 years ago that still applies today and that I, personally, have used across at least six different databases. The critiques in that link show a fundamental lack of understanding, eg. it poses that the following query should be allowed and wonders why sql complains about fetching more than one row since avg requires more than one row, but the reality is that what they are describing is avg(rows of rows) rather than avg(rows) which obviously doesn't make sense: SELECT AVG( SELECT SUM(amount) FROM purchases GROUP BY customer ) But even worse, the article says that the only solution is using a CTE(!?) and doesn't mention the obvious use of a subquery: SELECT AVG(total) FROM ( SELECT SUM(amount) total_per_customer FROM purchases GROUP BY customer ); In my experience I've seen that usually the people that find it difficult to understand SQL are also the people that will be writing .fetch_by_id functions instead of .fetch_by_filters, ie. they consider database tables and data in general as 1d arrays in imperative programming and only think in those terms rather than the 2d sets they are. I'd wonder how their mind would melt if they'd tried to understand querying for 3d data, eg. temporal databases. Also I fail to see how KQL is any different to SQL, it's pretty much the same thing with the exception that the selection comes afterwards, is that the factor that makes you hate SQL? From the link you posted: StormEvents | where StartTime between (datetime(2007-11-01) .. datetime(2007-12-01)) | where State == "FLORIDA" | count vs FROM StormEvents WHERE StartTime BETWEEN '2007-11-01' AND '2007-12-01' AND State = 'FLORIDA' SELECT count(*) and StormEvents | where DamageCrops > 0 | summarize MaxCropDamage=max(DamageCrops), MinCropDamage=min(DamageCrops), AvgCropDamage=avg(DamageCrops) by EventType | sort by AvgCropDamage vs FROM StormEvents WHERE DamageCrops > 0 SELECT max(DamageCrops) MaxCropDamage, min(DamageCrops) MinCropDamage, avg(DamageCrops) AvgCropDamage GROUP BY EventType ORDER BY AvgCropDamage

bradford 2y ago
To be clear, I don't find SQL difficult to understand. I've used it for 20+ years and I can always get the query to generate my desired output. But I often find that the language is a hindrance, and that I can more efficiently reach my desired output using modern languages. for example, here's the KQL equivalent to the 'average of sums' query: purchases | summarize total_per_customer=sum(amount) by customer | summarize avg(total_per_customer) I find this more elegant, and I'd prefer authoring it over any of the equivalent SQL solutions previously mentioned.

School-Cotton 2y ago

https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/ is another great takedown of SQL.