5 ms·

It seems to me SQL is, paradoxically, too low level, much like assembly language, in the sense that it makes you specify the joins. Over and over and over again

It seems to me SQL is, paradoxically, too low level, much like assembly language, in the sense that it makes you specify the joins. Over and over and over again. Why doesn't it infer them from the structure of the database (the relations between tables)? If the answer is performance, well, that means we are in agreement, and SQL is not high-level.. In summary I think a QL should produce a table from a database when provided with 1) the set of field names desired, and 2) the set of constraints on the desired rows. Anything that goes beyond this is in need of justification. It is for this impedance mismatch (of understanding) that I have an aversion against SQL (therefore I am not well-versed). I basically see SQL as an exercise in complexity, resulting from the constraints prevalent in the seventies. Please prove me wrong!

djhworld 8y ago
I take the opposite view, SQL is very high level. It's a declarative language that in theory abstracts you away from the underlying execution environment, it's up to that engine to work out how to optimally execute the query, you as a programmer should focus on the data you require.
- davnicwil 8y ago
  This is all layers of abstraction, I think the GP is saying it's low level in the frame of reference of the data and relationships, which is true because you have to explicitly join everything together 'by hand' even though you could imagine abstractions that could be built on top, making this unnecessary. In the frame of reference of how the queries get executed, yes it's high level. But this is the same thing that could be said for, say, C - it's high level in the frame of reference of CPU instructions, low level in the frame of reference of memory management.
  - michelpp 8y ago
    > I think the GP is saying it's low level in the frame of reference of the data and relationships, which is true because you have to explicitly join everything together 'by hand' even though you could imagine abstractions that could be built on top, making this unnecessary. Data abstractions in SQL are accomplished with views: https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html Model your most common joins once into a view, then use the view instead of repeating the joins everywhere.
BenoitP 8y ago
You might be interested in this article: https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11/ https://blog.jooq.org/2018/02/20/type-safe-implicit-join-thr... With some more UX work around foreign key naming/declaration and careful attention from the developer (maybe helped by the IDE) to the caveats mentioned, this impedance mismatched could be reduced.
darkpuma 8y ago
It's not at all clear to me how implicit joins could have the same expressive power as explicit joins. How could the system infer if you wanted a LEFT JOIN, INNER JOIN, etc?
- deleted 8y ago
  [deleted]
