Re: SQL queries as sets: was The tragedy of SQL - Mailing list pgsql-general

Oh, yeah, wow. Big topic.

My original post in the series is in significant part about how SQL hides this sort of thing from you.

A table is a set:  a set of true facts, all having the same structure, so you can operate on all of them with any operation on the individual rows.

Multiple tables, multiples facts. Sometimes about the same things, which is what a join does: the join key is the identifier of the things a bunch of statements are all about. A user has a name, and a login, and a creation date. When the identifier is the primary key, there is only such statement. When it’s a foreign key, there are multiple statements.

As an aside: it would be perfectly reasonable to have multiple tables with the same primary key. Or with the same foreign key. You could have only “foreign keys” on a bunch of different tables.

When you do a join, you’re finding all the “x and y” statements that the system knows to be true. That’s why a 1:many joins produces duplicate 1-side values in its results.

When you do a where, you’re reducing the number of rows by only finding the rows with a certain property. The joins and wheres together give you a very expressive logic engine.

There are two equivalent ways of looking at what the database does: it stores logical statements and acts as a logic engine; or it stores sets of tuples and gives you set operations on them.

Go read up on the basics of Datalog: it makes all of this beautifully obvious.

My original post was about how different the computer industry would be if only we’d made relations as easy as Datalog does. The entire industry would look different, just as garbage collection made the entire industry different.