17 ms·

Against SQL

• tritiy 5y ago
  Was this written by a nnet? I found it so hard to read as if it author has written it in another language and then used some weird translation engine.
• croes 5y ago
  >what if we want to return the salary too? >the only solution is to change half of the lines in the query How about adding a second subquery for the salary.
  • gerbler 5y ago
    This example seemed wrong to me as well. You can have a subquery, or CTE that returns as many fields as you want and can join on the manager key
    • kinjba11 5y ago
      An additional subquery and a CTE are both restructuring the query significantly, which is the author's point.
      • pjmlp 5y ago
        Well if he is insisting in doing the SQL version of writing everything inside a lambda without modularizing the code, then yeah that is bound to happen.
      • gerbler 5y ago
        There's already a subquery in there. I'm saying this can still be done with one subquery, just in a different place and it returns the desired results.
  • kinjba11 5y ago
    It is a toy example. Perhaps imagine a more realistic subquery that is much longer. Are you going to duplicate a 50 line subquery to get the salary when all you want is one more value? No, you'd probably want to restructure the query with a join or CTE instead. To the author's point, a large change relative to the gain.
    • pjmlp 5y ago
      No, I will write a SQL function for the subquery and call it from the main query.
    • croes 5y ago
      The author explicitly said "the only solution" and that's plain wrong.
• bullen 5y ago
  I thought they where talking about the data not being able to compress, the actual queries don't need to be compressed. But you need to separate the data and the index so you can compress the data while still searching the index, and none of the SQL databases do that because they don't have one file per value (for obvious disk-size reasons). We need to approach the database as files, even add features to our filesystems to accomodate that. In my distributed HTTP/JSON database I use ext4 type small to not run out of inodes before disk space.
  • kinjba11 5y ago
    Should the filesystem matter? I'd assume a database would just allocate a large chunk of disk space and memory and do what it will with that on a layer closer to the metal than files.
    • bullen 5y ago
      Files are closest to the metal. That's where we can improve things most for everyone. But drivers need to be compatible with everything and hardware needs to also be compatible with everything, there is no progress. To really change things we should design hardware (disks and monitors) that have drivers that only work with them. But in the meantime having simple database like functionallity in the filesystem, like being able to list files 100-200 in alpabetical order f.ex without wasting CPU/memory would be interesting (today you need to use multiple commands that list the whole directory)! Also looking at directory compression that does not require you to uncompress the whole directory to get a small subset of files.
• chris_wot 5y ago
  This isn't just a matter of some constant programmer overhead, like SQL queries taking 20% longer to write. 20% longer to write than what alternative? And how is this being measured? And.. am I missing something? By far the most common case for joins is following foreign keys. SQL has no special syntax for this: select foo.id, quux.value from foo, bar, quux where foo.bar_id = bar.id and bar.quux_id = quux.id Why can't this be expressed as an INNER JOIN? And can't some of these subqueries be written using a WHERE EXISTS or a windowing function?
  • erezsh 5y ago
    > Why can't this be expressed as an INNER JOIN? `from foo, bar, quux` is an inner join, it's a shorthand syntax. He's lamenting that he has to keep specifying and matching ids, when the database can figure it out on its own from the foreign keys.
    • pdkl95 5y ago
      They should use NATURAL JOIN if auto-selection of the join keys is that important. I wouldn't recommend relying on that type of automagic behavior because it is very brittle; adding a column to a table might accidentally break existing queries.
      • kinjba11 5y ago
        A natural join selects all matching names which is not the same as what the article is saying. The database already knows about foreign keys. Why do I have to say select * from A a join B b on b.Id = A.OtherId SQL IDEs will auto-suggest that "on b.Id = A.OtherId" because it's the foreign key and could be inferred. That's what you need 99% of the time.
        • cerved 5y ago
          Partly for persistence, I would imagine. The query you write today should function the same tomorrow and a year from now. If you want this implicit behavior, you may use natural joins
• thayne 5y ago
  > So instead the best we can do is add json to the SQL spec and hope that all the databases implement it in a compatible way (they don't). Of course they are incompatible. That's just par for the course when it comes to SQL.
• ltbarcly3 5y ago
  Lots of the examples here are yhe author writing very poor, non idiomatic SQL and then criticizing it. I could write a point by point rebuttal but I'll just pick one point, compressibility: VIEWs.
  • chris_wot 5y ago
    Yeah, some of these subqueries could be fairly easily rewritten.
  • kinjba11 5y ago
    I've been around quite a bit of SQL, and views are great. But they're not exactly as easy as assigning to a variable. If I followed your logic per compressibility I'd have to be creating views - permanent global objects - for almost every query I write. Do you create views every time you need to write some ad-hoc SQL?
    • wruza 5y ago
      And much worse problem is naming them correctly. And maintaining in such global-only space. But just give up, you hit the wall. Many people tried for decades to make this argument, but every time it was raised you’d see this thread full of people who see no problems. There is a lot of powerful, unrepeatable-in-your-lifetime software behind stupidest frontends that you can’t bypass, because most people write only straightforward code with no need for any abstractions beyond what was given to them.
• pjungwir 5y ago
  It would be really cool if databases had an Option<T> type. Then you could remove all the NULLs. Although you can mark a column as NOT NULL, that restriction doesn't "travel": it isn't present for function inputs/outputs, subquery results, etc. Adding it to the type system gives you a lot more mileage. And then joins could be option-aware: an inner join would have outputs matching the input types, but an outer join would have Option outputs (for at least one side). I'm curious how much work has been done on optimizers for Tutorial D or other D variants. It looks way nicer to use, but I wonder if it is easier to stumble into pathological cases.
  • einpoklum 5y ago
    > It would be really cool if databases had an Option<T> type. Then you could remove all the NULLs. A nullable T column _is_ an Option<T> column, with NULL representing "Empty" or "No Value".
    • Teafling 5y ago
      OP explains why this is not the same in all the other sentences of their comment
  • fnord77 5y ago
    you can use COALESCE to remove nulls
    • bmn__ 5y ago
      Explanation for your downvotes: you misunderstood GP's post, he refers to removing the concept of null as it is currently specified from the language, not removing null values from data.
  • zX41ZdbW 5y ago
    This is how it is done in ClickHouse. It has Nullable(T) type. The functions of non-Nullable types will return non-Nullable types (except some specific functions). https://clickhouse.tech/docs/en/sql-reference/data-types/nullable/ https://clickhouse.tech/docs/en/sql-reference/data-types/nul...
• cm2187 5y ago
  One thing I don't understand in SQL is why creating a tmp table is so verbose, why we can't use type inference. There is an internal software where I work where to create a tmp table you just assign the result of the query to a variable. It is so much nicer. So for instance creating a tmp table becomes as simple as the below, no need to declare each columns, to do an insert, to drop the table in the end: @t = select colA, colB from tbl select top 10 * from @t order by colB
  • bvrmn 5y ago
    CREATE TABLE AS can help. As well as CTEs.
  • saurik 5y ago
    You don't have to do all of those things though? Just create table as select (or sometimes select into).
    • cm2187 5y ago
      Thanks. I didn't know this syntax. You still have to drop it in the end but somehow I never ran into this syntax.
      • croes 5y ago
        You don't have to drop temp tables unless you want to use a second select into in the same session. Otherwise temp tables are automatically dropped if the session ends.
      • zbuf 5y ago
        Even better, look at WITH, or CREATE TEMPORARY VIEW AS. Then the planner can make optimisations to the overall query. That is assuming you use your "temporary" only once.
      • deleted 5y ago
        [deleted]
  • lelanthran 5y ago
• bvrmn 5y ago
  > fk_join(foo, 'bar_id', bar, 'quux_id', quux) This example has same amount of semantic entities as in SQL. Also there is USING. Also why author needs a strict modeling over json when one can model in native types? It's a very strange article.
  • hugodrax157 5y ago
    It's also not obvious to me what has been gained from introducing a function which takes 5 unnamed arguments. Without looking at some separate (and therefore possibly wrong) documentation there's no way to guess what it's doing. Writing the SQL may take a bit longer but reading and understanding it is far easier.
    • thom 5y ago
      What other approaches to making SQL composable can we imagine? With functions it seems very simple to extract a WHERE clause into a predicate of some sort. I'm sure even if this exact syntax isn't preferable, being able to reuse join logic would be great. That said, in the case of wanting to abstract out or reuse joins, just write a view, I guess. And I get a lot of mileage in Postgres from just writing functions to abstract out predicates, because it allows you to write things like `select * from order where order.is_complete` instead of `where is_complete(order)`
      • truculent 5y ago
        Forgive me for asking a simplistic question: What’s the advantage for you to write these as functions rather than, say, adding a new column in a view? Would you use the same predicate on multiple tables? Is there a performance benefit?
• progre 5y ago
  I do love SQL and at least where I live (MS SQL Server) it can be made to run amazingly fast if you take some care with your queries and indexes. It's not portable though: as far as I know not a single one of the big sql vendors follows the standards 100% and more importantly, spending some time with one vendor will give you some habits that are sure to not work as well with another (cursor constructs are generally a death blow to performance in tsql but they are the way to do it on oracle for example). So I kind of agree with the author here. But I also feel that maybe they are asking a bit much from SQL. The complaint that complex subqueries are complex... Well then don't use them? I would use WITH constructs in that situation because I find them easier to read but that's beside the point. I think its perfectly fine to pull out multiple result sets from simple queries and then do the complex stuff in your host language.
  • birdyrooster 5y ago
    <message has been deleted>
    • maxrev17 5y ago
      Any more info? ;)
      • birdyrooster 5y ago
        No I just hate mssql
  • kinjba11 5y ago
    > maybe they are asking a bit much from SQL But this article is thought provoking to say the least. It follows the courtroom logic of holding the defendant SQL on trial for as much as possible. And SQL is guilty of a lot of crimes. I do hope GraphQL and similar query languages become more prevalent and standardized, as it seems SQL could really use some stiffer competition.
    • xenomachina 5y ago
