https://gist.github.com/joelonsql/15b50b65ec343dce94db6249cfea8aaa

Skip to content
 
[                    ]
All gists Back to GitHub Sign in Sign up
 
Sign in Sign up
{{ message }}

Instantly share code, notes, and snippets.

@joelonsql

joelonsql/JOIN FOREIGN.md

Last active Dec 30, 2021

  * Star 15
  * Fork 0

Star
Code Revisions 6 Stars 15
Embed
What would you like to do?
">Embed Embed this gist in your website.
Share Copy sharable link for this gist.
Clone via HTTPS Clone with Git or checkout with SVN using the
repository's web address.
 
Learn more about clone URLs
[<script src="https:/]
 
Download ZIP
SQL language proposal: JOIN FOREIGN
Raw
JOIN FOREIGN.md

 SQL language proposal: JOIN FOREIGN

The idea is to improve the SQL language, specifically the join
syntax, for the special but common case when joining on foreign key
columns.

 The problem

Example below taken from PostgreSQL documentation 1

In SQL-89, we didn't have any JOIN syntax yet, so queries were
written in this way:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

This improved in SQL-92:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d
    JOIN films f ON f.did = d.did

However, in the same standard, they also added USING and NATURAL:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d
    JOIN films f USING (did)

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d
    NATURAL JOIN films f

Unfortunately, USING and NATURAL suffer from problems, as they can
suddenly break due to ambiguities if columns are added. 2

A proper database schema with foreign keys can be represented as
directed graph, where the tables are the nodes and foreign keys the
edges. It's a directed graph since a foreign key has a direction. The
foreign key is created on the referencing table, and the table it
references is called the referenced table. There is a Many-to-one
relationship between the referencing and the referenced table.

Now, please take a look at the join queries above again.

Other than making guesses based on the names of the tables and
columns, there is no information in the SQL query code itself that
tells us if distributors references films, or if it's the other way
around.

We currently rely solely on naming conventions to deduce the
relationship between tables, that is, if it's One-to-many or
Many-to-one.

If there is a bug in a query written by a colleague, causing
unexpected number of rows in the result set, it can sometimes be
complicated to debug such a query.

If the common simple joins (when joining on foreign key columns)
would be written in a different syntax, the remaining joins would
visually stand out and we could focus on making sure we understand
them when reading a large SQL query.

Not having to specify all the join columns would also improve
conciseness.

Problems summary:

  * JOIN USING (...) and NATURAL JOIN improved conciseness compared
    to JOIN ... ON, but suffer from other problems 2.
  * SELECT queries doesn't contain any information on the join
    direction, i.e. what table is foreign vs primary in a join. The
    reader needs to draw conclusions based on the naming of tables/
    columns.
  * When writing joins, having to specify all columns involved in a
    join, is unnecessairly verbose.

 The proposal

Foreign keys are constraints with names. These names are
auto-generated if not specified explicitly, and must be unique per
schema according to the SQL standard, but at least PostgreSQL only
require them to be unique per referencing table.

By specifying the foreign key, we wouldn't need to specify the
columns on which to perform the join.

Here is how it would work:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM films f
    JOIN FOREIGN f.films_did_fkey d

or e.g.

    LEFT JOIN FOREIGN f.films_did_fkey d

Let's explain the JOIN FOREIGN syntax step-by-step:

 1. When JOIN is followed by the FOREIGN keyword, we indicate we want
    to specify a foreign key to follow on a table alias already
    introduced in the from clause.

 2. Next, we specify the foreign key name qualified by the
    referencing table alias, f.films_did_fkey.

 3. Lastly, we assign a new table alias d for the referenced table.

If we would instead want to join by first introducing distributors
and then join films, the syntax would be different:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d
    JOIN films f FOREIGN films_did_fkey REF d

The syntax is different enough to easily distinguish between the two
cases. This is intentional and important, as the first case JOIN
FOREIGN, can never cause extra rows, since it defines a Many-to-one
relationship, whereas JOIN ... FOREIGN ... REF could cause extra
rows, since it defines a One-to-many relationship.

Let's explain the JOIN ... FOREIGN ... REF syntax step-by-step:

 1. In the grammar, at the same place as expecting ON
    boolean_expression or USING (...), we allow the keyword FOREIGN
    to indicate we want to specify the name of a foreign key for the
    table we just introduced in the from clause, as part of the
    current join statement, in the example, this is JOIN films f
    FOREIGN.

 2. Next, we specify the name of the foreign key, as the referencing
    table which has the foreign key, has already been specified in
    the first part of the join statement. We simply write
    films_did_fkey.

 3. Lastly, we need to specify what table alias to join against, REF
    d. This is necessary since even though we know the referenced
    table based on the foreign key, the table might have been
    introduced multiple times in the from clause.

To further improve conciseness and readability, a better default
naming convention for foreign keys would be to give them the same
name as the referenced table:

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM films f
    JOIN FOREIGN f.distributors d

SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d
    JOIN films f FOREIGN distributors REF d

Note, this proposal does not depend on any specific naming convention
for foreign keys.

If there are multiple foreign keys between two tables, the foreign
key names could instead be based on column names. A typical example
would be some table with multiple user_id columns, such as owner_id
and manager_id, both referencing users.user_id, in such case, the
foreign keys could be named owner and manager.

Another example would be a users table with a self-referencing
parent_id column. Such foreign key could be named parent.

The example query from the PostgreSQL documentation is perhaps too
simple to fully demonstrate and appreciate the clarity and
conciseness of the proposed JOIN FOREIGN syntax. Here is another
slightly more complicated example taken from the Grafana project 3.

SELECT *
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 = 1;

vs

SELECT *
FROM permission p
LEFT JOIN FOREIGN p.role r
LEFT JOIN team_role tr FOREIGN role REF r
LEFT JOIN FOREIGN tr.team t
LEFT JOIN user_role ur FOREIGN role REF r
LEFT JOIN FOREIGN ur.user u
WHERE p.id = 1;

What's nice about this example is the need to use both JOIN FOREIGN
and JOIN ... FOREIGN ... REF. It cannot be written using only JOIN
FOREIGN statements, since we cannot reach all referenced tables from
any single referencing table. For instance, we could begin with
team_role:

SELECT *
FROM team_role tr
LEFT JOIN FOREIGN tr.role r
LEFT JOIN FOREIGN tr.team t

But then we would need JOIN ... FOREIGN ... REF, as none of the role
nor team tables have any foreign keys to follow:

LEFT JOIN permission p FOREIGN role REF r
LEFT JOIN user_role ur FOREIGN role REF r
LEFT JOIN FOREIGN ur.user u
WHERE p.id = 1;

@gigatexal
Copy link

gigatexal commented Dec 30, 2021

I think this is brilliant. I'd love to see this get adopted.

Sorry, something went wrong.

@branjwong
Copy link

branjwong commented Dec 30, 2021

Big fan

Sorry, something went wrong.

@tucnak
Copy link

tucnak commented Dec 30, 2021

    To further improve conciseness and readability, a better default
    naming convention for foreign keys would be to give them the same
    name as the referenced table

This is a must. The alternative is verbose and will unfortunately
defy expectations in the cases where they don't follow particular
foreign key naming conventions.

Sorry, something went wrong.

@schutzie
Copy link

schutzie commented Dec 30, 2021

Foreign Films... ha.

Makes sense, but I don't know the names of any of the FKs on any
tables, existing functionality is still fine with me.

Sorry, something went wrong.

@pjb1008
Copy link

pjb1008 commented Dec 30, 2021

I like this too. As a side-effect, it'll encourage people to choose
meaningful names for constraints instead of letting the database
generate names automatically. I see many ERDs where the R has no
written description, so the meaning of the relationship drifts with
time in ways that are unhelpful. Shifting the focus of query writing
to be on following relationships rather than joining tables columns
should help with that.

I would like REF to be optional however. In the common case where
each table is referenced only once, the join is unambiguous without
REF being specified.

My personal preference would be to not split the syntax so much
according to the direction of the constraint, and instead to have a
syntax like:

table_a LEFT JOIN table_b USING table_a.constraint_name -- many to one, maybe
table_a RIGHT JOIN table_b USING table_a.constraint_name -- one to many
table_b LEFT JOIN table_a USING [table_b.]constraint_name [ REFERENCES table_b ] -- one to many
table_b RIGHT JOIN table_a USING [table_b.]constraint_name [ REFERENCES table_b ] -- many to one, maybe

I've written 'maybe' on the one-to-many relationships. You might have
a uniqueness constraint on the foreign key in table_a, turning it
into a one-to-one relationship. To know if you'll get multiple rows
from these queries, you need the schema.

Another feature you need is the ability to join, following a foreign
constraint as above, but imposing additional constraints on the join.
You see this problem already with USING. When you want to consider
all the rows, you can use USING. As soon as you want to do anything
more fancy, you have to use ON. That can stop you using USING later
in the query, and is much harder to visually check for correctness.
I'm thinking of joins like this:

employee LEFT JOIN rota USING (employee_id)
employee LEFT JOIN rota ON employee.employee_id=rota.employee_id AND rota.session > 5

For USING, this is annoying, but at least you're using the same
column names. Once you switch to using foreign key constraint names
for your joins, I'd say that's a showstopper problem; the queries
that have to be written out in full are longer and more complex, so
they're the ones that would benefit the most from a syntax based on
relationships. The proposed syntax needs to be able to combine both
the relationship and any additional restrictions for the join.

Sorry, something went wrong.

@gisborne
Copy link

gisborne commented Dec 30, 2021

Still too verbose.

If there's a declared foreign key relationship between the tables,
use it automatically, so just JOIN FOREIGN . The key name should only
be required if there is no declared foreign key or if there is more
than one.

Sorry, something went wrong.

@na-ka-na
Copy link

na-ka-na commented Dec 30, 2021 *
edited

Trust me I want to like this proposal. There's a lot to like. Only
two things bug me:

 1. foreign key constraint names are usually bad and too verbose.
    They often contain both table names and then the column names as
    well. It's easier to remember table names and column names than
    to remember foreign key constraint names
 2. JOIN FOREIGN f.distributors d ..... this syntax I'm not a fan of
    since it doesn't immediately tell me which table I'm joining
    with. Here of course you've named the foreign key same as the
    other table name, but that's usually not the case. How about...
    JOIN distributors d USING FOREIGN f.distributors .... slightly
    more verbose, but makes me feel much better. This way all my
    JOINs line up and look uniform, whether I'm using a foreign key
    or not

Sorry, something went wrong.

@dolmen
Copy link

dolmen commented Dec 30, 2021 *
edited

  * INNER JOIN
  * OUTER JOIN
  * LEFT JOIN
  * RIGHT JOIN
  * NATURAL JOIN

But this proposal is about:

  * JOIN FOREIGN

What justifies this naming inconsistency?

(Note: this is not an endorsement of the rest of the proposal)

Sorry, something went wrong.

@dvnrvn
Copy link

dvnrvn commented Dec 30, 2021

I don't like it. There is a cognitive cost to having to remember the
name of foreign keys, and it doesn't feel worth it when the tradeoff
is just making code concise by a trivial amount. Also, this is
another feature that wouldn't work in many data warehouse
implementations.

Sorry, something went wrong.

@stelf
Copy link

stelf commented Dec 30, 2021

interestingly no example with composite key. also note by @dolmen
holds. @pjb1008 also makes important point.

my 10c would be that more often than not the names of the keys
actually differ and LEFT OUTER more often than not has a second
non-key-related condition.

Sorry, something went wrong.

Sign up for free to join this conversation on GitHub. Already have an
account? Sign in to comment

  *  (c) 2021 GitHub, Inc.

  * Terms
  * Privacy
  * Security
  * Status
  * Docs
  * Contact GitHub
  * Pricing
  * API
  * Training
  * Blog
  * About

You can't perform that action at this time.
You signed in with another tab or window. Reload to refresh your
session. You signed out in another tab or window. Reload to refresh
your session.