4 ms·
Are you referring to recursive common table expressions? These do allow graph queries. They are quite different to the proprietary extensions, though, and much
by twic 7y ago
Are you referring to recursive common table expressions? These do allow graph queries. They are quite different to the proprietary extensions, though, and much more awkward.
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
Instead of:
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
Given that dots already mean something in SQL, maybe it would have to be:
select e.name, e->department->head->assistant.name from employees
Once you've got that, it would not be a huge leap to add a recursive version of this operator.
[1] https://www.objectdb.com/java/jpa/query/jpql/path#Navigation_through_Path_Expressions https://www.objectdb.com/java/jpa/query/jpql/path#Navigation...
- deleted 7y ago[deleted]
- dragonwriter 7y ago> 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 ...on the relevant tables.