7 ms·

I’m curious as to why you find sql to be clunky, I find it extremely on point in most cases. I mean, how would you write a SELECT that was better than: SELECT

• naasking 5y ago
  > I mean, how would you write a SELECT that was better than: SELECT whatever FROM thisplace? Haskell's comprehensions. C#'s LINQ. F#'s query providers. SQL is really not that good actually. It had (and still has) all sorts of limitations that eventually led to new syntax, and has many quirks that require all sorts of workarounds.
• faho 5y ago
  One very very simple fix is to mention the table first: FROM this SELECT whatever This already allows autocomplete for the attributes to work, and has an easier mental model - you think about the tables, then you think about their attributes. It also matches relational algebra better, where you'd do the projection (picking the attributes you want) at the end. But anyway, simple cases being simple doesn't mean the language isn't horrible for more complex ones. One thing I always complain about is join clauses making it easy to do the wrong thing (NATURAL JOIN) and annoying to do the correct thing (joining on the defined foreign keys).
  • skeeter2020 5y ago
    This is my biggest, high-level thing too; in the syntax we use SELECT to mean a projection and FROM the selection.
  • JoelJacobson 5y ago
    > and annoying to do the correct thing (joining on the defined foreign keys). Maybe you’ve seen this thread already; a proposal with some alternatives on how to improve the situation for joining in foreign key columns, but in case not here is the link: https://news.ycombinator.com/item?id=29739147 https://news.ycombinator.com/item?id=29739147
    • faho 5y ago
      That proposal unfortunately requires you to name the foreign key constraint, which is quite unergonomic. E.g. instead of the wrong `FROM a NATURAL JOIN b` you would use the correct `FROM a JOIN FOREIGN a.foo_fkey`, which not only needs that second name but now also loses the immediate naming of b. So e.g. autocomplete would have to look up the foreign key constraint to find out the second table. And it's still longer and harder to use than the natural join! Most databases have one foreign key from a given table to another given table, and that simple case should be made easy to use.
      • JoelJacobson 5y ago
        There are multiple alternative syntaxes suggested in the proposal, one of them doesn’t use foreign key names, but instead the foreign key column names, similar to USING (), but without the ambiguity: https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cfea8aaa#join-table_name-foreign-fk_column_name--from-referencing_alias--to-referenced_alias https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cf...
  • Izkata 5y ago
    People have long joked about "yoda conditionals" ("if 5 == x", for example, instead of "if x == 5"), and flipping it in that order is the same thing: SELECT first is the same as "Get the fork from the drawer", where the flipped order actually sounds like something Yoda would say.
    • Sankozi 5y ago
      It is more like starting the recipe with "take sauce from point 6 and pour it over meat from point 10". Very few people has problem with trivial queries like "SELECT x FROM y", but when query contains multiple joins or inner queries then having select at the beginning is visibly problematic.
      • to11mtm 5y ago
        What I did to get better at 'big' queries was start by writing out things like inner queries as CTEs, Table Parameters, or (if in oracle, lol) refCursors. That's (sometimes!) less performant than the one big query, but you can then refactor into a single query if you so choose. Yeah, it's slow goings at first, but you get pretty good at SQL in the process.
    • goto11 5y ago
      The question is if the syntax correspond to the logical order of operations. In the query "SELECT foo + 2 FROM bar WHERE baz ORDER BY foo", the logical order is actually "FROM foo WHERE baz SELECT foo + 2 ORDER BY wawa" because of how each clause depends on the previous. The SQL syntax is neither the logical order nor the direct reverse - it is just a random jumble.
      • ryathal 5y ago
        SQL syntax is designed for reading, which is the majority of coding time. What gets selected is generally the thing most cared about so it goes first, where stuff comes from is the next most important so you get FROM and JOIN. Filtering and aggregation are next, because they are often hinted at by the select list anyway, then sorting is the least important so it comes at the end.
  • momirlan 5y ago
    You're familiar with CTE, right ?
    • faho 5y ago
      Yes, you can work around some inadequacies of SQL by bolting more features on top. That doesn't mean that the basic design of SQL isn't awkward.
  • Twisell 5y ago
    I recently had to train my new junior to level his SQL skills and I found this resource pretty helpful to make sense of this mess. https://learnsql.com/blog/sql-order-of-operations/ https://learnsql.com/blog/sql-order-of-operations/ However I do also see the point of "SELECT first" just like a header you can infer the output data structure of a sub-expression without necessarily dive into the meat of it. It require a some brain training, but once you get there it oftentimes easier to navigate 100+ lines scripts by jumping from header to header (usually organized as CTE to make the code cleaner).
  • kodemager 5y ago
    > This already allows autocomplete for the attributes to work. So does the other way around in several SQL engines. If you write something a long the lines of select x.ID, y.NAME from bla.bla as x join hum.hum as y on x.ID = y.FK in msSQL you’ll get autocomplete on x. and y.. You’re right that it’s more intuitive to write the from first of course.
    • irishsultan 5y ago
      You can't possibly get autocompletion on x. and y. for that first select if you didn't write that from clause yet (or at least the autocompletion you'd get would not be tailored to those tables).
      • faho 5y ago
        If you add the table name you could, and that's what "x." here is. So yes, you can autocomplete SELECT employee.Na<TAB> to "employee.Name", but it requires you to type the table name "employee." first. But with the from-first style you can autocomplete even bare column names - you know you have "name" (possibly even "employee.name" and "supervisor.name") and "employeeID".
        • irishsultan 5y ago
          Except that the table name isn't necessarily going to be that x. If you are matching employees with their managers then you have two employees tables in that expression so you have to work with aliases. At which point autocompletion breaks down.
  • DemocracyFTW 5y ago
    It would perhaps be a good thing if one could write SQL clauses in their logical ordering; as [1] explains: * The FROM clause: First, all data sources are defined and joined * The WHERE clause: Then, data is filtered as early as possible * The CONNECT BY clause: Then, data is traversed iteratively or recursively, to produce new tuples * The GROUP BY clause: Then, data is reduced to groups, possibly producing new tuples if grouping functions like ROLLUP(), CUBE(), GROUPING SETS() are used * The HAVING clause: Then, data is filtered again * The SELECT clause: Only now, the projection is evaluated. In case of a SELECT DISTINCT statement, data is further reduced to remove duplicates * The UNION clause: Optionally, the above is repeated for several UNION-connected subqueries. Unless this is a UNION ALL clause, data is further reduced to remove duplicates * The ORDER BY clause: Now, all remaining tuples are ordered * The LIMIT clause: Then, a paginating view is created for the ordered tuples * The FOR clause: Transformation to XML or JSON * The FOR UPDATE clause: Finally, pessimistic locking is applied [1] https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/select-lexical-vs-logical-order/ https://www.jooq.org/doc/latest/manual/sql-building/sql-stat...
• Xelbair 5y ago
  1) it is committee driven therefore changes come slowly 2) adding new functionality requires addition of new keywords 3) you cannot define new keywords from SQL 4) despite standardization, each implementation differs this article summarizes it pretty well, while i do not agree with everything in it it points out flaws pretty well. https://www.scattered-thoughts.net/writing/against-sql/ https://www.scattered-thoughts.net/writing/against-sql/
  • JoelJacobson 5y ago
    > 2) adding new functionality requires addition of new keywords There are reserved keywords and unreserved keywords. The latter can be used as table/column/function/etc names, and don’t cause any trouble. New syntax can be invented by reusing existing reserved keywords, and introducing new unreserved keywords in places where they can’t be misinterpreted. Not saying the problem you describe isn’t a problem, just that it’s slightly more complicated and not as bad as one might think when reading your comment.