• roenxi 5y ago
  One of the elephants in the room with SQL is that it is one of a small number of popular languages that doesn't use function(arg, arg, arg) It is strange that "SELECT a, b, c FROM schema.table" keeps any aura of respectability. That is legitimately outdated syntax, people don't write languages that way any more. It was a 70s era experiment and what was learned from that experiment is that the style has no upside and comes with downsides. It should be 2 or 3 functions, with brackets. With full knowledge of SQL, the successful languages that followed it were C/Python/Java/Javascript that use lots of functions and a smattering of special syntax for control structures.
  • dbsmith83 5y ago
    Comparing SQL to those other languages doesn't really make sense. Their purpose is different. For what SQL does, the syntax makes a lot of sense because it is a completely different paradigm. I think it's dismissive to refer to SQL as merely a 70s experiment. It is used so widely today still
    • roenxi 5y ago
      What advantages do you think SELECT a, b, c FROM d has over even a trivial modernisation like, say, table(d) |> select(a, b, c) ?
      • mongol 5y ago
        Shorter? No use of modifier keys? Just to name two. I think to do a meaningful comparison, more complex expressions should be used, that include joins, group by, order by etc...
        • roenxi 5y ago
          There is pretty overwhelming evidence that using modifier keys is an advantage in this sort of thing. Pretty much every other language - possibly all of them - in common use make heavy use of modifier keys in describing what a computer should be doing. SQL is pretty much the dying breath of the attempts to do without them because the syntax is so bad in practice. Even configuration files typically make use of modifier keys. Losing the explicit link between a function and its arguments is a big deal. Note that even the relational algebra model behind SQL doesn't try to make that sort of silly trade off.
• NavinF 5y ago
  >The usual response to complaints about the lack of union types in sql is that you should use an id column that joins against multiple tables, one for each possible type. >create table json_value(id integer); >create table json_bool(id integer, value bool) >create table json_number(id integer, value double); No, the usual response is "Don't do that!" 99% of the time you either know the data types (so each JSON object becomes a row in a table where the column names are keys) or you don't know the data types and store the whole object as a BLOB I'd be on board with adding unions to SQL, but I doubt I'd use that feature very often.
  • valenterry 5y ago
    But the world just works like that. There are unions everywhere. No matter how it's implemented under the hood, it should really be a first class concept in any language, including SQL.
    • thom 5y ago
      In most languages where unions are a first class concept, are they not generally warned against?
      • bccdee 5y ago
        Not at all. Untagged unions, sure, but no decent modern language has first-class support for untagged unions. First-class tagged unions are a real treat to use.
      • OskarS 5y ago
        You’re probably thinking of C/C++ unions, and they do indeed have quite tricky semantics and are difficult to use correctly. What the article and the other commentators are talking about is more properly called a “sum type”, like Rust’s enums, for instance. Those are different things from C unions. In languages with first class support for sum types, they are used everywhere, it’s an incredibly useful concept.
        • 5y ago
• lincpa 5y ago
  Implement relational data model and programming based on hash-map (NoSQL) https://github.com/linpengcheng/PurefunctionPipelineDataflow/blob/master/doc/relational_model_on_hashmap.md https://github.com/linpengcheng/PurefunctionPipelineDataflow...
• pjmlp 5y ago
  > Why did SQL have to add it to the language spec? Most likely because there isn't cargo for SQL, everyone has to make do with a default install offers, and most big boys databases offer FFI to Java, .NET and C. > This works for data modelling (although it's still clunky because you must try joins against each of the tables at every use site rather than just ask the value which table it refers to) Only if one never learned what views are for, and the various flavours they come in. > By far the most common case for joins is following foreign keys. SQL has no special syntax for this: select foo.id, quux."value" from foo inner join bar on foo.bar_id = bar.id inner join quux on bar.quux_id = quux.id Really, how much time was spent learning SQL before complaining?
  • bokwoon 5y ago
    Given that the author also wrote https://scattered-thoughts.net/writing/select-wat-from-sql/ https://scattered-thoughts.net/writing/select-wat-from-sql/, I don't think he knows nothing about inner joins. I think he was just using that equivalent form to compare it with the terser `foo.bar.quux`. It is pretty strange to compare it with `fk_join(foo, 'bar_id', bar, 'quux_id', quux)` though, because SQL already has the equivalent `foo JOIN bar USING bar_id JOIN quux USING quux_id`.
    • pjmlp 5y ago
      Yeah, it reads like going down the path to write some SQL like parser without actually having used SQL in anger.
  • mickael-kerjean 5y ago
    After years of doing that same technique, in my new job, people would write: SELECT foo.id, quux.value FROM foo, quux, bar WHERE foo.bar_id = bar.id AND bar.quux_id = quux.id I couldn't find anyone telling me the difference between those 2 ways to write a query, do someone know more about this?
    • kthejoker2 5y ago
      There's no performance difference in the engine. The way you have written it (ANSI-89)used to be the only way joins could be written. The second one (ANSI-92) was introduced to allow for composability since the entities being joined and the join condition are next to each other in the code and multiple joins can be generated one after the other. IMO it also enhances developmemt quality of life since you can understand a new-to-you query faster (especially complex ones), you can just comment out a join in one line when testing replacement, cut and paste between queries easier, etc. An SO question on the topic https://stackoverflow.com/questions/334201/why-isnt-sql-ansi-92-standard-better-adopted-over-ansi-89 https://stackoverflow.com/questions/334201/why-isnt-sql-ansi...
• SPBS 5y ago
  SQL is a pretty warty implementation of relational databases (with non-composability being its primary sin IMO), but we're stuck with it at this point. A new querying DSL that fixes all of SQL's flaws is only half the story, getting enough programmers on the planet to buy into it is another half. To do that you'd need this new piece of software to at least be as fast and as battle tested as existing SQL databases. Even the new generation of massively-scalable relational databases stick with some form of SQL instead of inventing a new DSL because of the sheer momentum behind this sorry syntax.
• johndoe42377 5y ago
  Against the fundamental notions of product types, records, unions and intersections, binary relations? Idiots, idiots everywhere.
• benjiweber 5y ago
  > By far the most common case for joins is following foreign keys. SQL has no special syntax for this You can use NATURAL JOIN select * from foo natural join bar Works as long as the keys are named the same. However, a lot of people have a habit of naming keys differently in the two tables.
  • faho 5y ago
    And it also breaks if two non-key columns are named the same. This makes naming key columns differently a defence technique, so you stop people from using natural joins.
    • thom 5y ago
      I generally use a prefix for columns based on the relation name, but preserving the name of keys (so a foreign key to user_id is user_id, not order_user_id etc). You obviously can't use natural joins if you end up with _multiple_ foreign keys with different roles, but generally I find this a better way to live all round. Never having to rename five 'name' columns in some output to make it clear which is which etc.
  • rswail 5y ago
    or ON: select * from foo join bar on (foo.x = bar.y) if the columns have a different name. I tend to write my joins first, then use where clauses as filters. A select * from foo left inner join on (foo.x = bar.y) is semantically equivalent to foo, bar where foo.x = bar.y, but keeping the joins separate from the filters makes the query more clear.
  • vertere 5y ago
    And what if there is more than one way of joining two tables, or a table to itself? A foreign key is effectively a reference to another column, but to de-reference it you have to tell the database which table and column it's a reference to. Every time. Even when this information is already specified in a foreign key constraint. The author is talking about (not) being able to specify the "from" table and column without having specify the "to" table and column (i.e. tell the database how to de-reference it) on each query. A natural join removes the need to specify the columns, but still requires specifying both tables. So besides requiring a de facto single namespace for columns across tables and generally seeming like a footgun, it doesn't achieve the same thing.
