9 ms·

Every time I see an article such as this it reminds me how much I deeply abhor SQL. It is an ugly language, closer in feel to COBOL than something that can at t

Every time I see an article such as this it reminds me how much I deeply abhor SQL. It is an ugly language, closer in feel to COBOL than something that can at times approach elegance, like Ruby or Scala. With languages like those, you can loo at your work after you are done and be proud of it beyond its purely functional aspect. SQL never elicits a response beyond “the task is finished and it does what I want”, typically with a “finally” in there somewhere.

gigatexal 6y ago
Where you see ugliness I see beauty. I guess it is just what clicks for someone and what doesn’t. But together our strengths make for one really good programmer hence why I like working on teams.
- fiddlerwoaroof 6y ago
  Yeah, my experience is basically the opposite to the GP's as well: for expressing the projection of data I want to work with, there are very few syntaxes I find more elegant than an SQL query. (I have similar thoughts about CSS)
- Scene_Cast2 6y ago
  With SQL as an analysis / insights tool (as opposed to prod or dashboard use), answering one (nontrivial) question leads to follow-up questions that need a lot of query restructuring.
  - gigatexal 6y ago
    When attacking a problem like that I find many queries that might build upon them etc., helpful than one single query to build a report
codeulike 6y ago
SQL isn't really code though, its more like a specification of what you want. Working out how to actually get what you want is then (usually) the engine's problem. And when you're describing precisely what you want from large datasets with lots of columns, its always going to look ugly. I'd wager that if you re-wrote those snippets as Ruby or Scala operating against something tabular like CSV files, with all the joins and aggregates and so on done in code, it would look uglier. * ok sometimes sql (thinking specifically of SELECT statements) gets code-y, e.g. with inline formulas. But generally its more on the specification side.
BoysenberryPi 6y ago
>SQL never elicits a response beyond “the task is finished and it does what I want”... To you this is a bad thing, to me, this is exactly why I like SQL. I don't need to be in love with the elegance and majesty of a language I just need it to work and SQL typically just works and 9 times out of 10 it hooks up nicely with whatever high level language I'm using. And for what it's worth, I've seen quite a few senior level SQL developers write some pythonic SQL queries that would put my regular Python programming to shame.
- wruza 6y ago
  It can also play a devil’s advocate game with your data. Last time I wrote a 4-level join on complex conditions and ctes to find the latest effective non-deleted non-property-deactivated maybe-from-a-group-default-markupped product sale price it worked like charm, really. Until it broke two months later and started multiplicate rows in a frontend. It wasn’t me who designed that schema, if that matters. We already sold that part of business at that time and a poor guy who took that on a support had to repeat my hair pulling investigation into this madness again and how to coerce it into working, while production suffered badly. If a solution was imperative-y, it wouldn’t show such an effect naturally (loop over products, lookup a price by an algo, maybe presort few tables). I would make it like that hands down, but requirements were to use the database as is and no workaround was suitable. Now imagine this error in a financial analysis before an aggregation and no clear human-controlled sum checkpoints. Don’t get me wrong, I see where SQL shines, but I also see where my (your, their, anyone’s) mind shines and where it will lose its traction and prediction abilities. People are fine with some level of declarativeness, but add some more and it turns into hardest puzzles where formulating it correctly is a hardly provable task itself. While that’s true for imperative code, it breaks along the way of your thinking, and not at randomly ‘optimized’ expressions. UB issues in C language group is essentially the same sort of trouble.
WrtCdEvrydy 6y ago
SQL is an abstraction and I wish there was something on top of it supporting loops but that's why we have proper programming languages...
- te_chris 6y ago
  Recursive ctl’s in Postgres?
  - barrkel 6y ago
    Recursive CTEs are great for expressing very poorly performing queries. They might give the right answer, but after you've given up waiting. If you've got tree or graph structures, you're better off denormalizing paths or transitive closures, to reduce the amount of indirection required to answer questions.
- rpedela 6y ago
  One of the best data tools I have ever used: DBT. The big concept is that everything is a SELECT and handles most DDL and DML under the hood. It also provides the ability to add scripting, such as loops, with Jinja. It is primarily meant for OLAP and ELT, but could be used for some OLTP too. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt
- tfehring 6y ago
  You can write loops in (the procedural extensions of) SQL, but IME it's very rare that you'd want to. The bigger issue is the lack of performant abstractions - I copy-paste orders of magnitude more code in SQL than in any other language I've used. There are basically just views and functions, and both can carry substantial (read: multiple orders of magnitude) performance penalties compared to the copy-paste approach, especially when you try to nest or compose them. Materialized views rectify this somewhat but they come with various RDBMS-specific limitations and gotchas.
- eyelidlessness 6y ago
  If you think of SQL as specifying (without implementing) a pure function operating on a set—rather than imperative steps or statements operating on lists—you'll have a lot better luck reasoning about how to use it to operate on data. CTEs, window functions and joins can all be used to accomplish things you'd do in a loop in an imperative language. It really is a different paradigm, in the same way as logic, functional and object-oriented programming are different paradigms. But if you embrace the paradigm you will surely learn how to accomplish whatever you would in a paradigm more familiar to you.
eyelidlessness 6y ago
Have you considered that your distaste for SQL has prevented you from becoming proficient enough with it to appreciate it? I used to share your distaste, but as I've been faced with problems where SQL is not only the objectively best solution but also the other available solutions are either impossible (due to, for instance, memory constraints) or significantly harder to understand and maintain (due to SQL's expressiveness), I've come to learn how to use the tool more effectively and in turn I've come to deeply appreciate it. Codebases I work in continue to use ORMs extensively, but when I need a query of any complexity I'm far more likely to start with raw SQL than to try to make the ORM do what I want. I'm far more likely to "let the database do the work" when I need to do any kind of data analysis or reconciliation. It is very good at what it does, and if you understand how it works it's very simple to do complex things, and quickly. For me, SQL often elicits a response of wonder, and even gratitude for its expressiveness and power. I also suspect your feelings are rooted in syntactic aesthetics (there's little else in common between SQL and COBOL, for example). I can certainly agree that the syntax of SQL is not what I would choose (though I would not look to Ruby or Scala as examples of my preferences). But SQL is not just syntax, it's also a tool with incredibly expressive capabilities for viewing, analyzing and manipulating data effectively.
combatentropy 6y ago
If it helps, you don't really have to type SQL key words in all capitals ;) I stopped years ago. The ironic thing is that the way developers like to deal with data today is more like how they did in the early days of COBOL, to which SQL was an improvement. The first computerized databases were navigational, which just means hierarchical objects. You "navigated" through the data to find the parts that were interesting for a given query, just like with JSON you might loop around through the properties. In 1973 Charles Bachman wrote a book called The Programmer as Navigator. These data structures were insidious, because: (1) you wind up with duplicated data, vulnerable to getting out of sync with itself, and (2) complex queries can get slow. For example, imagine an array of Customer objects. Each has an Orders field, which is an array of Order objects. Each of those has, among other things, fields for the item name and description, and so on. With this structure, it's easy to fetch all the orders of a certain customer, but it's slow and complex for other queries, like the total number of orders for each item. For that, you might duplicate the data into a different structure. It was just like NoSQL, only there was no SQL at the time. It was PreSQL. Programmers are immediately attracted to such data structures because they are amenable to the first few pages you have in mind to build. It's really easy to run those nested objects through a template and output HTML, and it's straightforward to take data from a form and save it as one of these objects. As your application grows and spirals, though, your original data structures become more and more cumbersome and less suited to the new pages you have to make. This was a problem in the 1960s and 70s just as much as today, which is why E. F. Codd wrote his papers, most famously "A Relational Model of Data for Large Shared Data Banks." You might say, relational? Those old navigatorial objects sounded like they had lots of relationships. But it is a popular misconception that Relational here meant the relationships among tables (i.e., foreign keys). Dr. Codd was a mathematician, and he meant the mathematical term relation, which is essentially a grid of values, a table. So they were called relational databases not because you could relate one table to another but because they were databases made up of relations (tables). Tabular data solves the speed problem in navigational data. But now fetching your data is even more tedious, if you have to navigate those tables by hand (Loop through Table A. If the value in cell 12 > 10, then save it to a temporary variable...). But in that very same paper, Codd also proposed a very high-level language for working with the tables. It wasn't called SQL. IBM came up with SQL specifically, after examining Codd's papers (who in fact was a researcher at IBM). Believe me, SQL was an improvement. Codd's original language, called Alpha, was mathematical hieroglyphics. The foundation was solid but the user friendliness was lacking. SQL was an attempt to have the same nature but resemble English instead of Mathematics. But the two pillars, tabular data structures and a high-level query language, were introduced simultaneously and are both equally part of what makes SQL what it is. Which one would you like to remove? Chesterton's Fence comes to mind when watching programmers meet SQL: "In the matter of reforming things, as distinct from deforming them, there is one plain and simple principle; a principle which will probably be called a paradox. There exists in such a case a certain institution or law; let us say, for the sake of simplicity, a fence or gate erected across a road. The more modern type of reformer goes gaily up to it and says, 'I don't see the use of this; let us clear it away.' To which the more intelligent type of reformer will do well to answer: 'If you don't see the use of it, I certainly won't let you clear it away. Go away and think. Then, when you can come back and tell me that you do see the use of it, I may allow you to destroy it.'" --- https://en.wikipedia.org/wiki/Wikipedia:Chesterton%27s_fence https://en.wikipedia.org/wiki/Wikipedia:Chesterton%27s_fence
perl4ever 6y ago
This is a dangerous attitude, because it leads to someone like yourself being assigned a task and doing it in a loop in the procedural language associated with your database, where it could be done in one query that is orders of magnitude faster. And although it may be a matter of opinion, but I find a query that runs one or more orders of magnitude faster than another query to be a thing of beauty.
barrkel 6y ago
If you think in terms of relational algebra, and forget about the SQL syntax, it's easier to see the beauty. There's precious little difference between set.select { |x| x.a > 10 } .group_by { |x| x.c } .map { |c, xs| xs.length } and select count(*) from set where a > 10 group by c except the latter has more scope for optimization.
- emmelaich 6y ago
  This is key! Programmers can easily tie themselves in knots answering these questions with a 'normal' programming language. Whether or not you're actually writing SQL, it's a good conceptual approach.