• Sankozi 5y ago
  "SELECT whatever FROM thisplace" is trivial to improve and could be for example thisplace[whatever]. With joins it gets more complex but still SQL could allow using foreign keys and having SELECT at the end. You could get at least something like: "FROM Invoice i JOIN i.customer c SELECT c.name, i.number" instead of "SELECT c.name, i.number FROM Invoice i JOIN i.customer c ON i.CustomerId = c.CustomerId"
  • gadders 5y ago
    Isn't a "WHERE" clause more intuitive than that? SELECT c.name, i.number FROM Invoice i, Customer c where i.CustomerId=c.CustomerID"
    • tomnipotent 5y ago
      I prefer JOIN clauses because it makes it easier to reason about the underlying implementation (hash/sort-merge joins) than thinking about cartesian products. It's also much harder to screw up the ON predicate and actually cause a cross join.
• Traubenfuchs 5y ago
  I love SQL, but I also love being a contrarian. thisplace.whatever There you go.
  • dmurray 5y ago
    This looks a lot worse if thisplace is a long subquery itself.
• goto11 5y ago
  SQL syntax assumes queries have operations in a certain order - join, filter, group, filter again, project. What if you want to join after a grouping? What if you want to filter after a project? What if you want to group over a projection? You will have to use the clunky subquery syntax or WITH-clauses. Compare to LINQ-syntax in C#, where you can just chain the operations however you want. Another issue is that you can't reuse expressions. If you have an expression in a projection, you will have to repeat the same expression in filters and grouping. This leads to error-prone copy-pasting of expression or more convoluted syntax using subqueries.
  • oblio 5y ago
    And for expression reuse, they're so close with aliases. lots-of-bla-bla-bla-bla as short-name But later on you can only refer to short-name from very specific places, as you mention. So 80% of the time you're forced to go lots-of-bla-bla-bla-bla over and over and over again. Snatching defeat from the jaws of victory.
    • deleted 5y ago
      [deleted]
  • christophilus 5y ago
    I really, really miss LINQ, having moved from C# to Ruby, then Node and Go. LINQ is as close to absolute perfection as I’ve seen in a concept.