• erezsh 5y ago
  I share the author's point of view, which led me to start a new relational programming language that compiles to SQL. It's a way to build on existing databases, like postgres or mysql, with all of their advantages, but improve on many of SQL's limitations. If that sounds interesting, you can find it here: https://github.com/erezsh/Preql https://github.com/erezsh/Preql
  • dvdkon 5y ago
    Looks interesting. I've been thinking about trying this myself and one of my goals has been to create a language that's easily introspectible. I think it's much more important for a query language as opposed to an application language, since you'll want to see what code in the former does without running it for integration into application code. My approach has been to design a very simple (in the lisp sense) syntax, kind of the opposite to SQL where everything is hard-coded into the parser. I've adopted a "pipeline programming"-like approach, where the operations are just (special) functions, which also helps with extensibility. Have you thought about this? From a cursory look, it seems Preql does rely on keywords. Admittedly fewer than SQL, but it also doesn't cover all of its features.
    • erezsh 5y ago
      I don't have a lot of experience writing Lisp-y code, so perhaps I'm speaking from ignorance, but I think there is a reason that syntax never gained huge traction. Imho a syntax that's concise and expressive is important for effective coding. Having operators for the most common operations is just a small complication that yields a big reward. Having said that, the amount of keywords and operators that you see in Preql right now, isn't likely to grow by much. I have the basic and common operators covered, and for the most part, the rest can be done with regular functions. I agree about introspection, which is why in Preql you can ask for the type of any value, list the columns of a table, get the resulting SQL of an expression as a string, and so on. And certainly more can and should be done towards that.
      • dvdkon 5y ago
        Thanks for the reply. I'm happy that others are also tackling the problem of a better query language. My approach isn't actually a full-on Lisp with parentheses and all, rather it's based on a single compound data structure (like Lisp's cons cell, but more like Lua tables). I call it an "arg-tuple", and it's basically a function's arguments in Python, but as a data type (which allows nesting). Add in a simple function call syntax, infix operartors and a "pipeline" operator (like F#'s |>) and you get something like this: from stops | let region :be case(zoneid, :when ("P", "0", "B") :then "Prague", :when ("1", "2") :then "Almost-Prague", :else "Regional") | where lat != 0 && lon != 0 && region != "Prague" | select name, region "from", "let", "where" and "select" are filters, functions that take a query description object and return a new one. "name, region" is just an arg-tuple with two unnamed elements and "case" is a function taking one polymorphic value and a variable number of nested arg-tuples with ":when: and ":then" named elements. Filters can be arranged in any order, unlike SQL, so this would work: from stops | group_by lat, lon | group_by lat | select min(group.lon)
• rawoke083600 5y ago
  I've been thinking about this problem a lot, CRUDs, GraphQL,ORMs, Models etc. Mostly in the "CRUD-Like" environment. I have been thinking about a "client side SQL impl". In most CRUD's we currently have on the backend layers and layers of software with ORMS, frameworks etc, and it all boils down to "Writing/Generating the correct(good-enough) SQL" We now have added stuff like GraphQL, which if you squint hard enough (ok very hard) can be seen as being a SQL alternative(Language to get the actual data). Maybe SQL + "GraphQL-Like" Layers should "evolve" into ONE common "data scripting language" ? Maybe we have something like "ClientSide-SQL" - which can be a subset of ServerSide-SQL ? We need the "TypeScript" of "data-querying" which can be run on the server,client, moon and my device, where one can also only define any "Types" ONCE. Anywhoo - I think there is still a lot to be done, researched and discovered in this section of CS :)
  • vaughan 5y ago
    Yeh, this is lacking right now. GQL makes joins much easier to write than SQL, which is what you want to be using in your components. But GQL is not great for offline-support and caching. You want your frontend to know about how your data relates to each other. Your frontend GQL should query a local SQL db. I'm not sure you need a full SQL implementation on the frontend though, as the data is not going to get all that large to need the optimizations it affords, but it would be nice to be able to use the same queries on your browser DB as your backend DB.
• james_woods 5y ago
  SQL was not made for programmers alone. It has been invented also for not so technical people so that verboseness and overhead is part of the deal. >When Ray and I were designing Sequel in 1974, we thought that the predominant use of the language would be for ad-hoc queries by planners and other professionals whose domain of expertise was not primarily data- base management. We wanted the language to be simple enough that ordinary people could ‘‘walk up and use it’’ with a minimum of training. https://ieeexplore.ieee.org/document/6359709 https://ieeexplore.ieee.org/document/6359709
  • hvocode 5y ago
    This is very important. I personally like old technologies and languages where the designers considered users who had limited technical skills, and most importantly, assumed that those users had no interest or need to improve their technical skills. Removing the assumption that users are willing to increase their technical sophistication forces a designer to think more about what they're designing. Looking at older languages is interesting - for all their warts, they do feel more intentional in their design than modern things that have a clear developer-centric mindset baked in.
    • rswail 5y ago
      It's similar to the discussions around COBOL back in the day. What's interesting is that the primary "end user" language is Excel formulas. Who would have thought a declaratory relational language with matrices would "win" that battle. Any arguments that "users will write their own" languages are basically flawed. Users want results, if there's no alternative, they'll do it themselves, in the simplest, but probably most inefficient way possible.
  • zabzonk 5y ago
    > We wanted the language to be simple enough that ordinary people could ‘‘walk up and use it’’ with a minimum of training. In that case it has been an abject failure. I have been using SQL since the mid 1980s (so pretty much since the start of its widespread adoption) and I have never met "ordinary people" (by which I assume intelligent business-oriented professionals) who could (or wanted to) cope with it. I like it, but the idea of sets does not come naturally to most people (me included). But I once worked with a programmer who had never been exposed to SQL - I leant him an introductory book on it and he came in the next day and said "Oh, of course, it's all sets, isn't it?" and then went on to write some of the most fiendish queries I have ever seen.
• _the_inflator 5y ago
  I feel the pain. As someone who only uses SQL a couple of times a year, I feel that SQL shares the same fate as everything in IT: invented almost 50 years ago, not with today's world in mind, it has been blown up somewhat. Reminds me a bit of JavaScript: everything that can be done in JavaScript, will be done in JavaScript. Like after C followed C++ and here Java and others there will be new DSL and techniques on top of SQL. The article has its merits. Better abstractions for different use cases.
  • exmadscientist 5y ago
    I think the biggest difference between JavaScript and SQL is that there are things that SQL is actually extremely good at. For certain tasks, it really is the best language available, and not in the "least bad" sense but the "why would anyone even try to do this any other way?" sense. To get that level of applicability, of course, you have to make your problem match the form SQL needs. For applications on its home turf, for example a simple inventory system, this can be both easy to do and beneficial (since if you're on SQL's home turf and you want to do something you can't do, there's probably a Very Good Reason). Unfortunately, this is not always easy to do, or even possible at all, and even when it is possible you usually need to know some basic relational algebra. (Tangentially, I am convinced that many of SQL's critics would be quieter if they knew a bit of relational algebra themselves, though I don't think that applies to this article.) As you say, though, trying to make a tool do something it just shouldn't is the road to madness. The article's discussion of JSON in SQL is a pretty decent indicator of how that goes wrong even when it goes right. For further snapshots of the road to madness, the interested reader might examine C++, JavaScript, and a competent psychiatrist. Sometimes it really is time to move on, or at least add on.
    • samatman 5y ago
      Half of the triumph of SQL here is the strength of the relational model, which rests on a solid mathematical foundation and is well and truly the best way to model the vast majority of data domains. The other half is just having no meaningful competition in that one domain. So I agree with the author (and you presumably) that building something better on top of the relational algebra should be a priority for the profession.
