Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 9 down vote favorite
1

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?

share|improve this question

3 Answers

up vote 3 down vote

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

share|improve this answer
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. – BitOff Jan 8 '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 8 '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 9 '11 at 6: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 2 '11 at 12:08
up vote 3 down vote

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.

share|improve this answer
+1 Good comment about the views – Michael Hedgpeth May 12 '11 at 1:47
up vote 2 down vote

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.

share|improve this answer
No real "concern", just wondering if this is something anyone is looking into addressing. – TML Jan 11 '11 at 0: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 4:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.