> Some RDBs provide extensions to the SQL standard to do some basic graph traversals
This has been standardized since the SQL-1999 standard.
This has been standardized since the SQL-1999 standard.
I would love to see SQL grow a concise navigation/join syntax like JPQL's [1]; imagine writing:
select e.name, e.department.head.assistant.name from employees
select e.name, a.name
from employees e,
join departments d using (department_id)
join employees h on d.head_id = h.employee_id
join employees a on h.assistant_id = a.employee_id
select e.name, e->department->head->assistant.name from employees
[1] https://www.objectdb.com/java/jpa/query/jpql/path#Navigation...
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>
FOREIGN KEY department_id REFERENCES departments AS department
FOREIGN KEY head_id REFERENCES employees AS head
FOREIGN KEY assistant_id REFERENCES employees AS assistant
The various implementations of SQL are so different from each other, and from the spec, that I no longer find it useful to consider them the same language.
Is there a caniuse.com for SQL?
Recursive CTEs with standard syntax are in virtually every current-versiom notable SQL-based RDBMS, including SQLite. I think MySQL 8.0 was the last of the major ones to add support.
Not exactly the same, but: https://modern-sql.com/