3 ms·

> Some RDBs provide extensions to the SQL standard to do some basic graph traversals This has been standardized since the SQL-1999 standard.

by u320 7y ago

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.
ken 7y ago
Yet SQL still hasn’t standardized “CREATE INDEX” yet, has it? How many databases have implemented graph traversals, and do they even use the same syntax? 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?
- dragonwriter 7y ago
  > How many databases have implemented graph traversals, and do they even use the same syntax? 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.
- taffer 7y ago
  > Is there a caniuse.com for SQL? Not exactly the same, but: https://modern-sql.com/ https://modern-sql.com/
- bcoates 7y ago
  CREATE INDEX is fundamentally an implementation detail of the RDBMS engine; there can be no meaningful standardization there.

> Some RDBs provide extensions to the SQL standard to do some basic graph traversals This has been standardized since the SQL-1999 standard.

by u320 7y ago

> Some RDBs provide extensions to the SQL standard to do some basic graph traversals This has been standardized since the SQL-1999 standard.

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.

ken 7y ago

Yet SQL still hasn’t standardized “CREATE INDEX” yet, has it? How many databases have implemented graph traversals, and do they even use the same syntax? 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?

dragonwriter 7y ago
> How many databases have implemented graph traversals, and do they even use the same syntax? 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.
taffer 7y ago
> Is there a caniuse.com for SQL? Not exactly the same, but: https://modern-sql.com/ https://modern-sql.com/
bcoates 7y ago
CREATE INDEX is fundamentally an implementation detail of the RDBMS engine; there can be no meaningful standardization there.