• Cthulhu_ 5y ago
  That's the first / starter use case though, SQL can get a bit crazy once you get into enterprise spaces - stored procedures, funky datatypes, auditing & history features like temporal tables, hundreds, thousands of tables and a similar amount of columns, naming & organizing things, etc. Thankfully, most people will never have to deal with any of that, myself included. The biggest databases I've had to deal with were very relatable - one about books & authors, another about football and historic results. The other biggest database is one I'm working with and building right now, it's a DB for an installation of an application managing tons of configurations, a lot of domain specific terms. The existing database is not normalized of course, and uses a column with semicolon-separated-values as an alternative to foreign keys. Sigh. Current challenge is to implement history, so that a user can revert to previous versions. I'll probably end up implementing temporal tables in sqlite.
  • kodemager 5y ago
    I build and operated an employee database in accordance to the Danish OIO model for years, I even sat on a comity to define either models within the OIO model set for the public service. These days I work with millions of entries from solar production. I’ve never had to use complex SQL more than one time. You use tools like SSIS or APIs on top of it to get and store the data. I know you “can” create a lot of stored procedures and views, but as I’ve already said, you really, really shouldn’t do that exactly because it’s so terrible to work with for so many people. Honestly though, SQL with an Odata api on top of it is one of my favorite ways of storing and retrieving data. If you have to actually transform the data, you do it with SSIS or similar tools that are much more efficient top level layers that are also testable and reusable. But to each their own I guess. The join logic never bothered me much, and that seems to be an issue for a lot of people here.
    • jsyolo 5y ago
      True, keep the database as dumb as possible IMO. I have converted 200 line SQL queries into 30 lines of SQL plus 20 lines of code for OLTP. OLAP is a different beast though and SQL can get nasty.
