We Can Do Better Than SQL (2019) | Hacker News Reader

We Can Do Better Than SQL (2019)

edgedb.com

16 pointsyamrzou16 comments

The last line here: "like any solution, SQL is facing increasing inadequacy in the support of the new requirements, modes of use and user productivity. It is time we do something about it."

I agree, but I'm not sure the solution is to broadly replace SQL with something like EdgeQL. Graph Databases, BigTables, Document and Object Databases, Large scale key-value lookup tables... there are all kinds of solutions for problems that feel crammed into a SQL context. EdgeQL could be a great one in some scenarios - for example, when you need to do a lot of querying on arbitrary trees.

Another solution is to go as far as the sweet spot of SQL can take you, and then switch over to a programming language (this is distinct from what is possible in SQL, the basis of many SQL programming interview questions)). I suspect SQL compares less favorably in a lot of scenarios that require a pure, single language solution. But may be we should be comparing SQL + Python with EdgeQL + Python (or even SQL + EdgeQL + Python).

Any "improved SQL" could take some inspiration from .net's Linq. Some conveniences in Linq compared to SQL:

- From clause before the select clause. Makes it possible to use code completion in the select clause.

- Instead of the having clause, you just have a new where-clause after a group-by, which operates on the groupings.

- let-clauses where you can define expression which can then be re-used in the following clauses.

- composability, so you can have arbitrary many select/group/where/join/let clauses in any order following the initial from-clause.

I am no SQL expert, but I always found it a bit weird that selecting the relationships is a relatively hard thing to do for a relational database.

What I mean is that relationships are at the core of the database, so they should--IMHO--be easy. Instead, JOINS feel like some advanced feature that I always see novices struggle with.

If not advanced certainly rote and tedious.

I don't understand this comment.

x JOIN y ON x.id=y.id

How is this advanced?

The fact that there's an endless number of pitfalls[1] is a characteristic of the operation you're trying to achieve, not a deficiency of SQL. In fact if anything I would say that SQL does a great joke at letting you tweak how you want all those edge cases to be handled.

[1] E.g. What happens if some records are missing on one or the other sides of the join? What happens if ids are not unique?

I would be convenient if you could say "x INCLUDE y" or something like that and the engine would figure out the join by foreign/primary keys.

Might seem like a small improvement, but if you write lots of queries it would be a significant time saver.

If you do

x JOIN y

Postgres automatically uses same-name columns for the join. So in the example I gave you, actually "ON x.id=y.id" is redundant. I just added it because if I hadn't someone else would've replied "oh but that only works if you name the columns the same name"

Also, in any less than trivial scenario you will have to think how to handle the edge cases. But again, that's a property of the operation, not of the language.

Oh yeah, that is a "natural join". Problematic since it depends on the names of the columns. I can see how it is convenient though. I would like something like this, just based on declared foreign key relationships rather than column names.

I could say the same thing about your suggestion: Problematic since it depends on what you set as primary/foreign key. I can see how it is convenient though.

Foreign key is a formal semantic while same column name is just a convention

What is problematic about that? Column names are just names, there is no guarantee that value in one table corresponds to a value in another table just because the columns have same name. Private/foreign key declarations enforces consistency so it is not possible to have an orphan foreign key.

Agreed, not just a time-saver but a self-documenting/maintaining advantage. In concept you could change the underlying keys that relate a Foo to a Bar, and still have your queries work. And prevents dumb typos/brainfarts like "join... on Foo.Id = Bar.Id" mistakenly written instead of "on Foo.Id = Bar.FooId".

IMHO lots of teachers have an absolute horrible understanding of relational databases and SQL. Myself included, until I started working at large financial institutions and read every book by Joe Celko I could lay my hands on.

E.g. using NATURAL JOINs covers about 1/3 of the use cases where students or apprentices are usually fiddling with INNER JOINs or joining relations using WHERE TableA.ID = TableB.ID, and it's syntax is much simpler.

With a well thought out schema, I personally find that NATURAL JOINs covers about 80% of my needs in that respect, and being unable to NATURAL JOIN tables is beginning to indicate well, not a "code smell" but what I would call a "schema smell".

I fully agree. Joining relations in a relational databases should absolutely be simple - and it absolutely can be:

          SELECT col_x, col_y
            FROM TableA
    NATURAL JOIN TableB

Thanks for your comment, I actually never heard of NATURAL JOIN, I will have to play with it but it seems very interesting!

I am learning Power Query. It's the first Microsoft technology in quite a while that's left a positive first impression.

I'm not particularly crazy about relearning how to do the things I would do in SQL, differently. On the other hand, I feel like some of the straightforward functional language features would be helpful to have in SQL.

I always felt like T-SQL and especially PL/SQL were terrible mistakes, but that SQL was missing something.

Power Query didn't seem to have something like GROUPING SETS, but I was very pleased when it seemed natural to roll my own as a function. SQL (at least the dialects I'm used to) doesn't lend itself to that sort of thing.

Discussed at the time: https://news.ycombinator.com/item?id=19871051

Reply on news.ycombinator.com