gigatexal 6y ago

Where you see ugliness I see beauty. I guess it is just what clicks for someone and what doesn’t. But together our strengths make for one really good programmer hence why I like working on teams.

fiddlerwoaroof 6y ago
Yeah, my experience is basically the opposite to the GP's as well: for expressing the projection of data I want to work with, there are very few syntaxes I find more elegant than an SQL query. (I have similar thoughts about CSS)
Scene_Cast2 6y ago
With SQL as an analysis / insights tool (as opposed to prod or dashboard use), answering one (nontrivial) question leads to follow-up questions that need a lot of query restructuring.
- gigatexal 6y ago
  When attacking a problem like that I find many queries that might build upon them etc., helpful than one single query to build a report

codeulike 6y ago

SQL isn't really code though, its more like a specification of what you want. Working out how to actually get what you want is then (usually) the engine's problem. And when you're describing precisely what you want from large datasets with lots of columns, its always going to look ugly. I'd wager that if you re-wrote those snippets as Ruby or Scala operating against something tabular like CSV files, with all the joins and aggregates and so on done in code, it would look uglier. * ok sometimes sql (thinking specifically of SELECT statements) gets code-y, e.g. with inline formulas. But generally its more on the specification side.

BoysenberryPi 6y ago

>SQL never elicits a response beyond “the task is finished and it does what I want”... To you this is a bad thing, to me, this is exactly why I like SQL. I don't need to be in love with the elegance and majesty of a language I just need it to work and SQL typically just works and 9 times out of 10 it hooks up nicely with whatever high level language I'm using. And for what it's worth, I've seen quite a few senior level SQL developers write some pythonic SQL queries that would put my regular Python programming to shame.

