Better SQL JOINs
The idea is to add a new ternary operator, which would be allowed only in the FROM clause.
It would take three operands:
1) referencing_table_alias 2) foreign_key_constraint_name 3) referenced_table_alias
POSSIBLE BENEFITS
* Eliminate risk of joining on the wrong columns Although probably an uncommon class of bugs, a join can be made on the wrong columns, which could go undetected if the desired row is included by coincidence, such as if the test environment might only contain a single row in some table, and the join condition happened to be always true.
* Conciser syntax In a traditional join, you have to explicitly state all columns for the referencing and referenced table. This is somewhat addressed by the USING join form, but USING has other drawbacks, why I tend to avoid it except for one-off queries. When having to use fully-qualified table aliases, that adds even further to the verboseness.
* Makes abnormal joins stand out If joining on something else than foreign key columns, or some inequality expression, such joins will continue to be written in the traditional way, and will therefore stand out and be more visible, if all other foreign key-based joins are written using the new syntax. When reading SQL queries, I think this would be a great improvement, since the boring normal joins on foreign keys could be given less attention, and focus could instead be made on making sure you understand the more complex joins.
SYNTAX
Syntax is hard, but here is a proposal to start the discussion:
from_item join_type from_item WITH [referencing_table_alias]->[foreign_key_constraint_name] = [referenced_table_alias] [ AS join_using_alias ]
To experiment with the idea, I wanted to find some real-world queries written by others, to see how such SQL queries would look like, using traditional joins vs foreign key joins.
I came up with the idea of searching Github for "LEFT JOIN", since just searching for "JOIN" would match a lot of non-SQL code as well. Here is one of the first examples I found, a query below from the Grafana project [1] [1] https://github.com/grafana/grafana/blob/main/pkg/services/accesscontrol/database/resource_permissions.go
SELECT
p.*,
? AS resource_id,
ur.user_id AS user_id,
u.login AS user_login,
u.email AS user_email,
tr.team_id AS team_id,
t.name AS team,
t.email AS team_email,
r.name as role_name
FROM permission p
LEFT JOIN role r ON p.role_id = r.id
LEFT JOIN team_role tr ON r.id = tr.role_id
LEFT JOIN team t ON tr.team_id = t.id
LEFT JOIN user_role ur ON r.id = ur.role_id
LEFT JOIN user u ON ur.user_id = u.id
WHERE p.id = ?
FROM permission p
LEFT JOIN role r WITH p->permission_role_id_fkey = r
LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r
LEFT JOIN team t WITH tr->team_role_team_id_fkey = t
LEFT JOIN user_role ur WITH ur->user_role_role_id_fkey = r
LEFT JOIN "user" u WITH ur->user_role_user_id_fkey = u
WHERE p.id = 1;
Rewriting our example, using such naming convention for the foreign keys:
FROM permission p
LEFT JOIN role r WITH p->role = r
LEFT JOIN team_role tr WITH tr->role = r
LEFT JOIN team t WITH tr->team = t
LEFT JOIN user_role ur WITH ur->role = r
LEFT JOIN "user" u WITH ur->user = u
WHERE p.id = 1;
43 comments
[ 4.0 ms ] story [ 107 ms ] threadAgain, I'm no expert, but I'm a huge fan of "fail fast" in general. I'd rather have a miswritten query refuse to execute instead of executing poorly. I guess if you have an SLA where you're allowed to ... stretch the truth about the state of your servers, it's better to have everything grind to a half than error immediately. So that way your dashboard stays green. I guess I can see it both ways.
The only thing I really miss in PG is ASOF JOIN. It's possible to implement it via CROSS JOIN LATERAL, but in my case it resulted in nested loops — coming from ClickHouse it bothers me that there is no way (that I know of) to hint PG to use a specific join strategy.
The first thing (among many others) that I would change in SQL is the position of the SELECT clause:
FROM .. JOIN .. SELECT .. WHERE ..
instead of
SELECT .. FROM .. JOIN .. WHERE ..
That would make the construction of many queries more natural.
So if we could rewrite history, I agree the order you suggest would make more sense, but it’s probably unrealistic to change it, except for entirely new SQL inspired languages.
To comment on having to look up foreign keys. The idea I had in mind is to allow changing the default formatting of foreign key names, so you could figure out the name, except in special cases such as if there would be two foreign keys referencing the same table. For such cases you should explicitly name the foreign keys.
FROM .. JOIN .. WHERE .. SELECT
tables -> columns -> rows -> filter
I think they knew that but decided they wanted DBAs to think about what they wanted to end up with before they began writing code, so they moved the last step to the first step. I can get behind that from a pedagogical perspective.
However today, relational databases are far too ubiquitous to always get the level of serious thought and considerations they were once afforded, and are regularly being used by (perish the thought) non-DBAs.
At this point it is muscle memory to start with:
then comment out the `--count(*)` and build up the output after the body works because I may not know what is available to select before isolating it.If it were a proposal for a SQL21 standard to offer this as an optional method for query processing, I'd be all for it.
However, the idea of "non-optional followed by optional" came from an era where that sort of thing mattered and it made sense.
You're using the alias "r" here for two different things:
> the special but common case when joining on columns that exactly match a foreign keyI've been programming with RDBMSs since 1996. I'd say that approximately 99% of the thousands of JOINs I've written were based on PKs/FKs.
The example that you are attempting to improve already operates on PKs/FKs.
I don't understand the point of this proposed improvement at all
I agree this is a bit confusing. The user "BeefWellington" came up with a better idea, to skip the "= r" part, since it's redundant. That way, you would only need some new keyword, to indicate you want to perform a foreign key join, and specify the referencing or referenced table alias (depending on which direction the join is made, i.e. what table alias that has the foreign key), together with the name of the foreign key.
> The example that you are attempting to improve already operates on PKs/FKs. > I don't understand the point of this proposed improvement at all
The example operates on PKs/FKs column, yes, but they are specified manually. I listed a number of possible benefits under the section "POSSIBLE BENEFITS" in my original post, I think these explains what improvements I specifically see.
2) That's not at all clear from your example. Nor does it necessarily follow from the proposal in general
3) This would be your strongest argument. But it's not really a problem in the real world. If/when this happens, it does so for one of three reasons:
All of these are bigger problems - they transcend the the realm of JOINs.As with 1), I don't see enough value in it to make the overall language (and parsers) more complex.
SQL has lots of warts. It is an unpleasant language. It is a difficult language to learn (initially). But it (mostly) works.
If I was going to advocate for a change like this, I'd propose a new join type called something like FK JOIN:
This would work sort of like NATURAL JOIN, but without any possibility of ambiguity (and only on the key column).What if table1 has been joined in twice, to what table alias is table2 joined against?
Example:
Would table2 be joined against "b" or "a"? I would guess "b" and guess the idea is to always let FK JOIN join against the previous join?FK JOIN was a casually tossed off addition that, in retrospect, I shouldn't have included.
> 2) That's not at all clear from your example. Nor does it necessarily follow from the proposal in general
Can you provide a counter-example that will not be conciser when written using the foreign key join syntax?
I often think half the ORMs in existence might not have been invented if SQL was just a bit more ergonomic.
If the foreign key constraint is defined and unique just use it and don't make me type out all the redundant join rubbish. You could then even do deep nesting with no more syntax:
Something like this was actually my first iteration of this idea, to join based on the column name(s). But then someone helped me think about what if the column is involved in two different foreign keys (to two different tables)? Not common, but absolutely possible. That's why just naming the column(s) isn't precise enough, except in most cases.
Even if no such ambiguity was allowed, i.e. if all unique sets of column name(s) referenced exactly one table, you would still need to think about how the syntax would look like for foreign keys on multiple columns.
I'm sort of surprised that there is nothing similar to the CSS SASS/SCSS type idea that has ever taken off in SQL land ...
I think at least you would need to find a way to follow foreign keys on multiple columns.
I can see why working with column names would be nice, since we then would not have to deal with foreign key names at all, which is another thing to keep track of mentally. But the only way that would work would be if we would forbid creating multiple foreign keys on the same set of columns, only then would the set of columns uniquely identify what foreign key to follow. This is much more invasive though, as it could break existing data models.
-> is really just syntactic sugar for a left outer join, which implicitly becomes an inner join if a foreign key constraint is present. How it ends up resolved is up to the optimizer, whether there's a foreign key available it might exploit or not.
No longer needing to use commas between the items after the SELECT and before the FROM. Wouldn’t that be nice? We don’t need to use commas in between joins and items in the WHERE clauses. I get that there is probably some syntax reason for this, but I would like to know why.
SELECT a b c d FROM table
I can understand the appeal, but when you're doing:
SELECT CAST(a AS VARCHAR(8)) as cast_a b c d FROM table
There's no obvious syntactical way to separate those out, especially since you can get really complex in how you assemble things like aliases when you get into functions, stored procedures, subselets, etc.
PostgreSQL's cast operator syntax (::DATATYPE) can help a bit but it hasn't been standardized.
To be clear though; there's a really good set of reasons this isn't a great idea IMO, the two simplest being:
1) Now when I'm writing queries I have to go look up the foreign key names. This isn't something that I'm often innately familiar with (unless there's a reliable naming convention; spoiler: there never seems to be). Contrasted with field names, I could easily construct half a dozen quick queries against any DB I regularly use by knowing which tables or fields are there.
2) The example you provide are actually less clear than explicitly stating the joined fields. If I have to come in and troubleshoot something down the line, I then have to spend additional time after viewing the query going in search of what the heck fields are involved in team_role_role_id_fkey, rather than just seeing that it's role.id and team_role.role_id.
I see, you're right; we don't need to! Thanks for great improvement! I don't think using "USING" is a wise choice though, as that's already a different way of joining. But I agree WITH isn't very nice either. How about "KEY"? I also think we should avoid reusing "." since that will is used to refer to columns, and the FK is not a column. I think we need a separate token, "->", or something else.
With your idea of leaving out the redundant referenced table alias, and using "KEY" instead of "WITH", the FROM clause becomes:
To comment on your other two comments:> 1) Now when I'm writing queries I have to go look up the foreign key names. This isn't something that I'm often innately familiar with (unless there's a reliable naming convention; spoiler: there never seems to be). Contrasted with field names, I could easily construct half a dozen quick queries against any DB I regularly use by knowing which tables or fields are there.
Very valid point. For this idea to be convenient, one would also need to make the default name for newly created foreign keys user-definable. Simply using the referenced table name as the name would work fine in PostgreSQL, where constraint names are only required to be unique per table, and not per namespace as the SQL standard dictates (for really no good reason). E.g. "permission_role_id_fkey" would simply be named "role".
But then one might ask: What happens if you create two foreign keys referencing the same table? In PostgreSQL, a number starting at 1 is automatically appended to the name, e.g. if creating another FK on "permissions" referencing "rule", it would be named "permission_role_id_fkey1".
> 2) The example you provide are actually less clear than explicitly stating the joined fields. If I have to come in and troubleshoot something down the line, I then have to spend additional time after viewing the query going in search of what the heck fields are involved in team_role_role_id_fkey, rather than just seeing that it's role.id and team_role.role_id.
I think this complaint is related to the first one you made. If you do have a simple naming convention, you will see what table is referenced. The actual values of the foreign key column(s) is not very interesting, since they are just referring some foreign row's primary/unique column(s). Why would you ever need to "troubleshoot" the actual values? Since having FKs on such column(s), they will always be correct when set, so if something is to be troubleshooted it ought to be some actual data in the referenced table, which you could manually join-in and inspect, also by manually joining using the FKs, to avoid having to key in the values of the FK column(s).
So, for example, you would have the DDL:
Then you could do: Which would be equivalent to: Maybe even allow using the table->relationship name in the SELECT (or WHERE) clause for an implicit inner join, so you could just do this for the above: Or this to get a list of staff by manager name: Of course, if you are doing that, maybe adopt a syntax for implicit outer joins, too, then you can get a list of employees with no subordinates with: Which would be equivalent to:While probably possible, I think it would be less clear and also increase parser complexity more than necessary.
My original idea was actually to use dot ".", but then others explained the above to me, which made me rethink.
But is it really? If you think of it as referencing "some attribute of whatever is mentioned before it", the period could also refer to constraints, indexes, or whatever.