Item — HN Reader

SQL expresses relational algebra [0] which is the language defining data relationships and manipulation. It hasn't changed in 50 years for the same reason why B-Trees and sorting algorithms haven't: it's foundational to computer science.

Database-specific "SQL extensions" are in my experience just administration commands, e.g. `VACUUM` or `CREATE EXTENSION` in Postgres. They help operate the DB, but have little to do with the actual data manipulation.

Killing SQL is like trying to kill computer science: you better come up with something better than a synonym.

[0] https://en.wikipedia.org/wiki/Relational_algebra

8 comments

Nothing about relational algebra says you have to put your projection before you specify what relations are involved. SQL is a bad fit for modern tooling in ways that are a property of the language and are unrelated to it expressing relational algebra.

The notion that nothing has changed about B-Trees or sorting algorithms doesn't, I think, actually hold up... but this question is more like changing the API for a standard-ish B-Tree library than about modifying how the B-Tree itself works.

And that’s why Microsoft’s LINQ puts the entities (table names) first and then autocomplete just works. https://learn.microsoft.com/en-us/dotnet/csharp/linq/perform...

As an aside: LINQ is the only “ORM” that is truly worth using. It is integrated into the language and you can use the same expressions and query expressions are first class types that can be passed around as expression trees and are then only translated either as code if you’re using in memory lists, database specific SQL, MongoQuery etc based on the provider you pass the query to.

> LINQ is the only “ORM” that is truly worth using

I've been thinking a lot about ORMs and they almost usually fall flat at some level since they are not nearly as expressive as a first order language is. I haven't had experience with LINQ but I know the Django ORM is this way.

I'm thinking the best approach if I had time/money would be to develop a better query language that is close enough to SQL for people to learn, but then also translates back down to SQL. LINQ looks really close to this, but I'd really want it to be cross language.

One of the aspects of QUEL that I really liked was that if you couldn't describe what you wanted, you could program it out if you needed to. Say you had some complex ranking algorithm that was hard to write in SQL directly.

> I'm thinking the best approach if I had time/money would be to develop a better query language that is close enough to SQL for people to learn, but then also translates back down to SQL

Have a look at EdgeQL, the query language that powers edgedb.

LINQ is a full query language you can left join, right join, inner join limits, etc. It even maps C# functions to Sql functions. If you can express it with SQL, you can express it with LINQ.

Cross language will still be an issue. LINQ isn’t really an “ORM” it is a method to translate C# commands used to work over related collections to expression trees and those expression trees are interpreted/translated at runtime by a provider.

You need support from the language/runtime to treat expression trees as a first class type.

expression tree parser. It’s not for the faint of heart.

> If you can express it with SQL, you can express it with LINQ.

Not if it uses CTEs or other advanced SQL features. It can do basic select, join, group, sure. But that's just scratching the surface of how powerful SQL is. As for requiring language support: in Elixir, Ecto.Query essentially implements a LINQ-like DSL as a library (albeit with macros). See https://hexdocs.pm/ecto/Ecto.Query.html

From what I see a CTE is just an expression that you can reference.

In C#, if you did something like this

var seniorMales = from user in users where u.Age > 65 && u.Sex == “male” select u

seniorMales is just an IQueryable<User>

Later on you could say

var gaMales = seniorMales.Where(s => s.State == “GA”)

var floridaMale = seniorMales.Where(s => s.State ==“Florida”)

All three of those are just expressions that have not hit the database yet.

Then when you want to actually run the query and limit the number of rows, you can do

var result = floridaMales.Limit(20).ToList()

It would then create a query including a limit clause just as you would expect it to.

Now if you do

floridaMales.ToList().Limit(10)

It would return all of the rows in the table to the client and it would be limited on the client side (don’t do that).

Worse case, if LINQ can’t express in query syntax, a provider can add its own extensions in function syntax and the provider can still parse the expression tree.

Yes, I've been a professional C#/.NET developer before and understand LINQ and Entity Framework. The difference is that CTEs allow you to reference multiple tables at once and do inserts and updates on the intermediate result set. You can also use recursive CTEs to do hierarchical and graph-style queries, which you can't do with LINQ as far as I know.

LINQ also can't do window functions. Like I said, it covers the basics of SQL that most applications need. But as soon as you need to go beyond that you'll be writing custom SQL.

> LINQ puts the entities (table names) first and then autocomplete just works

I grew up with SQL so LINQ for a long time just reads so weird to me. I won't admit how long it was before I realized this fact you stated.

It still reads weird to me, but I get it.

