9 ms·
Half a century of SQL
- parpfish 2y agoi love SQL but it was really hard to get started for two reasons: - it's hard to start tinkering unless you have a database to write queries against. most tutorials never really get past the most rudimentary hello-world-esque stuff because they're limited by the size and complexity of the example data. - imperative programming teaches you to be very precise and explicit about what exactly should happen, so it feels very weird to start working with something declarative where you can just trust the computer to figure it out and do things well automatically.
- giancarlostoro 2y agoWhich is why I think we should always teach SQL basics with SQLite. You can just share the db file as-is. You can probably find dozens of GUIs and even web GUIs for SQLite that you could load a db into.
- hnthrowaway0328 2y agoIt's not that hard. You can use any SQL tutorial site for practice. If you want real life data, find the stackoverflow data dump on archive.org and install a PostgreSQL instance to play with it. IMO the biggest issue of SQL is that it brings bread and butter for the BI/DA people, who are so close to the business that they usually don't care about code quality and such. It's not the fault of the language, but the fault of erroneous team structure.
- pmarreck 2y agoSQLite is good for this.
- fifilura 2y agoI use to say that you have learned SQL the moment you forgot everything about C/C++. Imperative programming is a strong habit to kick. You just have to let go. And once you have you will see things from a very different perspective. Also in other programming you'll start seeing the map/fold/groupby/dict patterns everywhere.
- parpfish 2y agoevery once in a while i start thinking "it'd be really nice to build a declarative language to do this", but then I realize that: - that just means that I'm bored and would rather be writing out the logic to handle a more general/abstract representation of the problem - whenever I come across a DSL that somebody else made to "make a really simple declarative-like interface", i hate it and wish that they didn't do it
- fifilura 2y agoWas your point that that goes for SQL? One nifty thing with SQL is that you get all the threading/remote processing for free without having to think about it. That can be pretty hairy to write yourself.
- simonw 2y agoI've been building SQL tutorials against my Datasette web UI for SQLite as a solution for getting people started without having to install anything: https://datasette.io/tutorials/learn-sql https://datasette.io/tutorials/learn-sql
- refset 2y agoIntegrating live code editors within docs and tutorials is great. Another example of this I saw recently using SQLite (compiled to Wasm) in the browser: https://docs.sqlitecloud.io/docs/sqlite https://docs.sqlitecloud.io/docs/sqlite And if you ever want something similar for more general backend APIs (without relying on Wasm or the browser to run the software), https://codapi.org/ https://codapi.org/ looks very slick e.g. as demonstrated in https://antonz.org/sql-upsert/ https://antonz.org/sql-upsert/ - discussed on HN previously [0] Inspired by https://www.db-fiddle.com/ https://www.db-fiddle.com/ my colleagues ended up building a fairly bespoke setup for XTDB's docs (XTDB doesn't yet compile to Wasm) shortly before I came across Codapi, although our requirements were even more particular, e.g. see https://docs.xtdb.com/tutorials/financial-usecase/time-in-finance.html https://docs.xtdb.com/tutorials/financial-usecase/time-in-fi... - the backend here is https://github.com/xtdb/xt-fiddle https://github.com/xtdb/xt-fiddle which runs purely on top of Lambda Snapstart, and embedded within docs based on Astro's Starlight [1] and Web Components [0] https://news.ycombinator.com/item?id=38663717 https://news.ycombinator.com/item?id=38663717 [1] https://starlight.astro.build/ https://starlight.astro.build/
- itishappy 2y agoAgreed, but data is so easy to find online! My breakthrough was that Python comes with SQLite built-in. Pointing DuckDB at a CSV file looks even easier. https://docs.python.org/3/library/sqlite3.html https://docs.python.org/3/library/sqlite3.html https://duckdb.org/docs/data/csv/overview.html https://duckdb.org/docs/data/csv/overview.html
- parpfish 2y agosure, you can find data and spin up your own db but you still need a tutorial to a) explain what the data/tables are and b) guide you through some things to build. for most programming languages you can make some sort of little widget or itneractive program that lends itself well to learning via curious exploration. SQL does not lend itself to that because you don't really make something 'fun'. You just make more tables. People aren't going to naturally come up with the kind of boring-but-challenging things that you use SQL for like "come up with a daily aggregate of pageviews, but exclude users that had account status X in the previous 30 days OR if they have a special status set in this other table as of the date of the pageview. And make sure that days with 0 page views show up with 0 pageviews and are not just missing from the table". Also, it's very easy to make queries that complete and look correct but give wrong or incomplete results. You've not going to notice that from just running queries in self-directed practice.
- edmundsauto 2y agoFrom a slightly different perspective, some people like these tasks :). That's part of the mindset of being a good data engineer, for example - I rarely have a problem coming up with my own set of queries to try out a new database or dataset. But I'm also not really interested in a lot of SWE-specific things. Maybe this just isn't something you really want to dive into. That's ok!
- deleted 2y ago[deleted]
- AlisdairO 2y ago
- craigkerstiens 2y agoThis is very much why we built the Postgres playground, which has Postgres embedded in your browser with guided tutorials - https://www.crunchydata.com/developers/tutorials https://www.crunchydata.com/developers/tutorials
- kccqzy 2y agoI think subqueries are a useful feature that doesn't get taught often to beginners. Using subqueries it's much easier to think clearly and intuitively. Even imperative programmers who don't have a ton of functional programming experience still understand nested function calls `f(g(h()))`. With this you don't even have to know when the different parts of a SELECT are applied, and you don't even have to know things like HAVING.
- NortySpock 2y agoI have found Common Table Expressions -- which are slightly easier for the developer to re-order in the query -- to be even more readable than subqueries.
- cwbriscoe 2y agoYes, CTE's are a much more readable than subqueries IMO. I can't really explain it but it just makes queries easier to build and read to me.
- fifilura 2y agoTwo benefits: 1) The flow is clearer: take this, then transform this, then filter this 2) CTEs makes it easy to debug parts. You just change what you select from on the last line. Number 2 is the kicker IMO
- disgruntledphd2 2y agoAnd this is why you should always, always have a `final` CTE and the last line should be `select * from final` so that you can easily change this. We use a LOT of SQL at $current_dayjob and I almost always end up adding this to the queries. Clearly I need to do a better job of evangelising my approach.
- deleted 2y ago[deleted]
- therobots927 2y agoActually with R it’s very easy. Just install the sqldf package. Then you can read in a CSV or whatever format you’re using to R (even if you don’t know R, ChatGPT could probably write the code for you). Once the data is inside an R data frame you can query it directly with sqldf like: sqldf(“select count(*) from csv_dataframe”)
- xarope 2y ago1) sqlite is so easy to start with, I'm not sure what you mean to "hard to start tinkering"? 2) I'm not sure about your personal experience, but having worked with make files during C programming days, I think you get used to declarative state stuff pretty fast!
- SoftTalker 2y agoThe imperative vs. declarative was also a toe-stubber for me at first. It ties in with SQL being set-oriented, which is not natural for a lot of imperative-trained developers. If you find yourself looping over a rows with a cursor you are probably doing it wrong, or at least suboptimally.
- bradford 2y agoI view SQL right now similar to the way I viewed C++ in early 2000s: I hate it, but there's little point in complaining because it's so ubiquitous. More robust criticism is provided here (https://carlineng.com/?postid=sql-critique#blog https://carlineng.com/?postid=sql-critique#blog), which pulls on an interview here (https://www.red-gate.com/simple-talk/opinion/opinion-pieces/chris-date-and-the-relational-model/ https://www.red-gate.com/simple-talk/opinion/opinion-pieces/...) The quote I usually drag out is from Chris Date, who helped pioneer relational DBs: "At the same time, I have to say too that we didn’t realize how truly awful SQL was or would turn out to be (note that it’s much worse now than it was then, though it was pretty bad right from the outset)." As an example of a language that does it better, I think kusto-query-language (KQL, https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/ https://learn.microsoft.com/en-us/azure/data-explorer/kusto/...) has been a dream to work with. (disclaimer, Kusto is a Microsoft product, and I'm a Microsoft employee).
- wvenable 2y agoI kind of disagree; the problem with SQL is that fundamentally it's actually pretty good. So alternatives either tend to be too radical (throwing out the baby with the bathwater) or simply not enough of an improvement to gain any momentum. I feel like rational database querying is effectively solved and there's little point in re-litigating it. But still I'd be happy to switch to the perfect replacement if someone develops it.
- vkazanov 2y agoWhat does "good" mean in this context? SQL is not modular, most features are highly context-dependent and there numerous handguns. Sql might be ok for trivial things, as in OLTP that programmers tend to work with. But anything even slightly more advanced is... not nice. And the standard is unique in its uselessness. The underlying relational algebra model is brilliant thought.
- rangerelf 2y ago> SQL is not modular, most features are highly context-dependent... Examples? > SQL might be OK for trivial things, as in OLTP... What is the threshold for triviality? I've seen understandable fairly complex queries, but they're not mind-twisters by any means; if you know what you need, and understand your data, >>and are not a layperson regarding databases<< it's doable without much sweat. > But anything even slightly more advanced is ... not nice Again, what is the threshold, or at least what is your threshold, for triviality vs. non-trivial? You say it's "unique in its uselessness" but "the underlying relational algebra model is brilliant", can you explain a bit further what you mean by that?
- mrtimo 2y ago> SQL is great at expressing simple needs very well, but in more advanced cases, it can get "mind-twistingly complicated" even for a mathematically included brain. I have found Malloy[1] to be easier to read and write, even for really complex queries. Malloy complies to SQL. As an example check out [2]. Malloy can query .csv files directly (enabled by duckdb). You can also join .csv files. This makes getting started a breeze for beginners, and is a boon for data scientists. Much easier than Pandas in many cases. With the visualization capabilities it has built in, Malloy sort-of competes with Tableau and PowerBI. Most people only write SQL to get data out of databases, for these people, Malloy is an interesting tool to consider. [1] https://www.malloydata.dev/ https://www.malloydata.dev/ [2] https://docs.malloydata.dev/blog/2023-10-26-malloy-bump-chart/ https://docs.malloydata.dev/blog/2023-10-26-malloy-bump-char... Not affiliated with Malloy, I've just played with it for a while and been impressed. It's also MIT licensed.
- myaccountonhn 2y agoThere’s also recutils for those that have simpler data storage needs.
- mikpanko 2y agoMalloy is great. Why do you think it is not taking off if it is a clear significant improvement on SQL and even compiles to it?
- bdcravens 2y agoLimited support https://docs.malloydata.dev/documentation/ https://docs.malloydata.dev/documentation/ "Malloy currently works with SQL databases BigQuery, Postgres, and querying Parquet and CSV via DuckDB."
- emmanueloga_ 2y agoLanguages like Malloy or PRQL require some upfront investment to learn and setup. Additionally, I think both Malloy and PRQL are query oriented, so you still need to learn a decent amount of SQL to interact with your database (for updates). I'm guessing most people would rather spend effort in solving their immediate problems with SQL rather than bet on a newer and less known technology, even if has a lot of promise.
- DaiPlusPlus 2y agoI want to know why there hasn’t been much innovation in the less-sexy parts of ISO SQL. My go-to for this is to point-out how SQL-92 defined the same small set of constraints that we have today (PK, FK, UNIQUE, CHECK, and that’s pretty-much it). It’s been 30+ years and the expressiveness of SQL DDL constraints for data-modelling is completely unchanged. So far, only Postgres has extended it with EXCLUDE constraints; what I’d love to have is a “Non-unique foreign key” constraint and its inverse: a “NOT EXISTS” constraint - these alone would go far. Another huge need is for safe-and-sound data-structure invariants - it’s almost impossible to correctly store a linked-list on a table without running until concurrency issues.
- SpicyLemonZest 2y agoActive development in data systems has focused heavily on larger datasets where complex global state isn't practical to enforce on write. Some systems don't even enforce their PK or FK constraints.
- codeulike 2y agoYeah thats it, I've never seen contraints used that much in the real world. Unique keys but thats about it. Clever database-level contraints usually come back and bite you in the arse when you're trying to do some large scale change or upgrade or fix. And if you're building an app you usually want the validation of input to be done as close to the user as possible (like in the UI), not relying on a round trip to the database and handling an exception. And some might say 'well have validation in both places' but then you've got a load of complex stuff to keep in sync which will be a pain in the arse if it gets out of sync (if your db rules differ slightly from your ui rules)
- tanelpoder 2y agoFWIW, Oracle now has data domains that allow more complex constraints, including JSON schema based ones: https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
- deleted 2y ago[deleted]
- rqtwteye 2y agoI think SQL is pretty good but I would like to see a tighter integration with the application code and the database. Like managing the database source in the repository, integrate the database into tests easier, maybe express queries in the application language instead of SQL. You can do all of these to to some degree with things like ORM and LINQ but the database still feels very separate from the rest of the project. I think SQL as a language could also be improved a lot to make it easier to understand. It looks and feels like FORTRAN 77.
- kccqzy 2y agoYou are really discussing an improvement in programming languages rather than in databases. Innovation in programming languages themselves tend to be in languages like Haskell which give you much more abstraction power. You should take a look at Haskell beam https://haskell-beam.github.io/beam/tutorials/tutorial1/ https://haskell-beam.github.io/beam/tutorials/tutorial1/ or Esqueleto https://github.com/bitemyapp/esqueleto https://github.com/bitemyapp/esqueleto and you will find that these libraries achieve much tighter integration between the database and the rest of the language. I'm especially fond of Beam: the way to define data types and tables, the way to write queries just feel incredibly intuitive to me. > managing the database source in the repository, integrate the database into tests easier These are much easier problems to solve. Many people have already solved it.
- maweki 2y agoAs I see it, we haven't really made any progress in terms of impedance mismatch. Otherwise, jooq is a fine addition to the Java world, as it generates a type-safe DSL from your ddl file where every statically correct jooq/Java statement is also a statically correct SQL query.
- dventimihasura 2y agoI see it differently. In the 25 years I've been working in this industry I see a welcome trend toward doing more in the database, such that the impedance mismatch dissipates. This is from a low-water mark 20 years ago or so, when most Java developers I knew kept the database at arm's length, insisting it met them on their terms rather than the other way around. https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f... https://supabase.com/ https://supabase.com/ One way to eliminate the Java-SQL impedance (for example) mismatch is to delete Java altogether, along with JOOQ, Hibernate, and Spring (for example).
- quercusa 2y agoStonebraker said Oracle had a head start and was achieving strong growth using "sales tactics I would not condone." Well, I never...
- dventimi 2y agoIt helps to have friends in high places. https://news.ycombinator.com/item?id=32596903 https://news.ycombinator.com/item?id=32596903
- pphysch 2y agoSQL-the-standard is one of the great mistakes of computing, a bad abstraction of Titanic proportions. The standard should have been abandoned long ago in favor of the actually-existing reference implementations like PostgreSQL. "Standard SQL is good and portable and worth spending enormous resources supporting" -- statements dreamed up by the utterly deranged
- dventimi 2y agoI regard this as hyperbolic.
- 392 2y agoNo one has ever said that since no database implements the whole standard or limits themselves to just the standard. jOOQ blog has some good writings on this. I am glad at least that the standard gave us a sizeable common subset over half a century rather than everybody doing their own thing from day one.
- pphysch 2y ago> I am glad at least that the standard gave us a sizeable common subset over half a century rather than everybody doing their own thing from day one. There are tons of popular programming languages and they share paradigms, good ideas and syntactic similarities. Query languages ought to be the same. Dialects work in natural language because interpretability, syntax and semantics are all fuzzy. Dialects in computer languages is an anti-pattern because syntax and grammar must be precise and deterministic. You are either writing Java or gibberish, the Java toolchain will never charitably interpret your syntax errors as "close enough to understand". SQL-the-standard birthed a bunch of different dialects of SQL that ought to be de-jure separate query languages. So much human effort is wasted dealing with the standard SQL abstraction layer. If someone is migrating from Oracle to Postgres, the fact that their query languages are eerily similar is far down the list of practical concerns.
- bagol 2y agoThe only problem I have with SQL is that `SELECT` comes first instead of last. It prevents the development of good tooling.
- a_aitch 2y agoCould you elaborate? I'm curious why that makes a difference.
- cyberax 2y agoFor auto complete, for example.
- pmoleri 2y agoSelect Na.. vs From Users Select Na...
- OJFord 2y agoYou could qualify the names up front? select users.Na.. from .. If your tooling doesn't work in the way you want with that I don't think there's reason to think it would if it were possible to write from users select Na.. either.
- systems 2y agowell its trade off , its easier to read, maybe a bit harder to write also remember that sql is a query language not a programming language, so the main target are not programmers also think of it this way, it might help, you start with that you really want select firstname, lastname, hobby so you start by saying you want a table (a relation, a relvar) that have first name , last name etc ... this is what you want then you tell me where to get it from from users so the order is not bad , it like saying, get me a a drink from the box you wont say, from the box get me a drink select first resembles more natural language, and is more declarative from first, is more procedural what i want first, then how --> declarative how to find what i want, then what i really want --> procedural
- fallingfrog 2y agoTl;dr: the original design idea behind sql was a failure and here’s why. I know the intent was to write a declarative language where you just specify what you want and the system figures out how to get it- but I consider that project to have definitely failed considering that in order to get the sql interpreter to behave in a sane fashion you must use explicit JOIN statements everywhere and fetch table rows and index them to other table rows in a specific order using specific keys that the programmer chooses. It’s exactly the same as writing while loops, though the syntax is admittedly quite nice. I’ve seen people try to interact with the interpreter with a great big god query like SELECT FROM A,B,C,D,E WHERE A.id=B.id and B.id2=C.id2 and so on, and that would be a declarative way to do it, but the performance of the interpreter in such scenarios is absolutely, universally, perplexingly dismal and in practice you can’t use sql that way. It was a nice idea though.
- dventimi 2y agoRubbish
- balakk 2y agoSorry but I've to agree with the other flippant response to this post. The whole idea of SQL is to avoid such imperative thinking, and SQL optimizers are incredibly good in most prominent DB engines. There are ways to hint and influence a certain query plan; but that's not to take away from the incredible convenience that SQL affords for writing adhoc queries.
- fallingfrog 2y agoThe ones I’ve used (mainly Microsoft sql server) are not just bad, they’re abysmally bad for any query that’s not extremely trivial. Writing every query with explicit joins is a necessity almost 100% of the time. Maybe you’ve just never tried to do anything tricky with large tables with a lot of data? Query optimizers almost always resort to nested table scans as soon as you do anything even slightly complex.
- rspeele 2y agoSQL, C, Javascript: the three kings of "good enough". Bad enough that their flaws are evident even to novice practitioners. Good enough, entrenched enough, that they will be around for the next 50 years too.
- gregw2 2y agoI think C will "be around" like COBOL in 50 years given its footguns despite its ubiquity today. Not sure about SQL and Javascript though; successor options are way less clear.
- collyw 2y agoDoes AI do COBOL yet? Maybe that's where all the jobs will be in a decade. It's not like there are a ton of blog posts about how to do stuff in COBOL to train it on.
- FLT8 2y agoAll three are also superficially easy to learn to use, hence they're able to sucker people in initially with the learning curve, then keep them there with the sunk cost fallacy.
- jklowden 2y agoThe only reason we live with Sql in 2024 is that the industry has stagnated. Not only the vendors: the users, too. SQL was designed for a purpose it never fulfilled and never will: end user queries. The promise was that users would write their queries so IT staff would have to write reports. But the only users of SQL today are those same programmers, many resentfully or disdainfully. Every online database discussion is rife with misinformation or silly advice ("write SQL sentences"); rare is any discussion about the relational model or first order predicate logic. And that brings us to the user-led stagnation, as it were. If more programmers using SQL understood the fundamentals, perhaps there would be some innovation. Instead we live through the constant reinvention and rediscovery of what SQL can do, and a perennial effort to make it "easier" with ORMs and such. Actual ease, using a better language, is never discussed. The right language to express relational algebra IMO is Lisp. I don’t mean Lisp-over-SQL. I mean Lisp over data, where tables are fundamental Lisp objects, and modifying those objects updates the database. Lisp solves all of SQL’s deficiencies: verbosity, orthogonality, composability. The would-be inventor of a better database query language faces a daunting problem, however: there is no free relational engine. No C library implementing relational algebra over a data store. So, not only does the language need invention, but the database engine itself needs to be engineered. Nothing has happened in this area since Berkeley DB, which is barely a start and has anyway been hijacked by Oracle. The nearest thing to a free modular database framework isn’t very close. The original Berkeley Ingres DBMS is nominally free software. But it hasn’t been portable to, say, OpenBSD for decades, and the proprietary owners are understandably not motivated to make their product a toolbox for tinkerers. So, it seems we’re doomed to another 50 years of SQL. We have met the enemy, and he is us.
- TOMDM 2y agoIf anything the latest craze is leading us into repetition. > The LLM will write the SQL query for your end users! We are doomed to repeat the same mistakes forever.
- analyte123 2y agoIt's simply not true that the only users of SQL are programmers. In fact, I have seen the disdain programmers have for SQL be a problem, where the programmers designed a Python client for data and the major users wanted SQL.
- DeathArrow 2y agoI wonder if there can be a more efficient language to interrogate relational databases. By efficiency I mean both performance and less complexity.