• tibiapejagala 5y ago
  The problem with sql is what happens when you fall off the SELECT FROM JOIN WHERE GROUP BY HAVING ORDER BY LIMIT cliff. The simple stuff in sql reads like English, but for that case ORM would generate a pretty efficient query anyway. The complex stuff in sql looks terrible in my experience and ORM bail out quickly. Once you can’t get the result with a simple SELECT then sql stops being declarative. Instead of writing what you want to get, you write something like a postmodern poem while having a stroke, just to convince postgres benevolent spirits to give you something almost right. Complex UPDATEs and DELETEs with joins are even worse. Also lack of syntax sugar doesn’t help. SELECT list could support something like “t1.* EXCEPT col1, col2”. Maybe JOIN ON foreign key would be nice. IS DISTINCT FROM for sane null comparisons looks terrible. Aliases for reusing complicated statements are really limited. Upsert syntax is painful. Window functions are so powerful that I can’t really complain about them though. We use a lot of sql for business logic, but some code I have to reread from zero every time I need it. Maybe we modeled our data wrong or there is some inherent complexity you can’t avoid, but I mostly blame sql the language. Unfortunately I have no idea how it could be improved. Anyway I think the sql cliff is real. Once you take a step outside the happy path prepare for a headache. For me sql definitely is in some local maxima, after all I use it every day at work.
  • hiptobecubic 5y ago
    I have used those features that you say "Would be nice to have." I didn't realize they weren't ubiquitous. I agree they are excellent.
  • oblio 5y ago
    The biggest thing is... SQL is not reusable, period. Why don't we have SQL libraries? I know that data models are kind of special snowflakes, but some models pop up over and over and over again and code reuse is always 0 with SQL. To give you an example of a common problem, SLAs or the like for teams with regular business hours. A team has to respond to a request within N hours. To calculate that I need to take into account 8 business hours per day, excluding weekends, excluding holidays (ideally localized holidays), etc. It's a nightmare with SQL. It's precisely the kind of thing you want in a library. Plus, obviously, standard SQL doesn't have a way to share and distribute any libraries, even if they were made. It's pre-C in terms of stuff like that.
• da39a3ee 5y ago
  There’s some good discussion of the deficiencies of SQL here: https://opensource.googleblog.com/2021/04/logica-organizing-your-data-queries.html https://opensource.googleblog.com/2021/04/logica-organizing-... > Good programming is about creating small, understandable, reusable pieces of logic that can be tested, given names, and organized into packages which can later be used to construct more useful pieces of logic. SQL resists this workflow. Although you can encapsulate certain repeated computations into views and functions, the syntax and support for these can vary among implementations, the notions of packages and imports are generally nonexistent, and higher-level constructions (e.g. passing a function to a function) are impossible.
• deepsun 5y ago
  I'd flip the FROM and SELECT, just like in UPDATE and DELETE commands.
• samus 5y ago
  A problem is that SQL does not cleanly map to what the DBMS does to execute it. For simple queries, this is exactly the point. For more complex queries, the SELECT-FROM-WHERE straightjacket feels quite restrictive though. The abstraction really hurts when you have to optimize slow queries and convince the optimizer to do it the right way. Entering the query plan (essentially, the annotated AST of an expression of relational algrebra) would often be helpful. Also, SQL is ultimately text. This makes it very cumbersome to build tools that dynamically assemble queries, like ORMs or customized search dialogs, and to insert parameters. Parser performance impact overall DBMS performance quite a bit, and it would be useful to reduce overhead there too.
  • uvdn7 5y ago
    Yep, every abstraction introduced has added cost, in some cases. The fastest way of executing a program is to build special hardware with the perfect state changes of a set of transistors — I am not joking.
• vaughan 5y ago
  Try a 3-way M-M join. Or recursive CTE's for hierarchical data. It always looks clean with simple examples. Data these days is much more nested and inter-related.