3 ms·

You can JOIN USING in Postgres. It’s healthy to make it explicitly though, and queries should not be being written all the time anyway.

zmmmmm 5y ago
Yeah ... but I just don't understand, why rely on weak naming conventions when there is an explicit foreign key relationship that tells you the exact column and table? I shouldn't need to even write a join keyword at all, just select f.bar_id->some_bar_table_column from foo f ... if the foo table has a bar_id column with a foreign key constraint to bar's primary key. If the foreign key constraint is defined and unique just use it and don't make me type out all the redundant join rubbish. You could then even do deep nesting with no more syntax: select f.bar_id->baz_id->some_baz_table_column from foo f
- twoodfin 5y ago
  https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_IMPLICITJOINS https://docs.intersystems.com/irislatest/csp/docbook/DocBook... -> is really just syntactic sugar for a left outer join, which implicitly becomes an inner join if a foreign key constraint is present. How it ends up resolved is up to the optimizer, whether there's a foreign key available it might exploit or not.
- JoelJacobson 5y ago
  > "select f.bar_id->some_bar_table_column" Something like this was actually my first iteration of this idea, to join based on the column name(s). But then someone helped me think about what if the column is involved in two different foreign keys (to two different tables)? Not common, but absolutely possible. That's why just naming the column(s) isn't precise enough, except in most cases. Even if no such ambiguity was allowed, i.e. if all unique sets of column name(s) referenced exactly one table, you would still need to think about how the syntax would look like for foreign keys on multiple columns.
  - zmmmmm 5y ago
    I guess this comes back to the whole concept of something being ergonomic - how well is it designed to comfortably enable the common use case vs. forcing users to pay a tax and do something annoying on the 95% case to allow for the 5% case to work. The scenario you mention is rare enough in my experience compared to a simple DAG style hierarchical table structure that we should totally optimise the query language for the former and then just add in escape hatches for the less common scenarios. I'm sort of surprised that there is nothing similar to the CSS SASS/SCSS type idea that has ever taken off in SQL land ...
    - JoelJacobson 5y ago
      I don't think something that works in 95% of the cases would be accepted by the SQL committee. I think at least you would need to find a way to follow foreign keys on multiple columns. I can see why working with column names would be nice, since we then would not have to deal with foreign key names at all, which is another thing to keep track of mentally. But the only way that would work would be if we would forbid creating multiple foreign keys on the same set of columns, only then would the set of columns uniquely identify what foreign key to follow. This is much more invasive though, as it could break existing data models.

zmmmmm 5y ago

Yeah ... but I just don't understand, why rely on weak naming conventions when there is an explicit foreign key relationship that tells you the exact column and table? I shouldn't need to even write a join keyword at all, just select f.bar_id->some_bar_table_column from foo f ... if the foo table has a bar_id column with a foreign key constraint to bar's primary key. If the foreign key constraint is defined and unique just use it and don't make me type out all the redundant join rubbish. You could then even do deep nesting with no more syntax: select f.bar_id->baz_id->some_baz_table_column from foo f

twoodfin 5y ago
https://docs.intersystems.com/irislatest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_IMPLICITJOINS https://docs.intersystems.com/irislatest/csp/docbook/DocBook... -> is really just syntactic sugar for a left outer join, which implicitly becomes an inner join if a foreign key constraint is present. How it ends up resolved is up to the optimizer, whether there's a foreign key available it might exploit or not.
JoelJacobson 5y ago
> "select f.bar_id->some_bar_table_column" Something like this was actually my first iteration of this idea, to join based on the column name(s). But then someone helped me think about what if the column is involved in two different foreign keys (to two different tables)? Not common, but absolutely possible. That's why just naming the column(s) isn't precise enough, except in most cases. Even if no such ambiguity was allowed, i.e. if all unique sets of column name(s) referenced exactly one table, you would still need to think about how the syntax would look like for foreign keys on multiple columns.
- zmmmmm 5y ago
  I guess this comes back to the whole concept of something being ergonomic - how well is it designed to comfortably enable the common use case vs. forcing users to pay a tax and do something annoying on the 95% case to allow for the 5% case to work. The scenario you mention is rare enough in my experience compared to a simple DAG style hierarchical table structure that we should totally optimise the query language for the former and then just add in escape hatches for the less common scenarios. I'm sort of surprised that there is nothing similar to the CSS SASS/SCSS type idea that has ever taken off in SQL land ...
  - JoelJacobson 5y ago
    I don't think something that works in 95% of the cases would be accepted by the SQL committee. I think at least you would need to find a way to follow foreign keys on multiple columns. I can see why working with column names would be nice, since we then would not have to deal with foreign key names at all, which is another thing to keep track of mentally. But the only way that would work would be if we would forbid creating multiple foreign keys on the same set of columns, only then would the set of columns uniquely identify what foreign key to follow. This is much more invasive though, as it could break existing data models.