5 ms·
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 s
by 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 agoPartly 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.