> I would love to see SQL grow a concise navigation/join syntax
Yeah, what you need is a specification of a name to use for the referenced table in implicit joins when you sepcify an FK, then if you reference the linked table, it performs the join (to bring in a different subthread, such an implicit join specification could be the mechanism of opting-in for index-free adjacency on that FK, too, so you'd combine syntactic support with the supporting query optimization.)
Maybe something like:
REFERENCES <table> [(<key columns>)] AS <link-name>
So, your example would be be supported by (assuming all the FKs are to PKs so you don't need columns specified on the FKs):
FOREIGN KEY department_id REFERENCES departments AS department
FOREIGN KEY head_id REFERENCES employees AS head
FOREIGN KEY assistant_id REFERENCES employees AS assistant
Yeah, what you need is a specification of a name to use for the referenced table in implicit joins when you sepcify an FK, then if you reference the linked table, it performs the join (to bring in a different subthread, such an implicit join specification could be the mechanism of opting-in for index-free adjacency on that FK, too, so you'd combine syntactic support with the supporting query optimization.)
Maybe something like:
So, your example would be be supported by (assuming all the FKs are to PKs so you don't need columns specified on the FKs): ...on the relevant tables.