Why aren't primary key / foreign key matches used for joins?

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.

A foreign key is meant to constrain the data. ie enforce referential integrity. That's it. Nothing else.

1. You can have multiple foreign keys to the same table. Consider the following where a shipment has a starting point, and an ending point.

   table: USA_States
   StateID
   StateName
   
   table: Shipment
   ShipmentID
   PickupStateID Foreign key
   DeliveryStateID Foreign key
   

   You may want to join based on the pickup state. Maybe you want to join on the delivery state. Maybe you want to perform 2 joins for both! The sql engine has no way of knowing what you want.

2. You'll often cross join scalar values. Although scalars are usually the result of intermediate calculations, sometimes you'll have a special purpose table with exactly 1 record. If the engine tried to detect a foriegn key for the join.... it wouldn't make sense because cross joins never match up a column.

3. In some special cases you'll join on columns where neither is unique. Therefore the presence of a PK/FK on those columns is impossible.

4. You may think points 2 and 3 above are not relevant since your questions is about when there IS a single PK/FK relationship between tables. However the presence of single PK/FK between the tables does not mean you can't have other fields to join on in addition to the PK/FK. The sql engine would not know which fields you want to join on.

5. Lets say you have a table "USA_States", and 5 other tables with a FK to the states. The "five" tables also have a few foreign keys to each other. Should the sql engine automatically join the "five" tables with "USA_States"? Or should it join the "five" to each other? Both? You could set up the relationships so that the sql engine enters an infinite loop trying to join stuff together. In this situation it's impossible fore the sql engine to guess what you want.

In summary: PK/FK has nothing to do with table joins. They are separate unrelated things. It's just an accident of nature that you often join on the PK/FK columns.

Would you want the sql engine to guess if it's a full, left, right, or inner join? I don't think so. Although that would arguably be a lesser sin than guessing the columns to join on.

the concept of "joinability." Relations r1 and r2 are joinable if and only if attributes with the same name are of the same type... this concept applies not only to join as such but to various other operations [such as union] as well.

SQL and Relational Theory: How to Write Accurate SQL Code By C. J. Date

Standard SQL already has such a feature, known as NATURAL JOIN, and has been implemented in mySQL.

Although your suggestion is not quite as worthy, it seems a reasonable one. With SQL Server (which lacks support for NATURAL JOIN), I use SQL Prompt in Management Studio: when writing an INNER JOIN its InteliSense suggests ON clauses based on both common attribute names and foreign keys and I find it very useful. I've no great desire to see a new (standard) SQL join type for this, though.