Hacker News Reader

Comment by JoelJacobson | Hacker News Reader

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

View on HN

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).

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

Yeah, that's a problem.

FK JOIN was a casually tossed off addition that, in retrospect, I shouldn't have included.

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?

Reply on news.ycombinator.com