4 ms·

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 suscep

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

> 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?