• fbn79 5y ago
  Admit have not read the article but has of my personal experience I think the hostility of developers vs SQL came from lack of fundamental formation and experience in declarative programming and full constant every day immersion in imperative programming.
  • xupybd 5y ago
    This article promotes other relational alternatives and query languages.
  • 7952 5y ago
    I was in this state for years, thinking in an imperative way. The change for me was realising that your starting point is every possible combination of the rows in the tables. Just start with that (regardless of how massive) and then filter it down.
  • randomdata 5y ago
    The problem with SQL is the language, not the paradigm.
  • vendiddy 5y ago
    The article isn't against declarative programming. The author alternative declarative solutions throughout the article.
• slx26 5y ago
  I think the problem of this essay is that it's overly technical: only those versed well enough in SQL will really care to read the whole thing, and if they are already at that level, either they accepted that "SQL will get the job done in the end", or they learned to live along it and now even kinda embrace it, and are happy to write about how the examples are very poor and dismiss the critique based on that, when the essay kinda explains it main point pretty well: >> The core message [...] is that there is potentially a huge amount of value to be unlocked by replacing SQL To me, a lot of people defends SQL saying that "perfect is the enemy of good" and that SQL simply works. Not the favourite of anyone, but everyone kinda accepts it. And yeah, it's true. People use SQL because it's good enough, and trying to reinvent the wheel would take more work (individually speaking) than just dealing with SQL as it is right now. For large organizations where the effort could be justified, all your engineers already know SQL anyway, so it's not so great either. But for something so relevant as relational databases, perfect is not the enemy of good. We do deserve better. We generally agree that SQL has many pitfalls, it's not great for any kind of user (for non-technical users, a visual programming language would work well here, more like what Airtable does, closing the bridge between spreadsheet and hardcore database, and for technical users, it does feel unwieldy and quirky). We should be more open to at least consider critiques and proposals for better. We might find out that people, from time to time, are making some good points.
  • b33j0r 5y ago
    To me, SQL looks like something I should be using 79-char punchcards for. Scalable databases are just so difficult that we’re still driving a ‘64 IMPALA Most of this opinion comes from “SQL” being vendor-specific. Is JSON vendor-specific? Is anything else, that we actually use by choice? Mad at you too, Graph DBs, for sending us on another snipe hunt by adding vendor-imposed innovations, because it makes the enterprise marginally profitable. It’s how the world works, I’m still not happy about it. (Disclaimer: I possibly just traveled back to 2002 and said this on slashdot)
    • vbsteven 5y ago
      > Most of this opinion comes from “SQL” being vendor-specific. Is JSON vendor-specific? Is anything else, that we actually use by choice? Two things that come to mind are Markdown with all its flavors and Regex with multiple engines. edit: and to a lesser extent maybe C/C++ compilers and JS engines. edit2: also JVM, Python and Ruby runtimes But both edits describe technologies with an official spec and slightly different implementations. Markdown/Regex are more comparable to SQL because they have vendor-specific syntax.
• KingOfCoders 5y ago
  We're currently moving into a different direction, removing Spark Code to move most of the stuff into BigQuery SQL (which can use structs, one of the points of the article), because it's easier for Data (Engineers|Analysts|Scientists) to write SQL than e.g. Scala.
  • dominotw 5y ago
    Same here. Lots of places are doing this, afaik. shopify from pyspark -> sql https://shopify.engineering/build-production-grade-workflow-sql-modelling https://shopify.engineering/build-production-grade-workflow-...
• danbruc 5y ago
  I wonder how much of the limitation are necessary in order for the query optimizer to have any chance at finding a good execution plan. As you add more and more abstractions and more and more general computations in the middle of your queries, it will probably become harder and harder for the query optimizer to understand what you are actually trying to do and figure out how to do it efficiently. Are you not running the risk that the database will have to blindly scan through entire tables calling your user defined functions as black boxes on each row? I would also guess that we could have a better SQL but I do not think it could and should look anything like a general purpose programming language because otherwise you might get in the way of efficient query execution. Maybe some kind of declarative data flow description with sufficient facilities to name and reuse bits and pieces. And maybe you actually want two languages which SQL with its procedural parts already kind of has. One limited but well optimizable language to actually access the data, one more general language to perform additional processing without having to leave the server. Maybe the real problem of SQL lies mostly in its procedural part and how it interfaces and interacts with the query part.
  • paulmd 5y ago
    I think the criticisms of the article are basically right. SQL sucks in a lot of ways, and the base SQL standard really sucks such that virtually everyone has extended it at least somewhat, but they've all done it in a completely nonstandard way so nothing is portable, and the standard is never officially updated anymore. Oh also it's a massively leaky abstraction and you may have to tune your query to the database anyway, like even just normally, not even something ported, hence hints/etc. There are quite a lot of pretty basic things many programmers would want to do that just are way more stupid than they should be. One that irks me is things like "return me the top 100 items", "return me/join against the most event for item X in a history log", etc) that end up requiring way more shit than they should just because there's no standards-compliant way to say "select first X rows from ... where ... order by ..." or "join first row where .. order by ...". In the case of top 100 you just wrap it in another query, but for more analytical stuff like "join the most recent X for this item" you have to use window functions and the syntax fucking sucks. Since you mention optimization, perhaps it would help to allow the abstraction to be peeled away and write clauses that express imperative commands. Like being able to write that what you want is an "index join table X on index Y", etc. That's sorta what hints do, but roll them into the language in a portable way. It could also allow the query planner to look at it and tell you "that's just not possible and you need to check your keys/indexes/queries/etc", rather than having to run it and guess what it's trying to do. Because I kinda feel that's a lot of the tension for SQL queries (beyond the QOL stuff that everyone knows is stupid). It's a guessing game. You're trying to write queries that will be planned well, but the query planner is not a simple thing that you can easily predict. The closest analogy is probably something like OpenGL/DirectX where you're just guessing at what behaviors might trigger some given heuristic in the driver and there are a lot of unseen weights and flags. There are "equivalent" SQL expressions that are much heavier than another seemingly similar one (like "select from ... join ... where exists (select ...)" vs "select where x in (select ...)". There are operations that are mysteriously expensive because you're missing an index or the query isn't getting planned the way you expect. The suggestion of a "procedural" expression is, I think, also probably correct for some situations. PL/SQL functions are extremely useful, just obnoxious and in some cases arcane to write (as someone who never had formal education in DBA). It would be even nicer if you could have an embedded Python-style thing that let you have "python" syntax with iteration and list comprehensions and shit, that represent DB queries using cursors and shit, and perhaps defer execution until some final "execute" command while transforming it all into a SQL query. Like C#'s LINQ but for Python, but instead of buffering streams of objects transform it into a database query. Transform operations/etc on fields into SQL statements that work on columns, etc. Or java if you will. Imagine a Java Streams API that compiles down to planner ops. I know Hibernate Criteria and JPA exists, but skip that API and express it as streams: iterations and transforms and so on, and map that onto the DB. Being able to build subqueries, then attach them to larger queries, etc. That way they execute in bytecode rather than pl/java.
• johnswas 5y ago
  asdsadsadsdaasd
• tome 5y ago
  The only way out that I can see is to design embedded domain specific languages (EDSLs) that inherit the expressiveness, composability and type safety from the host language. That's what Opaleye and Rel8 (Postgres EDSLs for Haskell do. Haskell is particularly good for this. The query language can be just a monad and therefore users can carry all of their knowledge of monadic programming to writing database queries. This approach doesn't resolve all of the author's complaints but it does solve many. Disclaimer: I'm the author of Opaleye. Rel8 is built on Opaleye. Other relational query EDSLs are available. [1] https://github.com/tomjaguarpaw/haskell-opaleye/ https://github.com/tomjaguarpaw/haskell-opaleye/ [2] https://github.com/circuithub/rel8/ https://github.com/circuithub/rel8/
• sizzler 5y ago
  Anybody can criticise SQL, programming languages, etc. It isn't hard and it doesn't make you better than the people that wrote them. When someone says "this thing that has been working fine for decades needs to be completely replaced" and barely mentions any alternative, I don't think they understand the process involved in replacing things or the terrible (non) proposition they are offering. Increment on SQL, write a translation layer, and see if people adopt it. Maybe 10 years from now your idea will be more popular than standard SQL. Most likely your idea sucks though and you will stay in the easy land of criticising things. The front-end is infinitely more complex than SQL on the backend. I write fairly common web applications and the SQL part is maybe 10% of my time, and very easy. React is where I spend most of my time. I don't have any problem that really needs to be solved. SQL works for me even though it isn't perfect. Any imperfections can most likely be incrementally fixed. I use tagged templates in JavaScript to deal with parameters, composability, and reusability. The fact the the author highlights GraphQL as supposedly the great alternative shows how ridiculous the proposition is. GraphQL does basically nothing. It is 10% of the functionally of SQL.
  • ngrilly 5y ago
    When I compare the codebase of a tool like gqlgen (a GraphQL library in Go) with the code base of PostgreSQL or SQLite, I'd say even 10% is generous.
