4 ms·

> FROM permission p > LEFT JOIN role r WITH p->permission_role_id_fkey = r <snip> You're using the alias "r" here for two different things: - a table ali

> FROM permission p > LEFT JOIN role r WITH p->permission_role_id_fkey = r <snip> You're using the alias "r" here for two different things: - a table alias for the role table - a whatever (foreign key index object?) alias for p->permission_role_id_fkey > the special but common case when joining on columns that exactly match a foreign key I've been programming with RDBMSs since 1996. I'd say that approximately 99% of the thousands of JOINs I've written were based on PKs/FKs. The example that you are attempting to improve already operates on PKs/FKs. I don't understand the point of this proposed improvement at all

JoelJacobson 5y ago
> You're using the alias "r" here for two different things: I agree this is a bit confusing. The user "BeefWellington" came up with a better idea, to skip the "= r" part, since it's redundant. That way, you would only need some new keyword, to indicate you want to perform a foreign key join, and specify the referencing or referenced table alias (depending on which direction the join is made, i.e. what table alias that has the foreign key), together with the name of the foreign key. > The example that you are attempting to improve already operates on PKs/FKs. > I don't understand the point of this proposed improvement at all The example operates on PKs/FKs column, yes, but they are specified manually. I listed a number of possible benefits under the section "POSSIBLE BENEFITS" in my original post, I think these explains what improvements I specifically see.
- coolgeek 5y ago
  You list three proposed benefits. (I'm listing them in the order I will address them): 1) make non-key joins stand out 2) conciser syntax 3) less susceptible to joining on wrong columns 1) I'll concede this. But I don't see enough value in it to add (and force people to learn) an alternative JOIN syntax 2) That's not at all clear from your example. Nor does it necessarily follow from the proposal in general 3) This would be your strongest argument. But it's not really a problem in the real world. If/when this happens, it does so for one of three reasons: - the programmer misunderstood the data model - the programmer misunderstands JOINs in general - how they work and how to write them - the programmer wasn't paying attention to what they were doing - e.g. copy/pasted the wrong thing All of these are bigger problems - they transcend the the realm of JOINs. As with 1), I don't see enough value in it to make the overall language (and parsers) more complex. SQL has lots of warts. It is an unpleasant language. It is a difficult language to learn (initially). But it (mostly) works. If I was going to advocate for a change like this, I'd propose a new join type called something like FK JOIN: SELECT * from table1 FK JOIN table2 This would work sort of like NATURAL JOIN, but without any possibility of ambiguity (and only on the key column).
  - JoelJacobson 5y ago
    > SELECT * from table1 > FK JOIN table2 What if table1 has been joined in twice, to what table alias is table2 joined against? Example: SELECT * FROM table1 a CROSS JOIN table1 b FK JOIN table2 Would table2 be joined against "b" or "a"? I would guess "b" and guess the idea is to always let FK JOIN join against the previous join?
    - coolgeek 5y ago
      Yeah, that's a problem. FK JOIN was a casually tossed off addition that, in retrospect, I shouldn't have included.
      - JoelJacobson 5y ago
        As for your other comments, sounds like we are in agreement on (1) and (3), but just weigh pros/cons differently. > 2) That's not at all clear from your example. Nor does it necessarily follow from the proposal in general Can you provide a counter-example that will not be conciser when written using the foreign key join syntax?

JoelJacobson 5y ago

> You're using the alias "r" here for two different things: I agree this is a bit confusing. The user "BeefWellington" came up with a better idea, to skip the "= r" part, since it's redundant. That way, you would only need some new keyword, to indicate you want to perform a foreign key join, and specify the referencing or referenced table alias (depending on which direction the join is made, i.e. what table alias that has the foreign key), together with the name of the foreign key. > The example that you are attempting to improve already operates on PKs/FKs. > I don't understand the point of this proposed improvement at all The example operates on PKs/FKs column, yes, but they are specified manually. I listed a number of possible benefits under the section "POSSIBLE BENEFITS" in my original post, I think these explains what improvements I specifically see.

coolgeek 5y ago
You list three proposed benefits. (I'm listing them in the order I will address them): 1) make non-key joins stand out 2) conciser syntax 3) less susceptible to joining on wrong columns 1) I'll concede this. But I don't see enough value in it to add (and force people to learn) an alternative JOIN syntax 2) That's not at all clear from your example. Nor does it necessarily follow from the proposal in general 3) This would be your strongest argument. But it's not really a problem in the real world. If/when this happens, it does so for one of three reasons: - the programmer misunderstood the data model - the programmer misunderstands JOINs in general - how they work and how to write them - the programmer wasn't paying attention to what they were doing - e.g. copy/pasted the wrong thing All of these are bigger problems - they transcend the the realm of JOINs. As with 1), I don't see enough value in it to make the overall language (and parsers) more complex. SQL has lots of warts. It is an unpleasant language. It is a difficult language to learn (initially). But it (mostly) works. If I was going to advocate for a change like this, I'd propose a new join type called something like FK JOIN: SELECT * from table1 FK JOIN table2 This would work sort of like NATURAL JOIN, but without any possibility of ambiguity (and only on the key column).
- JoelJacobson 5y ago
  > SELECT * from table1 > FK JOIN table2 What if table1 has been joined in twice, to what table alias is table2 joined against? Example: SELECT * FROM table1 a CROSS JOIN table1 b FK JOIN table2 Would table2 be joined against "b" or "a"? I would guess "b" and guess the idea is to always let FK JOIN join against the previous join?
  - coolgeek 5y ago
    Yeah, that's a problem. FK JOIN was a casually tossed off addition that, in retrospect, I shouldn't have included.
    - JoelJacobson 5y ago
      As for your other comments, sounds like we are in agreement on (1) and (3), but just weigh pros/cons differently. > 2) That's not at all clear from your example. Nor does it necessarily follow from the proposal in general Can you provide a counter-example that will not be conciser when written using the foreign key join syntax?