Are there any database engines which will intuit the join condition based on the existing foreign keys?

Question

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?

Answer 1

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.

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

Answer 2

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)

But Oracle will base joins off of name of columns if you don't specify a specific join type nor a join condition, or if you use NATURAL JOIN

Answer 3

There are auto-complete utils for your editor of choice to auto-suggest appropriate join statements if typing is the concern. RedGate's SQL Prompt for handling SQL Server in SSMS & Visual Studio. I believe Quest's TOAD will handle Oracle and some of the other more popular RDMSs.