• js4ever 5y ago
  After a little bit more than 2 decades of coding, SQL is nearly the only thing that was constant in my career. It's a skill I used every working day, I'm pretty sure I will still use it in 20 years. On the other side, tt's very unlikely that the ORM 'du jour' will exist in 3 years from now.
  • exdsq 5y ago
    I think you're right. I hope your name isn't though!
    • js4ever 5y ago
      Haha, in fact I believe js (vanilla, not framework of the week) is probably as durable as SQL
• patkai 5y ago
  Am surprised that in such a long thread nobody mentions RethinkDB.
  • boobsbr 5y ago
    I used RethinkDB for a while, before migrating to Azure Table Storage, and it was nice to use.
  • ngrilly 5y ago
    RethinkDB is mentioned in the article.
• Izkata 5y ago
  The GROUP BY section is odd: > You can use as to name scalar values anywhere they appear. Except in a group by. -- can't name this value > select x2 from foo group by x+1 as x2; ERROR: syntax error at or near "as" LINE 1: select x2 from foo group by x+1 as x2; -- sprinkle some more select on it > select x2 from (select x+1 as x2 from foo) group by x2; ?column? ---------- (0 rows) Looking at that first one I'm just kinda like "well duh, there's nothing special there" - it doesn't work with ORDER BY either, you use that to rename columns (on SELECT) or tables (on FROM and JOIN). And then it goes on to show ways to work around that: > Rather than fix this bizaare oversight, the SQL spec allows a novel form of variable naming - you can refer to a column by using an expression which produces the same parse tree as the one that produced the column. Instead of just... using the renamed column? select x+1 as x2 from foo group by x2;
  • wvenable 5y ago
    Sadly using the renamed column name in a group by or order by doesn't work on all database engines.
    • deleted 5y ago
      [deleted]
  • zX41ZdbW 5y ago
    This is also fixed in ClickHouse - you can set and reuse aliases in any expressions in the query.
• PudgePacket 5y ago
  This is a great article and you can tell the author has deep experience with SQL from the way they speak and the other projects they're involved in. I think many of the comments here are missing the point by saying "Oh you can get around that issue in that example snippet by doing X Y Z". Sure there are workaround for everything if you know the One Weird Trick with these 10 gotchas that I won't tell you about... but that just makes the authors point. We can do better. We deserve better. What could things look like if you could radically alter the SQL language, replace it altogether, or even move layers from databases or applications into each other? Who knows if it will be better or worse, but I'd like to find out.
• Annatar 5y ago
  This web site is amazing: every so often some webshit dipstick who doesn't grok SQL writes an essay bitching about it (instead of learning it!) and it ends up here. Enough with the bitching against SQL and promoting JSON webshit already! If you can't grok SQL, you should consider a career completely unrelated to computers! What the hell has this industry come down to!
  • rswail 5y ago
    Downvoted for the personal attack on the author, but in a spirit of using this as a learning opportunity... The writer obviously groks SQL. SQL as a language expressing relational algebra sucks. It was a way of trying to make relational algebra "grokable" by end users, in the same way that COBOL was a way to make "programming" grokable by end users. The author's point is nothing to do with JSON, it's about having a column type that contains structured data. In "pure" SQL you have to extract that structured data into another table or a hack like "subtables" or something. The "natural" form of a lot of data these days is JSON. Having a JSON data type is only the start of being able to query it cleanly in SQL. For SQL to cleanly support JSON, it needs the ability to handle the lack of data types in JSON, and the fact that each JSON element can be an untagged union of potential types.
    • Annatar 5y ago
      "Downvoted for the personal attack on the author"... Yes, well, I've had it with charlatans in this industry; in every other profession, professionals take on responsibility for their actions, just in information technology and computer industry, almost everybody hacks, builds crap and takes responsibility for nothing. "We never point fingers at people, just at technology..." Yeah well guess what, that technology was "invented" by people, usually one person. When there is personal responsibility in the game, maybe the people responsible will stop reinventing the wheel and start producing small, fast, simple, high quality software... If you spout nonsense, I will call you out personally for it, as I do not suffer fools, since my patience has worn out: enough with the kindergarten for adults already, that has to stop as well! Take responsibility for what you spout, bear the consequences. Enough with crap technology and constantly reinventing the wheel! There is no need for JSON in the first place: a flat UNIX text file, delimited by colons or pipes and streamed as an input to another filter or the final program does just fine. So the premise that it has to be JSON is already broken, and anyone who doesn't even question that premise has already missed the elephant in the room. Ergo, if the author is even considering solving the problem so that it would fit into JSON, then the author lacks insight, and if he or she lack insight, then they are not equipped to solve the problem, which in the end, quite unsurprisingly, they do not solve. Why am I so much against JSON? Because not only is the format ill thought out, but constructing a reliable, robust parser for it is a technological nightmare. How then is this a better solution? How is it a more robust one? How is it a simpler one? The answer to all three questions is quite obvious, at least to me: it is none of those things. If somebody who is supposed to be a professional with that level of insight completely misses or accepts that JSON isn't simple, that it isn't robust, and that therefore it is not the better solution which does not better the industry and computer science as a whole, then that person has no business writing, let alone designing anybody's software.
• trapatsas 5y ago
  There are two types of people in the world. The ones that are pro-SQL and the ones that don’t understand how SQL works
  • Kuinox 5y ago
    I invite you to read the article.
• LeonB 5y ago
  I would like a typescript style transpiler tool chain for testing out new language features that are seamlessly transpiled down to existing sql. Once that’s in place I don’t know which features I’d want first… but there’s a lot of them!
• ngrilly 5y ago
  A more pragmatic view in that article: https://blog.nelhage.com/post/some-opinionated-sql-takes/ https://blog.nelhage.com/post/some-opinionated-sql-takes/
  • quietbritishjim 5y ago
    Thanks, that's a great article. It has just the right balance of some interesting things I didn't know with enough things I agree with that I believe it! I agree with the desire for a data-based language, rather than text-based one as SQL is. A classic example of this is MongoDB: you can add a new filter by just adding a new entry to a dict in Python or object in JS etc. I think 99% of the reason MongoDB was successful, at least in the early days, was because of its data based API. (Polite request to all: please don't reply to this comment with pros/cons of MongoDB except it's query language.) I especially agree with the point about having to trick query planners into using the indices you wrote. I get that sometimes it's nice to let the database engine cleverly choose the best strategy (dynamically building queries with a data-based API would be a case in point). But in other situations you'll have carefully designed the tables and indices around one or more specific queries, and then it's frustrating not being able to directly express that design in the code. I don't have any experience with live migration of production databases (thankfully!) so that was interesting, especially the conclusion that MySQL is best for this, which I didn't expect. The idea of separating out the type system into lower-level "storage types" and higher-level "semantic types" was also food for thought.
    • ngrilly 5y ago
      I agree that a programmatic API semantically equivalent to SQL – for example like the JSON-based MongoDB API you mentioned – would go a long way making things easier for application developers and removing the need for ORMs (which I consistently avoid). F1, Google's SQL database, uses Protocol Buffer in an interesting way: https://storage.googleapis.com/pub-tools-public-publication-data/pdf/41344.pdf https://storage.googleapis.com/pub-tools-public-publication-...
    • glogla 5y ago
      SQL is not a great API but its a great human interface. JSON based query language is much better API but terrible human interface. So depending on your objectives, it might make things better or worse. MySQL is strange choice but think I understand why the author picked it - from his other critique he seems to look at databases as a building block of hyper-scaleable applications, not as a tool for humans to do often-ad hoc things with data. I would never recommend MySQL for "business data" - it had and possibly still has way too many footguns with regards to number behavior, character encodings and Unicode, date and timestamp handling, and so on - hell, it doesn't have a proper MERGE and it only got CTEs in the latest version. But if you're using it as persistence store that barely more than key-value store, why not? I have no problem believing the author that that kind of use is more common.