avmich 8y ago
> Why doesn't it infer them from the structure of the database (the relations between tables)? That's because the structure doesn't always has the feature which you need this specific time to rely upon. And that's a feature, not a bug, which allows having flexibility between data structure and data queries. If you have a relation, DB engine maintains that, ensuring integrity defined as such. However, you are free to query non-integral (in this sense) references, which you're able to describe in queries. If you won't be able to specify joins, you'd have to always express relations in the structure. Since queries in SQL can be arbitrary, to reach parity with flexibility you'd have to have all possible relations. Then you have the problem of choosing what you need for this moment. In other words, some relations are not known before query time.
tomnipotent 8y ago
You're missing the forest for the trees. SQL & RDBMS' support use cases that might as well be cheats with how easy it makes it to do non-trivial queries. > In summary I think a QL should produce a table from a database In SQL we call these projections, which is just whatever columns are being returned from the amalgamation of tables you used in your query. This could include aggregations or window functions. Things are possible in SQL that are simply more difficult with anything else, and my time and my companies are more important than figuring out the perfect approach to every problem. SQL makes 80% of problems trivial, and the remaining 20% I can easily solve somewhere else (like Elasticsearch or sqlite or even custom serdes to disk).
blattimwind 8y ago
> in the sense that it makes you specify the joins. Over and over and over again. Why doesn't it infer them from the structure of the database (the relations between tables)? The answer is that there are different kind of joins (you may object that this is part of "set of constraints on the desired rows", however, then you are just specifying joins explicitly), and that generally speaking this is a non-unique property, i.e. you could usually infer several joins that represent dramatically different result sets that still have the same tuple archetype. I should point out that these issues aren't brought on by the added expressiveness of SQL, but rather, these issues are already present in basic relational algebra; I'd argue they're fairly fundamental.
keshavmr 8y ago
Given a schema, you can generate an infinite number of queries. The starting from the business question is a better choice. There are some interesting work done in this area, but still a long way to go. Here's an example: https://einstein.ai/static/images/pages/research/seq2sql/seq2sql.pdf https://einstein.ai/static/images/pages/research/seq2sql/seq...
mamcx 8y ago
> makes you specify the joins It not. SELECT * FROM "document", "line"; Is valid. "But what it do?" A CROSS JOIN. ie: a Cartesian product. That is not what most of the time you want. Why cartesian? Because this is the most fundamental join of all. Is like "fold" in functional languages (ie: You implement map, filter, etc on top of fold). Consider the JOIN clause a extra help. Not was always present: https://stackoverflow.com/questions/25161747/how-to-implement-sql-joins-without-using-join https://stackoverflow.com/questions/25161747/how-to-implemen... When using old fox pro, JOIN was not available. ---- Ok, but why not infer that if I join FROM "document", "line", it use the PKs??? That could be nice, but SQL is kind of EXPLICIT with not many implicit exceptions. And you don't wanna that behavior all the time. This is in contrast to OO. Where you can only use a kind of "join", but lack the capabilities to do much more without extra work. In fact, put JOINs not bother much. It fit well. Other aspect of the syntax of SQL are more annoying. ---- > If the answer is performance... SQL is not high-level So, if you are on python, and use a generator to pipe several inputs instead of materialize each step in a big list and pass to the other, because I'm aware that is not memory efficient, suddenly is not high-level? > I basically see SQL as an exercise in complexity, resulting from the constraints prevalent in the seventies. Please prove me wrong! I say is the TOTAL OPPOSITE. Sql is TOO SIMPLE! You are correct about it to be a product of the era. More exactly, the original plan was use SQL for the END USER to interface with the database. To write "off-shots" queries, not to be a proper programming interface. That is why, SELECT come first, instead of Last. Also, why SQL can't be composed, you don't have variables, can't express loops or recursive functions, and many many other really annoying stuff. SQL is alike CSS. Too simple. Not even lisp-like capable. P.D: Of course, exist a lot of extensions to cover for it (including WITH clauses), but the fact is, sql was not made for be a proper database language and it show. Why is this relevant? Because SQL with a bit more power could be abstract and allowed to fix any small shortcoming more naturally. ---- I'm in the hunt to build a relational language, not tied to a particular RDBMS, with full programming power. Not follow the SQL syntax, but more the LINQ style: http://tablam.org http://tablam.org Still in research/alpha, and looking for help!
o_nate 8y ago
There might be some value in an "INFER JOIN" keyword that tried to guess what relationship you wanted to use for the join, either based on defined relationships between the tables or based on statistics about the most common join conditions actually used.
LoneWolf123 8y ago
1. Postgres does provide natural joins: http://www.postgresqltutorial.com/postgresql-natural-join/ http://www.postgresqltutorial.com/postgresql-natural-join/ 2. Not all joins can be inferred like this: ` FROM People AS p JOIN Age_Groups AS ag ON ag.min_age <= p.age AND p.age < ag.max_age`
devit 8y ago
You can just create a view for each table with all other useful tables joined and query that. It would indeed be nice to do that automatically based on foreign keys; I suppose one could write a PostgreSQL extension for that (or maybe one exists?). Of course you still need the ability to perform joins that are not implied by the database structure.
astine 8y ago
"It seems to me SQL is, paradoxically, too low level, much like assembly language, in the sense that it makes you specify the joins. Over and over and over again. Why doesn't it infer them from the structure of the database (the relations between tables)? If the answer is performance, well, that means we are in agreement, and SQL is not high-level." First of all, it absolutely can and does infer joins if you use a natural join.[1] However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Not every join is along a clean primary key/foreign key axis. Sometimes you have datasets from different sources. Othertimes, you have more novel relationships between your tables. The whole point of the relational model is to give the end user a lot of flexibility in querying the database which means you need to specify the joins a lot of the time. 1. https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj...
- marcosdumay 8y ago
  > However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Some 90%+ of the times, you want exactly the join the database would infer, but natural join is basically useless, so you must repeat the basic join all the way every time. SQL did it backwards. The simple join keyword should mean natural join over a foreign key. A cross product should be created with the `product` keyword or something similar.
michelpp 8y ago
Encapsulate your joins in a view: https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html
lukaseder 8y ago
ORMs like Hibernate / jOOQ work around this by providing a convenient "dot notation" to navigate a foreign key path. This is particularly useful when keys are composite. See for example: https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11 https://blog.jooq.org/2018/02/20/type-safe-implicit-join-thr... Indeed, this should be available in the SQL language as well. I can't think of a performance problem, having implemented this in jOOQ. There's always a very obvious mapping between the notation and inner/left joins, which are reusable for common paths and subpaths. At least for navigating to-one relationships. To-many relationships raise more questions. Some RDBMS support a T1 JOIN T2 ON KEY syntax, which is nice as well, but the path notation is much more powerful: SELECT c.first_name, c.last_name c.address.street, c.address.zip, c.address.city FROM customer c Instead of SELECT c.first_name, c.last_name a.street, a.zip, a.city FROM customer c JOIN address a USING (address_id)

