It seems strange to me that, when I've defined a foreign key, the engine cannot use this information to automatically figure out the correct JOIN expressions, but instead requires me to re-type the same clauses. Are there any databases, perhaps some kind of research project, that will inspect the existing foreign keys?
I'm not aware of any that use foreign keys (and that would can be messy if you have more than one foreign key back to the same table)
It would be really cool if I could write this
from calendar join table1
and the dbms would know that it needs to evaluate this.
from calendar left join table1 on (work_date between (cal_date - 13) and cal_date)
Really cool. But not every join is an equi-join.
NATURAL JOIN is in the SQL standard. But I'm pretty sure I haven't used it in the last 25 years.
If you find yourself writing the same join clauses over and over, maybe you just need more views.