SQL does express relational algebra, but OP is arguing it expresses it poorly, in a cumbersome way. There's nothing foundational about how exactly SQL chooses to express relational algebra, you could imagine many different syntaxes and specific semantics.

I concur, SQL is a liability in many ways.

>Killing SQL is like trying to kill computer science

SQL is a language that expresses relational algebra, killing it is like killing any other language i.e. nearly impossible because there is still code sitting around written in it, thus needing competent programmers to maintain, competent programmers who will be asked to write more code and will choose to do it in SQL if they can get away with it, rinse and repeat and so forth.

Once a language has a certain installed base it may be eternal - what size that installed base is I don't know.

The installed base is kind red herring, because most of the worlds largest companies use Oracle to hold their data. So you'd have to get Oracle on board.

Could there be a better alternative, oh yes, most certainly.

E.g. Every company I worked for needed to do SQL migrations. Unfortunately that isn't a part of SQL. Could it be? Yes! Is it? No! So everyone re-invents the wheel...

The Third Manifesto[0] is 27 years old at this point. If it were possible to take seriously the proposition that SQL and relational algebra over databases were equivalent before its publication, it hasn't been since.

[0]: https://en.wikipedia.org/wiki/The_Third_Manifesto

I've been trying to get my hands on a corpus of Dataphor code, because Tutorial D is unsatisfactory for different reasons than SQL. What I've been able to glean of Dataphor is quite promising, but it isn't much.

You mean it butchers relational algebra; composition is the name of the game, and yet that's somehow incredibly cumbersome in SQL.

Db-specific extensions unfortunately aren't only admin commands. They include the whole PL/SQL thing, with concepts like triggers and stored procedures that should definitely be fundamental in SQL, but that nobody bothered to standardize, and eventually everybody implemented as they liked.

Stored procedures and even triggers are non-relational and have little to do with SQL proper. They are like Lua scripting inside nginx which are not directly related to anything HTTP.

On one hand, it might be nice to have completely portable triggers. On the other hand, the lack of standardization allowed to have approaches as differer as PL/SQL and T-SQL to emerge. It would be terrible to be stuck with some ancient COBOL-like SP / trigger syntax, required by the standard.

As someone who has worked across many DBs, I find the lack of a unified standard for procedures and triggers appalling and very time-consuming.

Granted, stored procedures and triggers aren't part of the relational model (strictly speaking). But they are the first layer built on top of the core - the one that packs multiple queries/statements together, introduces variables, and allows custom logic to be executed when the data changes.

The lack of standardization means that migrating from e.g. Oracle to Postgres/MySQL involves knowing the dialects and statements supported by each of them in order to migrate the PL/SQL or T-SQL logic - and, especially in the case of MySQL/MariaDB, some of those constructs may not be available at all.

That creates really a lot of friction when it comes to database migrations, especially for large databases. I've myself been working with this stuff for years, but I still have to regularly lookup how to write an IF or declare a variable for this or that DMBS, since we have such a proliferation of dialects and standards that one person can't keep all the variations in their mind.

Relational algebra is what makes SQL great: some actual strict math behind the design.

Ad-hoc syntax with hard-to-compose parts,is what makes SQL arbitrary and quirky. I would like a more algebraic syntax, with uniform, composable parts.

> SQL expresses relational algebra

Actually, no. Relational algebra is based on sets. SQL is not. Perhaps you are confusing SQL with QUEL, which was used by Postgres in its younger days? QUEL evolved out of Codd's original Alpha language.

How is SQL not based on sets? One of the big issues I see when people first come to SQL is not thinking in sets. Instead they think in individual records and loops, which is the exact opposite on how to think about SQL.

Sets are collections of unique elements, and indeed relations are sets of tuples. But SQL only sticks to the tuples part, foregoing sets. It's quite possible, and quite likely if you're not careful, to end up with duplicate entires in your results to an unwanted effect.

SQL is only relational-like. With experience and care you can craft your queries in such a way that you do get true sets (e.g. using DISTINCT), but I see beginners – and sometimes even experts in a rush – overlook this quirk in a complex query resulting in what might seem okay in limited testing, but blows up when there is more data than the bare minimum needed to test. A language that truly follows Codd's relational model would produce sets always.

I expect SQL is considered difficult to learn exactly because of this deviation. It's quite unintuitive, especially if you go in thinking that it is actually relational. Nothing you can't work around with sufficient knowledge and experience, but that added knowledge and experience required is where the difficulty no doubt stems from and undoubtedly scares many away out of frustration when it doesn't work like you think it should in the interim.