djhworld 8y ago

I take the opposite view, SQL is very high level. It's a declarative language that in theory abstracts you away from the underlying execution environment, it's up to that engine to work out how to optimally execute the query, you as a programmer should focus on the data you require.

davnicwil 8y ago
This is all layers of abstraction, I think the GP is saying it's low level in the frame of reference of the data and relationships, which is true because you have to explicitly join everything together 'by hand' even though you could imagine abstractions that could be built on top, making this unnecessary. In the frame of reference of how the queries get executed, yes it's high level. But this is the same thing that could be said for, say, C - it's high level in the frame of reference of CPU instructions, low level in the frame of reference of memory management.
- michelpp 8y ago
  > I think the GP is saying it's low level in the frame of reference of the data and relationships, which is true because you have to explicitly join everything together 'by hand' even though you could imagine abstractions that could be built on top, making this unnecessary. Data abstractions in SQL are accomplished with views: https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html Model your most common joins once into a view, then use the view instead of repeating the joins everywhere.

BenoitP 8y ago

You might be interested in this article: https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11/ https://blog.jooq.org/2018/02/20/type-safe-implicit-join-thr... With some more UX work around foreign key naming/declaration and careful attention from the developer (maybe helped by the IDE) to the caveats mentioned, this impedance mismatched could be reduced.

darkpuma 8y ago

It's not at all clear to me how implicit joins could have the same expressive power as explicit joins. How could the system infer if you wanted a LEFT JOIN, INNER JOIN, etc?

deleted 8y ago
[deleted]

avmich 8y ago

> Why doesn't it infer them from the structure of the database (the relations between tables)? That's because the structure doesn't always has the feature which you need this specific time to rely upon. And that's a feature, not a bug, which allows having flexibility between data structure and data queries. If you have a relation, DB engine maintains that, ensuring integrity defined as such. However, you are free to query non-integral (in this sense) references, which you're able to describe in queries. If you won't be able to specify joins, you'd have to always express relations in the structure. Since queries in SQL can be arbitrary, to reach parity with flexibility you'd have to have all possible relations. Then you have the problem of choosing what you need for this moment. In other words, some relations are not known before query time.

tomnipotent 8y ago

You're missing the forest for the trees. SQL & RDBMS' support use cases that might as well be cheats with how easy it makes it to do non-trivial queries. > In summary I think a QL should produce a table from a database In SQL we call these projections, which is just whatever columns are being returned from the amalgamation of tables you used in your query. This could include aggregations or window functions. Things are possible in SQL that are simply more difficult with anything else, and my time and my companies are more important than figuring out the perfect approach to every problem. SQL makes 80% of problems trivial, and the remaining 20% I can easily solve somewhere else (like Elasticsearch or sqlite or even custom serdes to disk).

blattimwind 8y ago

> in the sense that it makes you specify the joins. Over and over and over again. Why doesn't it infer them from the structure of the database (the relations between tables)? The answer is that there are different kind of joins (you may object that this is part of "set of constraints on the desired rows", however, then you are just specifying joins explicitly), and that generally speaking this is a non-unique property, i.e. you could usually infer several joins that represent dramatically different result sets that still have the same tuple archetype. I should point out that these issues aren't brought on by the added expressiveness of SQL, but rather, these issues are already present in basic relational algebra; I'd argue they're fairly fundamental.

keshavmr 8y ago

Given a schema, you can generate an infinite number of queries. The starting from the business question is a better choice. There are some interesting work done in this area, but still a long way to go. Here's an example: https://einstein.ai/static/images/pages/research/seq2sql/seq2sql.pdf https://einstein.ai/static/images/pages/research/seq2sql/seq...

mamcx 8y ago