• ComodoHacker 5y ago
  >By far the most common case for joins is following foreign keys. SQL has no special syntax for this That's because there can be more than one FK relationship between the same two tables. For example, if we model a binary tree, there could be references to left, right and parent nodes.
• smitty1e 5y ago
  > First, while SQL allows user-defined types, it doesn't have any concept of a union type. Isn't a union type essentially a de-normalized field? This seems like attacking arithmetic operators for their lousy character string support. Weren't XML databases (briefly) a (marketing) thing some decades back? One idea might be to have everyone integrate jq[1] into their database engines. My understanding is that one can make the JSON do back flips with jq. Then we can move to complaining about queries that appear to have been written in Klingon instead of boring ol' SQueaL. [1] https://stedolan.github.io/jq/manual/ https://stedolan.github.io/jq/manual/
  • deleted 5y ago
    [deleted]
  • masklinn 5y ago
    > Isn't a union type essentially a de-normalized field? No? You have to denormalize to emulate unions when they're missing. Sum types are a fundamental category of types, that SQL only supports product types is a problem you have to work around.
    • smitty1e 5y ago
      See mannykannot's reply. The argument for union types seems to get weak when one asks: how do we index their components? Because there seems little middle ground between needing discrete fields and safely just parking the data as a memo field and deferring the management to the application. Unix win by letting the system utilities specialize. SQL need not be "one language to rule them all".
      • masklinn 5y ago
        > See mannykannot's reply. Their answer clearly, explicitly, assumes a C-style `union`. Despite the essay literally using a proper sum type as example. > The argument for union types seems to get weak when one asks: how do we index their components? With the system creating partial indices under the cover? I fail to see what's complicated about it. > SQL need not be "one language to rule them all". SQL has "won" the relational battle and is literally the only way to query relational databases. Any time SQL is unable to do the job and you have to move that job to application code, you're making the schema less reliable and less of a source of truth, because parts of the schema's information have to be embedded in each application instead. That doesn't seem desirable to me, unless you assert SQL should just be a trivial data storage and retrieval interface, which it has not been… possibly ever, but at the very least since the introduction of window functions.
• asavinov 5y ago
  One alternative to SQL (type of thinking) is Column-SQL [1] which is based on a new data model. This model is relies on two equal constructs: sets (tables) and functions (columns). It is opposed to the relational algebra which is based on only sets and set operations. One benefit of Column-SQL is that it does not use joins and group-by for connectivity and aggregation, respectively, which are known to be quite difficult to understand and error prone in use. Instead, many typical data processing patterns are implemented by defining new columns: link columns instead of join, and aggregate columns instead of group-by. More details about "Why functions and column-orientation" (as opposed to sets) can be found in [2]. Shortly, problems with set-orientation and SQL are because producing sets is not what we frequently need - we need new columns and not new table. And hence applying set operations is a kind of workaround due the absence of column operations. This approach is implemented in the Prosto data processing toolkit [0] and Column-SQL[1] is a syntactic way to define its operations. [0] https://github.com/asavinov/prosto https://github.com/asavinov/prosto Prosto is a data processing toolkit - an alternative to map-reduce and join-groupby [1] https://prosto.readthedocs.io/en/latest/text/column-sql.html https://prosto.readthedocs.io/en/latest/text/column-sql.html Column-SQL (work in progress) [2] https://prosto.readthedocs.io/en/latest/text/why.html https://prosto.readthedocs.io/en/latest/text/why.html Why functions and column-orientation?
• cletus 5y ago
  The complexity of the SQL spec is a fair point. Inconsistencies between implementations has some merit but in practice doesn't really matter (eg how often do you really replace your database?). A lot of the rest of it reads like the author started with this conclusion and then went looking for justification. Example: the author states it's hard to return more than one column with a correlated subquery. That's what with clauses or join with queries are for. The author later mentions with statements so is aware of them. As for JSON, I honestly don't think anybody needs that. Either return a JSON blob (generally bad idea IMHO) or you need to construct it in code. The example of join verbosity has issues too. First, abbreviated syntax would need to express what kind of join to do (eg inner vs outer). Second, I find this fairly natural: SELECT ... FROM a JOIN b ON a.id = b.a_id LEFT OUTER JOIN c ON b.id = c.b_id The author instead used this syntax: SELECT FROM a, b, c WHERE a.id = b.a_id AND b.id = c.b_id The also leaves the join type unexpressed. In some SQLs you say: AND b.id = c.b_id (+) But that's kind of ugly and old-fashioned. The first syntax is preferable and clear. On "compressability", SQL has this. They're called views. GraphQL has a notion called fragments that SQL doesn't. This is one of those things that sounds like a good idea but probably isn't. It makes queries much harder to read and I've seen this reach the point where a fragment is so widely used changing it is expensive (eg generated code) and removing anything is impossible. Plus a lot of users end up querying things they don't need. Poor optimization and error messages of with clauses aren't really an argument against SQL. They're an argument against particular implementations. Extracting an anonymous query into a WITH clause should be a no-op to performance for any half-decent query optimizer/executor. Writing extensions (eg functions) should be discouraged. It's harder to deploy and debug and the last thing you want is a badly written C function crashing your database. Years ago we also had stored procedures (eg Oracle PL/SQL) and nobody does that anymore because it's terrible. You don't want that. There's a lot in there about pathological corner cases that I honestly don't really care about. I do agree that ORMs are generally a disaster. Lastly, it's worth noting that SQL unless a lot of alternatives has a solid theoretical basis and that is relational algebra. SQL wasn't created in a vacuum. SQL is just a way to express those constructs. I will say that SQL got the order of clauses wrong whereas LINQ got this right. SQL should actually look more like this: FROM a WHERE a.foo = 'bar' SELECT id, col1, col2 Honestly though, SQL just isn't "broken". That's why it's endured so long despite the NoSQL fad and various efforts to replace it.
• historyloop 5y ago
  SQL isn't immutable, it's always evolving. I find it awkward some of the arguments in the article read like "you couldn't do that before CTE was added". But it WAS added, so? If you want to fix SQL, contribute to the next version of the standard, or provide example by implementing what you want to see out there.
• latte 5y ago
  SQL and the relational model mostly works well and it's probably not practical to redo the enormous amount of work that was invested in SQL and its implementations and extensions. As someone who frequently used SQL for analytics and less frequently for app development, I would gladly use a language that would transparently translate to SQL while adding some syntactic niceties, like Coffeescript did to JS: - Join / subquery / CTE shortcuts for common use cases (e. g. for the FK lookups that are mentioned in the article) - More flexible treatment of whitespace (e. g. allow trailing commas, allow reordering of clauses etc.) And for the language to be usable, it would probably need: - First class support for some extended SQL syntax commonly used in practice (e.g. Postgres's additions) - integration with console tools (e.g. psql), common libraries (e.g. pandas, psycopg2) and schema introspection tools - editor support / syntax highlighting. It would probably be good to model the syntax of that language on some DSL-friendly general purpose language (like Scala, Kotlin or Ruby).
  • laurent123456 5y ago
    Isn't it what query builders, such as Knex.js, are for?
    • latte 5y ago
      Basically it would be ideal to have a query builder that is easily integrated with shells and notebooks (so that it can be used outside the context of writing programs in a specific languages) and that is accepted across the community.
• thinkr42 5y ago
  Though verbose and somewhat strange at times, one thing I love about SQL is that the query statements read like a set definition from set theory. That declarative nature is pretty powerful IMO, sure there are hiccups but it is a different way of thinking.
  • WillDaSilva 5y ago
    The (mostly) declarative nature of SQL is not something the post is criticizing. You could have a good declarative language for relational database that doesn't suffer from the things the post criticizes.
  • christophilus 5y ago
    I agree. I also agree with the post. I’m a big fan of sql and write a lot of it by hand. TFA makes a lot of excellent points to which I could add quite a few more. If we ever do get a replacement, I hope it retains the declarative set theory approach of SQL while addressing the warts.
• deleted 5y ago
  [deleted]
