[richiebful1]

A database system could implement a feature that automatically uses the foreign key to join to a table. Maybe some RDBMS out there does this.

For example, you have a many to one relationship between posts and users. Instead of this:

  select *
  from user as u
  join post as p
  on p.user_id = u.user_id;

You could do:

  alter table post
  add constraint fk_user_id foreign key (user_id) 
  references user.user_id;

  select *
  from user as u
  left referent join post as p;

Any good sql auto-completer will also look up the foreign key information and auto-generate the on clause for you as well. Redgate SQL Prompt (mssql only) is one of the best tools out there for this reason

[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.