> makes you specify the joins It not. SELECT * FROM "document", "line"; Is valid. "But what it do?" A CROSS JOIN. ie: a Cartesian product. That is not what most of the time you want. Why cartesian? Because this is the most fundamental join of all. Is like "fold" in functional languages (ie: You implement map, filter, etc on top of fold). Consider the JOIN clause a extra help. Not was always present: https://stackoverflow.com/questions/25161747/how-to-implement-sql-joins-without-using-join https://stackoverflow.com/questions/25161747/how-to-implemen... When using old fox pro, JOIN was not available. ---- Ok, but why not infer that if I join FROM "document", "line", it use the PKs??? That could be nice, but SQL is kind of EXPLICIT with not many implicit exceptions. And you don't wanna that behavior all the time. This is in contrast to OO. Where you can only use a kind of "join", but lack the capabilities to do much more without extra work. In fact, put JOINs not bother much. It fit well. Other aspect of the syntax of SQL are more annoying. ---- > If the answer is performance... SQL is not high-level So, if you are on python, and use a generator to pipe several inputs instead of materialize each step in a big list and pass to the other, because I'm aware that is not memory efficient, suddenly is not high-level? > I basically see SQL as an exercise in complexity, resulting from the constraints prevalent in the seventies. Please prove me wrong! I say is the TOTAL OPPOSITE. Sql is TOO SIMPLE! You are correct about it to be a product of the era. More exactly, the original plan was use SQL for the END USER to interface with the database. To write "off-shots" queries, not to be a proper programming interface. That is why, SELECT come first, instead of Last. Also, why SQL can't be composed, you don't have variables, can't express loops or recursive functions, and many many other really annoying stuff. SQL is alike CSS. Too simple. Not even lisp-like capable. P.D: Of course, exist a lot of extensions to cover for it (including WITH clauses), but the fact is, sql was not made for be a proper database language and it show. Why is this relevant? Because SQL with a bit more power could be abstract and allowed to fix any small shortcoming more naturally. ---- I'm in the hunt to build a relational language, not tied to a particular RDBMS, with full programming power. Not follow the SQL syntax, but more the LINQ style: http://tablam.org http://tablam.org Still in research/alpha, and looking for help!

o_nate 8y ago

There might be some value in an "INFER JOIN" keyword that tried to guess what relationship you wanted to use for the join, either based on defined relationships between the tables or based on statistics about the most common join conditions actually used.

LoneWolf123 8y ago

1. Postgres does provide natural joins: http://www.postgresqltutorial.com/postgresql-natural-join/ http://www.postgresqltutorial.com/postgresql-natural-join/ 2. Not all joins can be inferred like this: ` FROM People AS p JOIN Age_Groups AS ag ON ag.min_age <= p.age AND p.age < ag.max_age`

devit 8y ago

You can just create a view for each table with all other useful tables joined and query that. It would indeed be nice to do that automatically based on foreign keys; I suppose one could write a PostgreSQL extension for that (or maybe one exists?). Of course you still need the ability to perform joins that are not implied by the database structure.

astine 8y ago

"It seems to me SQL is, paradoxically, too low level, much like assembly language, in the sense that it makes you specify the joins. Over and over and over again. Why doesn't it infer them from the structure of the database (the relations between tables)? If the answer is performance, well, that means we are in agreement, and SQL is not high-level." First of all, it absolutely can and does infer joins if you use a natural join.[1] However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Not every join is along a clean primary key/foreign key axis. Sometimes you have datasets from different sources. Othertimes, you have more novel relationships between your tables. The whole point of the relational model is to give the end user a lot of flexibility in querying the database which means you need to specify the joins a lot of the time. 1. https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturaljoin.html https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj...

marcosdumay 8y ago
> However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Some 90%+ of the times, you want exactly the join the database would infer, but natural join is basically useless, so you must repeat the basic join all the way every time. SQL did it backwards. The simple join keyword should mean natural join over a foreign key. A cross product should be created with the `product` keyword or something similar.

michelpp 8y ago

Encapsulate your joins in a view: https://www.postgresql.org/docs/current/sql-createview.html https://www.postgresql.org/docs/current/sql-createview.html

lukaseder 8y ago

ORMs like Hibernate / jOOQ work around this by providing a convenient "dot notation" to navigate a foreign key path. This is particularly useful when keys are composite. See for example: https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11 https://blog.jooq.org/2018/02/20/type-safe-implicit-join-thr... Indeed, this should be available in the SQL language as well. I can't think of a performance problem, having implemented this in jOOQ. There's always a very obvious mapping between the notation and inner/left joins, which are reusable for common paths and subpaths. At least for navigating to-one relationships. To-many relationships raise more questions. Some RDBMS support a T1 JOIN T2 ON KEY syntax, which is nice as well, but the path notation is much more powerful: SELECT c.first_name, c.last_name c.address.street, c.address.zip, c.address.city FROM customer c Instead of SELECT c.first_name, c.last_name a.street, a.zip, a.city FROM customer c JOIN address a USING (address_id)