• JoelJacobson 5y ago
  I suggest using the fact foreign keys are constraints with unique names, and using these names to explicitly specify what column(s) to join between the two foreign key tables. In PostgreSQL [2], foreign key contraint names only need to be unique per table, which allows using the foreign table "as is" as the constraint name, which allows for nice short names. In other databases, the names will just need to be a little longer. Given this schema: CREATE TABLE baz ( id integer NOT NULL, PRIMARY KEY (id) ); CREATE TABLE bar ( id integer NOT NULL, baz_id integer, PRIMARY KEY (id), CONSTRAINT baz FOREIGN KEY (baz_id) REFERENCES baz ); CREATE TABLE foo ( id integer NOT NULL, bar_id integer, PRIMARY KEY (id), CONSTRAINT bar FOREIGN KEY (bar_id) REFERENCES bar ); We could write a normal SQL query like this: SELECT bar.id AS bar_id, baz.id AS baz_id FROM foo JOIN bar ON bar.id = foo.bar_id LEFT JOIN baz ON baz.id = bar.baz_id WHERE foo.id = 123 I suggest adding a new binary operator, allowed anywhere where a table name is expected, taking the table alias to join from as left operand, and the name of the foreign kery contraint to follow as the right operand. Perhaps "->" could be used for this purpose, since it's currently not used by the SQL spec in the FROM clause. This would allow rewriting the above query into this: SELECT bar.id AS bar_id, baz.id AS baz_id FROM foo JOIN foo->bar LEFT JOIN bar->baz WHERE foo.id = 123 Where e.g. "foo->bar" means: follow the foreign key constraint named "bar" on the table/alias "foo" If the same join type is desired for multiple joins, another idea is to allow chaining the operator: SELECT bar.id AS bar_id, baz.id AS baz_id FROM foo LEFT JOIN foo->bar->baz WHERE foo.id = 123 Which would cause both joins to be left joins. SELECT bar.id AS bar_id, baz.id AS baz_id FROM foo LEFT JOIN foo->bar->baz WHERE foo.id = 123 [1] https://scattered-thoughts.net/writing/against-sql/ https://scattered-thoughts.net/writing/against-sql/ [2] https://www.postgresql.org/ https://www.postgresql.org/
  • Seb-C 5y ago
    This is interesting, I did not know this syntax. Alternatively there are still the NATURAL JOIN and USING syntaxes that have been standard like forever.
    • JoelJacobson 5y ago
      I should clarify this syntax is only an idea, it's not implemented yet in any vendor nor part of the SQL standard, yet. I think it would be a nice feature to add to the SQL standard.
• nojvek 5y ago
  I agree with the Author. SQL is not a great query language. Almost every decently sized app I have written I have needed some sort of a query compiler so I don’t have to deal with nuances. Also agree that GraphQL is a pretty fantastic language for working with graphs. And that relational databases are essentially graphs. Hasura is neat.
• jackbravo 5y ago
  Here in hacker news it was posted this article about the story of SQL biggest rival, QUEL, which is pretty related: https://www.holistics.io/blog/quel-vs-sql/ https://www.holistics.io/blog/quel-vs-sql/? It is not that we didn't try to replace it, but just as other comments have said, SQL was good enough, and already has the biggest mind share.
• jmull 5y ago
  So what? Complaining about SQL is the easy part. Actually, it's the first skill most new SQL developers truly master. I'm waiting for the viable alternative. There are a lot (a LOT) of solutions that handle some cases, but inevitably you need to get into the SQL anyway because that's the DBMS' native API (and now you also need to fight your way through the abstraction, oh and since there are a LOT of solutions a different one is used every chance someone gets, so you need to relearn how to fight through the abstraction all the time). I doubt it's going to change. There's actually no significant reason. SQL (actually, the set of mutually incompatible SQL variants) is thoroughly entrenched and a small problem... that is, it's rarely the dominant reason a project/product succeeds or fails, or takes too long, or becomes unmaintainable, etc.
• kthejoker2 5y ago
  As someone from the analytics side who's been working with SQL for 30 years (First Choice, remember that?) (but who also wrote a fair share of ORM boilerplate), I find these debates fascinating .. but also kind of trivial, in the sense that SQL has a lot of other pros and cons that app devs rarely consider. Truly it is blind men evaluating an elephant. Given SQL's roots as a human-friendly declarative interface, the only thing I see completely replacing it in the near future is a Copilot-style neural implant where you just think of the results you want.
• twodave 5y ago
  We have a general rule on our team that complex SQL is a code smell. In our project complex queries are usually an indication of a poor design. Anything SQL that can be made simpler via dynamic generation (which is safe as long as you use proper parameters for user inputs) is favored over creating logical branches in queries. Anything that can be processed further quickly in memory in the app (mapping operations, string ops, ordering/filtering predictably small data sets, etc.) we tend to offload from SQL into something more suitable. And we tend to solve a class of problem in our data layer and reuse those generalized patterns heavily. This makes our codebase predictable even when dealing with unfamiliar subject matter. Of course there are always places where some complex query is necessary (especially when building reports), but if it’s status quo then you’re doing something wrong—-it’s only a matter of time until you end up with a performance nightmare on your hands.
  • karmakaze 5y ago
    > complex queries are usually an indication of a poor design. Can you give an illustrative example of one. I suspect that framing it this way biases designs away from 'poor ones that use complex queries' into one that foregoes other good aspects such as normalization. Sometimes the best design uses a complex query for something other than a report. Design is not something that should be done by application of dogma and avoiding smells.
    • twodave 5y ago
      No, but by treating a SQL server as a way of storing and retrieving data efficiently FIRST then the times when a complex query is actually necessary tend to stand out better. In reality most tables and queries start out simple enough, and poor schema choices are usually accompanied by poor architectural choices. It can be painful to come up with a decent migration scheme when the business needs change, especially if there’s fear/pressure involved, but often that’s going to be better than trying to keep the data layer the same/similar and shoehorning in data to represent new scenarios. This is what leads to a fragmented design IMO and allows the schema to diverge from the actual goal of efficient data storage/retrieval.
  • 5y ago
• gumby 5y ago
  SQL is a COBOL-era language — though there are 15 years between them, language theory was quite rudimentary at that time. But it exists and is adequate. And, as Gabriel’s famous essay says, Worse is Better.
• bob1029 5y ago
  I feel like most frustrations with SQL boil down to fighting against a shitty schema. When you are sitting in a properly normalized database, it is a lot easier to write joins and views such that you can compose higher order queries on top. If you are doing any sort of self-joins or other recursive/case madness, the SQL itself is typically not the problem. Whoever sat down with the business experts on day 1 in that conference room probably got the relational model wrong and they are ultimately to blame for your suffering. If you have an opportunity to start over on a schema, don't try to do it in the database the first few times. Build it in excel and kick it around with the stakeholders for a few weeks. Once 100% of the participants are comfortable and understand why things are structured (related) the way they are, you can then proceed with the prototype implementation. Achieving 3NF or better is usually a fundamental requirement for ensuring any meaningfully-complex schema doesn't go off the rails over time. Only after you get it correct (facts/types/relations) should you even think about what performance issues might arise from what you just modeled. Premature optimization is how you end up screwing yourself really badly 99% of the time. Model it correctly, then consider an optimization pass if performance cannot be reconciled with basic indexing or application-level batching/caching.
  • batty_alex 5y ago
    Yeah, this has been my experience, too. Sql and RDBs are a lot less frustrating when someone takes the time to actually do some design and planning. Personal experience incoming: At startups, it's usually a mess because hiring someone who knows databases seems to always come so late in the game. At bigger corporations, well, hopefully the developers and database people get along and talk - otherwise, one of those teams is going to be a bottleneck. > Model it correctly, then consider an optimization pass if performance cannot be reconciled with basic indexing or application-level batching/caching So true. This also extends into general purpose languages, everything is so much easier when you take the time to model things correctly.
  • balfirevic 5y ago
    > I feel like most frustrations with SQL boil down to fighting against a shitty schema. Which one of the frustrations from the article boils down to fighting against shitty schema?
• mcv 5y ago
  Having worked a lot with neo4j, a graph DB, over the past two years, I must say I'm surprised how rigid and inexpressive SQL is by comparison. We started our project with a SQL database, but some queries would be 10 or more lines with multiple joins. Very hard to read. Once we switched to neo4j, the same query was a single, easily readable line. SQL is very well-established, but it's also old, and it shows its age. It's kinda weird how easily we jump from one programming language to another, and yet we can't seem to move on from our main relational query language.
• tonymet 5y ago
  Relational Tables & SQL should be just one storage mechanism for your app. What if someone told you: build an app, but only use b-trees? Then you start complaining about all the shortcomings of b-trees. The point is that you have relational tables / SQL, along with many other persistence , storage & indexing mechanisms: distributed hashtables, queues, lists, etc. All the apps I've worked on have mixed SQL with all of the other data structures with consistent or inconsistent replication among them depending on the use-case. One way to manage this is a key-value online tier and a relational offline tier, with inconsistent replication online to offline. SQL & RDMBS are very powerful, but like any tool, limited to the designated use case. Stop trying to make it do everything.