wruza 6y ago
It can also play a devil’s advocate game with your data. Last time I wrote a 4-level join on complex conditions and ctes to find the latest effective non-deleted non-property-deactivated maybe-from-a-group-default-markupped product sale price it worked like charm, really. Until it broke two months later and started multiplicate rows in a frontend. It wasn’t me who designed that schema, if that matters. We already sold that part of business at that time and a poor guy who took that on a support had to repeat my hair pulling investigation into this madness again and how to coerce it into working, while production suffered badly. If a solution was imperative-y, it wouldn’t show such an effect naturally (loop over products, lookup a price by an algo, maybe presort few tables). I would make it like that hands down, but requirements were to use the database as is and no workaround was suitable. Now imagine this error in a financial analysis before an aggregation and no clear human-controlled sum checkpoints. Don’t get me wrong, I see where SQL shines, but I also see where my (your, their, anyone’s) mind shines and where it will lose its traction and prediction abilities. People are fine with some level of declarativeness, but add some more and it turns into hardest puzzles where formulating it correctly is a hardly provable task itself. While that’s true for imperative code, it breaks along the way of your thinking, and not at randomly ‘optimized’ expressions. UB issues in C language group is essentially the same sort of trouble.

WrtCdEvrydy 6y ago

SQL is an abstraction and I wish there was something on top of it supporting loops but that's why we have proper programming languages...

te_chris 6y ago
Recursive ctl’s in Postgres?
- barrkel 6y ago
  Recursive CTEs are great for expressing very poorly performing queries. They might give the right answer, but after you've given up waiting. If you've got tree or graph structures, you're better off denormalizing paths or transitive closures, to reduce the amount of indirection required to answer questions.
rpedela 6y ago
One of the best data tools I have ever used: DBT. The big concept is that everything is a SELECT and handles most DDL and DML under the hood. It also provides the ability to add scripting, such as loops, with Jinja. It is primarily meant for OLAP and ELT, but could be used for some OLTP too. https://github.com/fishtown-analytics/dbt https://github.com/fishtown-analytics/dbt
tfehring 6y ago
You can write loops in (the procedural extensions of) SQL, but IME it's very rare that you'd want to. The bigger issue is the lack of performant abstractions - I copy-paste orders of magnitude more code in SQL than in any other language I've used. There are basically just views and functions, and both can carry substantial (read: multiple orders of magnitude) performance penalties compared to the copy-paste approach, especially when you try to nest or compose them. Materialized views rectify this somewhat but they come with various RDBMS-specific limitations and gotchas.
eyelidlessness 6y ago
If you think of SQL as specifying (without implementing) a pure function operating on a set—rather than imperative steps or statements operating on lists—you'll have a lot better luck reasoning about how to use it to operate on data. CTEs, window functions and joins can all be used to accomplish things you'd do in a loop in an imperative language. It really is a different paradigm, in the same way as logic, functional and object-oriented programming are different paradigms. But if you embrace the paradigm you will surely learn how to accomplish whatever you would in a paradigm more familiar to you.

eyelidlessness 6y ago

Have you considered that your distaste for SQL has prevented you from becoming proficient enough with it to appreciate it? I used to share your distaste, but as I've been faced with problems where SQL is not only the objectively best solution but also the other available solutions are either impossible (due to, for instance, memory constraints) or significantly harder to understand and maintain (due to SQL's expressiveness), I've come to learn how to use the tool more effectively and in turn I've come to deeply appreciate it. Codebases I work in continue to use ORMs extensively, but when I need a query of any complexity I'm far more likely to start with raw SQL than to try to make the ORM do what I want. I'm far more likely to "let the database do the work" when I need to do any kind of data analysis or reconciliation. It is very good at what it does, and if you understand how it works it's very simple to do complex things, and quickly. For me, SQL often elicits a response of wonder, and even gratitude for its expressiveness and power. I also suspect your feelings are rooted in syntactic aesthetics (there's little else in common between SQL and COBOL, for example). I can certainly agree that the syntax of SQL is not what I would choose (though I would not look to Ruby or Scala as examples of my preferences). But SQL is not just syntax, it's also a tool with incredibly expressive capabilities for viewing, analyzing and manipulating data effectively.

combatentropy 6y ago

