[sgk284]

SQL already covers this use case with the `using` keyword. But you need to specify the shared column name. If you didn't need to specify the column then adding a new foreign key between the tables would make existing queries ambiguous and break backwards compatibility. See:

https://www.postgresql.org/docs/current/queries-table-expres....

[dragonwriter]

> SQL already covers this use case with the `using` keyword.

No, “using” doesn’t automatically join by foreign key, it joins by explicitly-provided column name which must be identical between the two tables.

> If you didn’t need to specify the column then adding a new foreign key between the tables would make existing queries ambiguous and break backwards compatibility.

Not if you specified with constraint name rather than the column name (which also works for multi-column foreign keys without having to reiterate all the columns, being much more DRY than current SQL USING.)

Better, with that approach you could also allow fully implicit joins, using the foreign key constraint name as if it were an row-valued field in the referencing table.

E.g.:

  CREATE TABLE employees (
    id BIGSERIAL PRIMARY KEY,’
    name VARCHAR,
    manager_id BIGINT CONSTRAINT manager REFERENCES employees
  );

would let you do:

  SELECT name as employee_name, manager.name as manager_name
  FROM employees

as syntax sugar for:

  SELECT ee.name as employee_name, mgr.name as manager_name
  FROM employees ee 
  LEFT JOIN employees mgr ON (
    employees.manager_id = mgr.id
  )

[Justsignedup]

wow i absolutely did not know this. amazing.

[marcosdumay]

Using is a bad standard. It doesn't do the obvious thing (that is following foreign keys), it requires a heavy amount of bad conventions, it fails for the not too rare case where you need more than one FK between the same two tables, and if you go strictly with the standard (good for Postgres that it doesn't), it breaks the table-aware semantics of column names.

Meanwhile, the obvious natural way to make joins isn't available.