• jandrewrogers 5y ago
  Another subtle issue with SQL is that it tacitly assumes a great deal about the internal architecture of the database engine implementing it. SQL is designed to be easy to implement for the way SQL databases worked in the 1990s. Unfortunately, modern high-end databases today have radically different internal architectures, are capable of much greater internal expressivity as a minimum, and are designed to support data models as first-class citizens that weren't even on the radar in the 1990s. Patching the first-class capabilities of modern database kernels into the SQL language, such as generalized recursion, can often be awkward or require non-standard syntax or behaviors that defeat easy optimization. The DDL has similar issues, particularly around its concept of what an "index" can look like under the hood or the myriad ways in which data can be organized. I've used and even written SQL databases for much of my career. SQL is pretty satisfactory for what it was designed to do. I view SQL like classic inheritance-based OOP; it works well for the problem domains for which it was originally designed, but is poor for efficiently expressing problem domains that are better expressed in a composition-based or functional way. Yet it worked so well in its original domain that we try to apply it everywhere. The diversity of data models and the kinds of operations we want to do with them today is far greater than was considered when SQL crystallized into its current form. The limitation of most nominal SQL replacements I've seen is that they commit the same sin of SQL originally: overfitting for a problem domain that the designer was most interested in. There is an appetite for a really good SQL replacement if done well, and in principle anything SQL can do could be directly translated into a new language for compatibility.
• AtNightWeCode 5y ago
  Not that bad workarounds. The N + 1 problem is usually not a big issue with ORM:s but one should the check the generated code I think. Seen far worse written code. (Well, if you don't do SELECT *...) I have other issues with SQL: The linear way resources are needed with the amount of data but no built in way to handle it. That integer ids are way overused and basically locking every database to a specific environment. The index tweaking. The workarounds for write speed. The fact that you can do anything in SQL and people know it.
• mlinksva 5y ago
  Inspiring article (I love SQL, but it's also frustrating). My only wish would be to see the criticisms used as a checklist to evaluate SQL improvements or new query languages. EdgeQL, indirectly linked at the end of the article, looks at a glance like it might score well. EdgeDB's blog post [1] criticizing SQL and introducing EdgeQL seems to cover the same concepts (inexpressive, incompressible, non-porous) with slightly differing language in some cases (e.g.. system cohesion for porousness). Noticed after posting this comment that there's a post today about EdgeQL. [2] [1] https://www.edgedb.com/blog/we-can-do-better-than-sql https://www.edgedb.com/blog/we-can-do-better-than-sql [2] https://news.ycombinator.com/item?id=27793398 https://news.ycombinator.com/item?id=27793398
• Crash0v3rid3 5y ago
  I’m always asked how I am so good at sql. I laugh given I know how crappy my sql skills are. It’s really that I just know our schema so well I can formulate a decent enough query to extract what I need. Knowing your schema design is just as important as knowing sql.
• historyloop 5y ago
  Did the author forget that we had this entire "NoSQL" period that lasted well over a decade, where SQL was the worst thing ever, and everyone kept coming with the superior alternatives to SQL? What happened? What happened is many of those NoSQL products started adding SQL syntax and features to their databases, others disappears, and yet others specialized into niches where they don't compete with SQL RDBMS at all, which remains the primary database paradigm and language. So those are the facts. If someone still believes they know better, put up or shut up.
  • boxed 5y ago
    Those were afair not even relational systems. So doesn't apply here. The article clearly states so in the very first sentence.
• zug_zug 5y ago
  I guess I don't get it. It uses a bunch of big sounding technical terms ("inexpressive" "non-pourous") to criticize sql, but when I actually read it this seems to be mostly miniscule details that could be added trivially to an SQL engine if there was demand. For example, joining natively on foreign key seems like a trivial convenience, I'm not sure it proves any larger point to me, many people prefer code that is more verbose and clear about what it does than magical/implicit. Another example complaint hidden behind a ominous-sounding word boils down to "Using a table expression inside a scalar expression is generally not possible, unless the table expression returns only 1 column and either a) the table expression is guaranteed to return at most 1 row or b) your usage fits into one of the hard-coded patterns such as exists." Uh, great I've never needed to do that in my career, and so if you care so much make a PR, but suggesting that SQL itself is somehow the problem is laughable. It would be orders of magnitude more effort to try to standardize the industry on a new query language than to patch table expressions. I can scarcely imagine what a productivity loss it would be to the industry of SQL standardization were dropped, it would be much worse than python 2/3 debacle. Also "incompressible" - Sounds like the author doesn't use views/materialized-views. Finally the "fragile" example is just the author writing a bad query. The example here is performant and less fragile: https://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-partition-by-another-column-in-mys https://stackoverflow.com/questions/612231/how-can-i-select-... etc.
• chubot 5y ago
  Amazing critique! It has a wealth of examples -- I liked the "N+1 query bugs" and "feral concurrency" links (stuff I've experienced but didn't have a name for). ---- The comparison of SQL vs. flink windowing ("kernel space" vs "user space") reminds me of the this 2013 call to change the design of browsers feaetures: https://extensiblewebmanifesto.org/ https://extensiblewebmanifesto.org/ Basically there's a lot of stuff implemented stuff in the C++ layer of the browser that's impossible to emulate in JavaScript, and that's a bad design. It is indeed alarming how much syntax SQL has. It reminds me of shell, where every string manipulation function like stripping a prefix has custom syntax like ${x//pat/replace} or ${x%%prefix}. Oil (https://www.oilshell.org/ https://www.oilshell.org/) will simply have functions for this, like x.sub('pat', 'replace'). ---- I also wonder if the author has worked with dplyr and the tidyverse at all? He mentions Pandas, but IMO it's a clunkier imitation of those ideas (and I'm saying that as a Python programmer). Tidy data was my intro to the design of dplyr: http://vita.had.co.nz/papers/tidy-data.html http://vita.had.co.nz/papers/tidy-data.html It's very inspired by the relational model, but it has a few more operations like "gather" and "spread" which turn "long" format into "wide" format and vice versa. It has a clean and expressive API: https://www.rstudio.com/wp-content/uploads/2015/02/data-wrangling-cheatsheet.pdf https://www.rstudio.com/wp-content/uploads/2015/02/data-wran... It composes like regular code, so you can write stuff like: bin_sizes %>% select(c(host_label, path, num_bytes)) %>% left_join(bytecode_size, by = c('host_label')) %>% mutate(native_code_size = num_bytes - bytecode_size) -> sizes Good comparison of the relational model and data frames: Is a Dataframe Just a Table? https://plateau-workshop.org/assets/papers-2019/10.pdf https://plateau-workshop.org/assets/papers-2019/10.pdf I link all of these in What is a Data Frame? (In Python, R, and SQL) https://www.oilshell.org/blog/2018/11/30.html https://www.oilshell.org/blog/2018/11/30.html
• justshowpost 5y ago
  It's all about the background. For HLL and even basic programmers grasping SQL poses little-to-no challenges. Some are even falling in love with SQL despite some minor inconsistencies and prolix wordy verbosity and asking for writing more SQL. In contrast, users of, for example, the lingo where object minus object equals NaN are terrified when suddenly exposed to type zoo like https://www.postgresql.org/docs/9.5/datatype.html https://www.postgresql.org/docs/9.5/datatype.html (Disclaimer: a relatively randomly chosen example, neither endorsement nor preference of particular RDBMS/dialect). And let's keep in mind what types above form a structures and these structures getting manipulated en mass as intrinsically unordered sets (which are data types too!). That is, a leap from barely existing concept of data types to circa 30% of DDL/DML keeps scripters out of SQL. So the reason behind that endless «SQL bad» teeth gnashing turns out to be very simple.
• lenkite 5y ago
  Been coding for over a decade and written thousands of simple and complex queries and I have always thought SQL sucked but was too afraid to ever express that opinion since everyone else believes it is the best thing since sliced bread. Quite relieved that some experts feel the same way.
• MilkyFloor 5y ago
  Things are going crazy in the whole IT field now and as I business owner I am looking more and more at things like https://doit.software/services/staff-augmentation https://doit.software/services/staff-augmentation . Wish to get back to just straight-forward programming tasks but when you are in charge, God, things are getting way too complicated.