2 ms·

They should use NATURAL JOIN if auto-selection of the join keys is that important. I wouldn't recommend relying on that type of automagic behavior because it is

by pdkl95 5y ago

They should use NATURAL JOIN if auto-selection of the join keys is that important. I wouldn't recommend relying on that type of automagic behavior because it is very brittle; adding a column to a table might accidentally break existing queries.

kinjba11 5y ago
A natural join selects all matching names which is not the same as what the article is saying. The database already knows about foreign keys. Why do I have to say select * from A a join B b on b.Id = A.OtherId SQL IDEs will auto-suggest that "on b.Id = A.OtherId" because it's the foreign key and could be inferred. That's what you need 99% of the time.
- cerved 5y ago
  Partly for persistence, I would imagine. The query you write today should function the same tomorrow and a year from now. If you want this implicit behavior, you may use natural joins
- pdkl95 5y ago
  > A natural join selects all matching names Yes, which is why I called it "brittle". It can easily break if you aren't careful with column names. However, if someone really wanted (unwisely, in my opinion) to have the DB automatically handle their inner joins, they could use NATURAL JOIN. This would require being very careful with you column names, which is why I (strongly) recommend doing it the usual way with INNER JOIN. > the foreign key and could be inferred Only in simple cases. If there are multiple foreign keys referencing the same table, automagically inferring joins will probably do the wrong thing, just like NATURAL JOIN. > IDEs will auto-suggest The editor/IDE is where this type of automagic inference should be done! You have the opportunity to inspect the suggestion and fix it if necessary, instead of hoping the DB does the right thing at runtime.
erezsh 5y ago
> adding a column to a table might accidentally break existing queries Just a column, no. Adding a new foreign key might, but that's something that a type-checker/compiler can let you know about. > They should Use NATURAL JOIN NATURAL JOINs are better than nothing, but they're flawed too. Column names are supposed to convey meaning or intention, not just the type of data that they contain.
faho 5y ago
Yes, but that's only automagic and brittle because it's not an actual join on the foreign key. It's a join on whatever happens to have the same name! Which sometimes happens to be the foreign key. The point here is that SQL doesn't have a way of specifying "join on whatever the foreign key is, and nothing else".

by pdkl95 5y ago

kinjba11 5y ago

A natural join selects all matching names which is not the same as what the article is saying. The database already knows about foreign keys. Why do I have to say select * from A a join B b on b.Id = A.OtherId SQL IDEs will auto-suggest that "on b.Id = A.OtherId" because it's the foreign key and could be inferred. That's what you need 99% of the time.

cerved 5y ago
Partly for persistence, I would imagine. The query you write today should function the same tomorrow and a year from now. If you want this implicit behavior, you may use natural joins
pdkl95 5y ago
> A natural join selects all matching names Yes, which is why I called it "brittle". It can easily break if you aren't careful with column names. However, if someone really wanted (unwisely, in my opinion) to have the DB automatically handle their inner joins, they could use NATURAL JOIN. This would require being very careful with you column names, which is why I (strongly) recommend doing it the usual way with INNER JOIN. > the foreign key and could be inferred Only in simple cases. If there are multiple foreign keys referencing the same table, automagically inferring joins will probably do the wrong thing, just like NATURAL JOIN. > IDEs will auto-suggest The editor/IDE is where this type of automagic inference should be done! You have the opportunity to inspect the suggestion and fix it if necessary, instead of hoping the DB does the right thing at runtime.

erezsh 5y ago

> adding a column to a table might accidentally break existing queries Just a column, no. Adding a new foreign key might, but that's something that a type-checker/compiler can let you know about. > They should Use NATURAL JOIN NATURAL JOINs are better than nothing, but they're flawed too. Column names are supposed to convey meaning or intention, not just the type of data that they contain.

faho 5y ago

Yes, but that's only automagic and brittle because it's not an actual join on the foreign key. It's a join on whatever happens to have the same name! Which sometimes happens to be the foreign key. The point here is that SQL doesn't have a way of specifying "join on whatever the foreign key is, and nothing else".