So, if we put aside Clippy jokes and so on, what's your problem with SQL exactly and what does "better than SQL" mean to you?
Because what I saw in the last couple of decades is the NoSQL movement lose air, and half the products representing it adding some sort of SQL dialect support, which if you think about it is hilarious admission of defeat.
SQL is exceptionally good at being a relational algebra language, and if you think in terms of relational algebra, you might find out there's nothing wrong with it, aside from superficial remarks like "FROM should come before SELECT".
.NET added LINQ and now basic queries are integrated. It was not up to SQL, it was up to .NET
And calling it "very low-level" makes me question if you understand what SQL does and is. It's probably the only mainstream 4th generation language I can think of.
https://en.wikipedia.org/wiki/Fourth-generation_programming_...
It's low level because it lacks abstraction and anything outside the data query declarative paradigm is subpar. It has no notion of anything outside of itself, so it's a very restrictive inflexible dsl living in it's own bubble. And even worst, it lacks elegant abstractions for problems we've have had for decades, such as pagination, hierarchy, etc
Also, using linq as an example rather shows your lack of understanding of the field of abstractions for sql. The state of the art ORM is currently SQLA, the leading innovation to replace sql as a dsl is edgeql, both are addressing sql pain points while aiming at allowing you to do all of what sql could do.
Agree about the verbosity. The syntax is quite clunky.
Some rebel about really silly things, such as RDBMS.
> SQL is incapable of providing the kind of firm foundation we need for future growth and development. Instead, it’s the relational model that has to provide that foundation. [...] We see SQL as a kind of database COBOL, and we would like to see some other language become available as a better alternative to it.
Summarizing Date's points:
- Real relations don't contain duplicate tuples but SQL tables allow duplicate rows.
- Tuples and therefore relations don't ever contain nulls. Personally, I have trouble understanding how to support this limitation given LEFT JOIN.
- Domain types only provide type aliases not a new type. For example, you can compare or join on different domain types if the base type is the same. Date uses the example of `p.weight = sp.qty` to show a comparison on domain types that shouldn't be allowed.
- SQL has many ways to express the same query. His example shows at least 12 ways to answer "Get part numbers for parts that either are screws or are supplied by supplier S1, or both."
- SELECT DISTINCT should have been the default instead of SELECT ALL.
My personal gripes:
- SQL is quite chatty, so much so that you can omit words in some incantations.
- It's hard to dynamically build queries. It composes poorly for anything dynamic, even simple things like ordering by a column name.
For example, allowing duplicate rows is irrelevant, because if you have a primary key, there are no duplicate rows. SQL doesn't require primary keys because relational algebra has no such concept as a "primary key". There are just keys. However without defining a primary key, enforcing unique rows would mean the database silently indexing the ENTIRE ROW'S CONTENTS, including potentially blobs and large text fields. This would obviously be nonsense.
Likewise, having SELECT DISTINCT be a default would mean a very expensive processing step in your query processing being a default. An expensive step that in fact doesn't matter, because the vast majority of queries don't produce duplicate results in practice. DISTINCT is optional because the need for it is exceptional, and its cost is high.
Even you don't buy the "have no NULL" argument. So I don't have to defend this. Real-world data is not perfectly "rectangular". Optional attributes are a thing. So having a primitive for it makes sense. Once again, SQL allows you to define a field as non-nullable, so complaining about it being there if you EXPLICITLY WANT IT is silly.
Regarding having many ways to express the same query: that's true for all languages. This is one of the biggest issues in optimizing compilers, canonicalizing expressions so patterns can be recognized. There's no way to do it at the source, so complaining SQL also does it, is like shouting at clouds.
Regarding the type system, type systems can always be better, but let's not forget type systems (typically) exist to eliminate mistakes, not to enable new features. The kind of mistake where you compare "weight and quantity" is not likely.
More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system. Even if SQL had a detailed type system, therefore, most people wouldn't bother duplicating their detailed type definitions from Java to SQL or back.
The only remaining issue is SQL is chatty. Which is quite ironic given the procedural code for what SQL does would be several times the size of the SQL query. SQL is a high-level language, and it being explicit is fine. And few extra letters here and there for a keyword don't make or break a language. I prefer chatty over cryptic.
> More subtle errors are possible, like comparing metric and imperial measures, but since SQL is often used in tandem with a system's language (Java, C++) or a script, all this domain logic is offloaded to them and their type system.
PostgreSQL actually supports user-defined types[1]! You could do something like define [2] a “kilogram” type and a “pound” type and then summing or joining on the column is safe.
That could still get sticky with grams and kilograms. Also, pounds are defined in terms of kilograms. So you could also define a “weight” type. This can have semantics like DATE[3] or BOOLEAN[4] so inserting “1kg”, “1kilogram”, “1000g” or “2.204623lb” all store the same value.
You can then use (define) format functions like TO_CHAR(weight, string) to display weights in grams, lb or whatever you need.
Of course, the other argument here is that all you really need are the primitive types and normalization. A “weight” table can just have a “unit”::string column. You can do conversions with another table such as “from_unit”::string, “to_unit”::string and “multiple”::float. Your “product_weight” table would then just have foreign key relationships to the weight and conversions tables.
[1]: https://www.postgresql.org/docs/current/xtypes.html
[2]: https://github.com/df7cb/postgresql-unit
[3]: https://www.postgresql.org/docs/13/datatype-datetime.html#DA...
[4]: https://www.postgresql.org/docs/13/datatype-boolean.html
In SQL you enforce uniqueness of rows by defining a primary key. Dates concern seem to be that you can have duplicate rows if no primary key is defined, but why would you do that in the first place? A table should always have a primary key.
His gripe against nulls are controversial - E.F.Codd suggested nulls himself, so when Date claims null have no place in the relational model he is just stating a personal opinion. And while nulls are kind of weird, his suggested alternative is far worse.
His complain about comparing different types is valid IMHO. SQL is weakly typed and types are silently converted. I think it would be be much better if it was strongly typed and values has to be explicitly converted when e.g. comparing a string to a number. The issue about custom types is a consequence of SQL being weakly typed.
- object (or rather, tuple) identity
- replace null with something to indicate missing or unknown. Have sensible null object behavior
And the big one that gives every relational algebra purist fits: make graph theory a first-class citizen of the data model.
I don't see that as an admission of defeat. I see that as an attempt to provide some of the things that are good about SQL without dragging in the entire hairball of the language. Along the way, by choosing which subset of SQL to emulate the designers get to incorporate programming concepts that have come along since SQL. Things like user-defined ADTs, lambda expressions, and extensibility. Oh wait, LISP had all those before relational algebra was a gleam in the eyes of Date & Codd.
It's sort of like Git in that way.
SQL is hard to replace because most replacements are either not radical enough to make migration worth it or so radical that it's something else entirely.
As a language, SQL is a downright mess. The only thing it really got right was being based on the relational algebra and model.
It’s also why language tooling is utter garbage is the DBA world. The lack of real Autocorrect/suggestion, code formatters, useful error messages, static typing (the tables & views are strictly typed, but your queries can’t be?), the lack of interoperability, etc all stem from how awful SQL is at being a programming language. Jumping from C# to SQL is like viewing a portal into the 80’s — it’s ridiculous. Hell, the standard itself is behind a damned paywall. If that’s not a signal of something going horribly wrong, I don’t know what is
What I’ve never understood is why open source databases don’t have alternative frontends to SQL — where’s my first-class support for datalog in Postgres? Or even just a SQL dialect that lets me put FROM before SELECT so autocomplete can do something useful for once?
Come on aspiring post-docs, do your thing and launch a new programming language.
If MIT can launch Julia someone can launch a language that's not SQL.
It's read only, but holy shit please become popular and displace non-ANSI SQL in my life.
Postgres was originally built on one: https://en.m.wikipedia.org/wiki/QUEL_query_languages
There’s also D/D4/Dataphor, though i’m less convinced.
Unsurprisingly that mostly died out after sql took over everything.
But, a lot many applications can be developed without really knowing the depths of SQL with help of the ORM itself.
However group by specifically seems to be a hack, and it falls on its face in weird ways almost every time I use it. I’m not sure how much I like it’s “AST rewriting” model, but it works great when it works. Just the LET command alone justifies it
I really like the automatic migrations.
For small apps and a single dev it hits a sweet spot.
I find .Net really good at being kitchen sink included and business ready. If you're making internal small apps I've yet to find a better platform. Especially if you're in an MS Office heavy environment.