11

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?

Leigh Riffel
  • 23,854
  • 16
  • 78
  • 152
TML
  • 1,364
  • 12
  • 20

3 Answers3

4

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.

Mike Sherrill 'Cat Recall'
  • 10,766
  • 1
  • 36
  • 43
4

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

Community
  • 1
Joe
  • 5,149
  • 1
  • 28
  • 39
  • Why would it be messy if you have more than one foreign key to the same table. If you are looking at it from the "parent" table perspective it should still be clear as to what record in the child you are interested in. – Beth Lang Jan 08 '11 at 18:09
  • @Bitoff you might have more than one relationship between the two tables, and so not a composite key, but actually more than one foreign key to the child table. Eg, in a previous job, we had a database of organizations, and in that table there are two links to my table of people -- head of the department, and the primary contact (usually, the secretary). There was a foreign key in the opposite direction, as staff members are assigned to a department, until we had to break it out to support many-to-many relationships. – Joe Jan 08 '11 at 18:27
  • I know none of the common RDBMSes do this - as the question states, I put the question out there to see if anyone had heard of anything new in this area. – TML Jan 09 '11 at 06:46
  • 1
    -1 This is not right about Oracle behaviour, and neither was your answer that you link to. As pointed out by Gary (the third comment to your answer), "natural join has never been the default". Incidentally, Tom Kyte is surely right when he calls natural join a bug waiting to happen – Jack Douglas May 02 '11 at 12:08
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.

Eric Humphrey - lotsahelp
  • 5,013
  • 1
  • 24
  • 28
  • No real "concern", just wondering if this is something anyone is looking into addressing. – TML Jan 11 '11 at 00:57
  • @TML I doubt anyone is worried about this as this is really in the domain of application logic to my understanding (where application logic is the things that don't pertain to running a database server, the things we get paid to do) – jcolebrand Jan 11 '11 at 04:16