In Favor of SQL: Add Follow Foreign Key Syntax

3 points

5 years ago

As noted by others [1], a very common case for joins is to follow foreign keys.

I suggest using the fact foreign keys are constraints with unique names, and using these names to explicitly specify what column(s) to join between the two foreing key tables.

In PostgreSQL [2], foreign key contraint names only need to be unique per table, which allows using the foreign table "as is" as the constraint name, which allows for nice short names. In other databases, the names will just need to be a little longer, since they need to be unique per schema according to the SQL spec.

Given this schema:

  CREATE TABLE baz (
  id integer NOT NULL,
  PRIMARY KEY (id)
  );
  
  CREATE TABLE bar (
  id integer NOT NULL,
  baz_id integer,
  PRIMARY KEY (id),
  CONSTRAINT baz FOREIGN KEY (baz_id) REFERENCES baz
  );
  
  CREATE TABLE foo (
  id integer NOT NULL,
  bar_id integer,
  PRIMARY KEY (id),
  CONSTRAINT bar FOREIGN KEY (bar_id) REFERENCES bar
  );

We could write a normal SQL query like this:

  SELECT
    bar.id AS bar_id,
    baz.id AS baz_id
  FROM foo
  JOIN bar ON bar.id = foo.bar_id
  LEFT JOIN baz ON baz.id = bar.baz_id
  WHERE foo.id = 123

I suggest adding a new binary operator, taking the table alias to join from as left operand, and the name of the foreign kery contraint to follow as the right operand.

Perhaps "->" could be used for this purpose, since it's currently not used by the SQL spec in the FROM clause.

This would allow rewriting the above query into this:

  SELECT
    bar.id AS bar_id,
    baz.id AS baz_id
  FROM foo
  JOIN foo->bar
  LEFT JOIN bar->baz
  WHERE foo.id = 123

Where e.g. "foo->bar" means:

  follow the foreign key constraint named "bar" on the table/alias "foo"

If the same join type is desired for multiple joins, another idea is to allow chaining the operator:

  SELECT
    bar.id AS bar_id,
    baz.id AS baz_id
  FROM foo
  LEFT JOIN foo->bar->baz
  WHERE foo.id = 123

Which would cause both joins to be left joins.

[1] https://scattered-thoughts.net/writing/against-sql/ [2] https://www.postgresql.org/

11 comments

CREATE TABLE baz ( id integer NOT NULL, PRIMARY KEY (id) ); CREATE TABLE bar ( id integer NOT NULL, baz_id integer, PRIMARY KEY (id), CONSTRAINT baz FOREIGN KEY (baz_id) REFERENCES baz ); CREATE TABLE foo ( id integer NOT NULL, bar_id integer, PRIMARY KEY (id), CONSTRAINT bar FOREIGN KEY (bar_id) REFERENCES bar );