If it helps, you don't really have to type SQL key words in all capitals ;) I stopped years ago. The ironic thing is that the way developers like to deal with data today is more like how they did in the early days of COBOL, to which SQL was an improvement. The first computerized databases were navigational, which just means hierarchical objects. You "navigated" through the data to find the parts that were interesting for a given query, just like with JSON you might loop around through the properties. In 1973 Charles Bachman wrote a book called The Programmer as Navigator. These data structures were insidious, because: (1) you wind up with duplicated data, vulnerable to getting out of sync with itself, and (2) complex queries can get slow. For example, imagine an array of Customer objects. Each has an Orders field, which is an array of Order objects. Each of those has, among other things, fields for the item name and description, and so on. With this structure, it's easy to fetch all the orders of a certain customer, but it's slow and complex for other queries, like the total number of orders for each item. For that, you might duplicate the data into a different structure. It was just like NoSQL, only there was no SQL at the time. It was PreSQL. Programmers are immediately attracted to such data structures because they are amenable to the first few pages you have in mind to build. It's really easy to run those nested objects through a template and output HTML, and it's straightforward to take data from a form and save it as one of these objects. As your application grows and spirals, though, your original data structures become more and more cumbersome and less suited to the new pages you have to make. This was a problem in the 1960s and 70s just as much as today, which is why E. F. Codd wrote his papers, most famously "A Relational Model of Data for Large Shared Data Banks." You might say, relational? Those old navigatorial objects sounded like they had lots of relationships. But it is a popular misconception that Relational here meant the relationships among tables (i.e., foreign keys). Dr. Codd was a mathematician, and he meant the mathematical term relation, which is essentially a grid of values, a table. So they were called relational databases not because you could relate one table to another but because they were databases made up of relations (tables). Tabular data solves the speed problem in navigational data. But now fetching your data is even more tedious, if you have to navigate those tables by hand (Loop through Table A. If the value in cell 12 > 10, then save it to a temporary variable...). But in that very same paper, Codd also proposed a very high-level language for working with the tables. It wasn't called SQL. IBM came up with SQL specifically, after examining Codd's papers (who in fact was a researcher at IBM). Believe me, SQL was an improvement. Codd's original language, called Alpha, was mathematical hieroglyphics. The foundation was solid but the user friendliness was lacking. SQL was an attempt to have the same nature but resemble English instead of Mathematics. But the two pillars, tabular data structures and a high-level query language, were introduced simultaneously and are both equally part of what makes SQL what it is. Which one would you like to remove? Chesterton's Fence comes to mind when watching programmers meet SQL: "In the matter of reforming things, as distinct from deforming them, there is one plain and simple principle; a principle which will probably be called a paradox. There exists in such a case a certain institution or law; let us say, for the sake of simplicity, a fence or gate erected across a road. The more modern type of reformer goes gaily up to it and says, 'I don't see the use of this; let us clear it away.' To which the more intelligent type of reformer will do well to answer: 'If you don't see the use of it, I certainly won't let you clear it away. Go away and think. Then, when you can come back and tell me that you do see the use of it, I may allow you to destroy it.'" --- https://en.wikipedia.org/wiki/Wikipedia:Chesterton%27s_fence https://en.wikipedia.org/wiki/Wikipedia:Chesterton%27s_fence

perl4ever 6y ago

This is a dangerous attitude, because it leads to someone like yourself being assigned a task and doing it in a loop in the procedural language associated with your database, where it could be done in one query that is orders of magnitude faster. And although it may be a matter of opinion, but I find a query that runs one or more orders of magnitude faster than another query to be a thing of beauty.

barrkel 6y ago

If you think in terms of relational algebra, and forget about the SQL syntax, it's easier to see the beauty. There's precious little difference between set.select { |x| x.a > 10 } .group_by { |x| x.c } .map { |c, xs| xs.length } and select count(*) from set where a > 10 group by c except the latter has more scope for optimization.

emmelaich 6y ago
This is key! Programmers can easily tie themselves in knots answering these questions with a 'normal' programming language. Whether or not you're actually writing SQL, it's a good conceptual approach.