As far as I could find out many DBMSs (e.g. mysql, postgres, mssql) use fk and pk combinations only to constrain changes to data, but they are rarely natively used to automatically select columns to join (like natural join does with names). Why is that? If you've already defined a relationship between 2 tables with a pk/fk, why can't the database figure out that if I join those tables I want to join them on the pk/fk columns?

EDIT: to clarify this a bit:

suppose I have a table1 and a table2. table1 one has a foreign key on column a, which references to the primary key on table2, the column b. Now if I join these tables, I'll have to do something like this:

SELECT * FROM table1
JOIN table2 ON table1.a = table2.b

However, I already defined using my keys that table1.a references to table2.b, so it seems to me that it shouldn't be to hard to make a DBMS system automatically use table1.a and table2.b as the join columns, such that one can simply use:

SELECT * FROM table1
AUTO JOIN table2

However, many DBMS don't seem to implement something like this.

12 Answers 12


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.

| improve this answer | |
  • 1
    Likely there would be a small performance hit in fact as it has to figure out the join columns rather than sprcifying them. – HLGEM Mar 16 '12 at 13:37
  • 1
    @HLGEM, That might be cached, and also be irrelevant for larger queries. The advantage is that we can be sure keys are not missed due to some human error. – Pacerier May 10 '15 at 6:55
  • Adding and altering columns could also break NATURAL JOIN (which is why I usually avoid them), but I don't think that in itself should mean that a dbms couldn't implement an automatic way to join tables based on foreign keys. – Jay K Aug 1 '16 at 16:01
  • 3
    Many cases? On a thousand table DB, I only have some few cases of more-than-1 relation between two tables. Anyway, that's not a problem, it would be sufficient to add relation name like AUTO JOIN mytable THROUGH myrelation, it would be very nice. – Teejay Dec 19 '17 at 10:12
  • That's what we do in our custom-built .NET SQL builder, with intellisense, like InnerJoin(SRC_TABLE.rDEST_TABLE.REL_NAME_F01) – Teejay Dec 19 '17 at 10:15

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
    table: Shipment
    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.

| improve this answer | |
  • 9
    I consider foreign keys and normalization to be very relevant to table joins. – Michael Durrant Mar 16 '12 at 6:07
  • 3
    Your arguments hold when the normal JOIN keyword always tries to match that (as I did wrong in my example, I'll fix that). However, many joins can be derived directly from only the joins so I don't see any reason why there can't be any explicit syntax for joining them. Many DBMSs do have a natural join, which does basically the same thing but with column names (=bad). The same thing could be done with this type of join, e.g. by specifying an AUTO JOIN operation. – Tiddo Mar 16 '12 at 7:56
  • 7
    "It's just an accident of nature that you often join on the PK/FK columns" -- I'm not convinced! – onedaywhen Mar 16 '12 at 12:24
  • 2
    "Normalization?" I think the thinking here is that if you started with a 1NF relvar then decomposed into 6NF relvars then the chances are a) they would have foreign keys on implementation and b) they would be frequently joined in queries. – onedaywhen Mar 16 '12 at 13:59
  • 5
    I would upvote if there wasn't that "PK/FK has nothing to do with table joins." – ypercubeᵀᴹ Mar 16 '12 at 14:11

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.

| improve this answer | |
  • 1
    Natural join & joining on common columns is distinct from & orthogonal to the notion of joining on FK-PK. (See my answer.) – philipxy Oct 14 '17 at 22:02
  • @philipxy: agreed, I didn't intend to imply otherwise. (Your's is an excellent answer!) – onedaywhen Feb 4 '18 at 13:15

SQL came first!

Foreign Keys and Foreign Key constraints came later and are essentially an optimization for "transaction" style applications.

Relational databases were originally conceived as a method of applying complex queries on sets of data in a way that was mathematically provable using relational algebra. I.E. for a given set of data and a given query there is always a single correct answer.

Relational databases have come a long way since then, and there primary use as the persistence layer for transactional systems was not what CODD et. all envisaged.

However the ANSI standards body for all its conflicting goals and vendor politics has always striven to preserve "mathematically provable" properties of SQL.

If you allowed the database to infer the join properties from "hidden" foreign key data you would lose this property (consider the ambiguity if there was more than one set of foreign keys defined).

Also a programmer reading the SQL would not necessarily know what foreign keys were currently defined for the two tables, and, would need to examine the database schema to work out what the query was doing.

| improve this answer | |
  • 3
    Thanks, this made sense to me! However, don't natural joins have the same problems? Although natural joins even have bigger problems many DBMS do support them. IMO a join based on pk/fk would be a natural join done right. – Tiddo Mar 16 '12 at 8:00
  • 1
    There is no difference as far as most database engines are concerned between a natural join and an explicit " JOIN ... ON ". The engine analyses the query and does the join as best it can based on the various predicates. Using an explicit join does not force the usage of a particular index or access path, its there mostly to support the "LEFT,OUTER,INNER" join syntax which needs to know the explicit join predicates to know when to insert a "missing" row. – James Anderson Mar 16 '12 at 9:40
  • 7
    SQL did not come first! The relational model (which included the concept of foreign keys of course) was first outlined by E.F.Codd in 1969. SEQUEL, as it was then, didn't see the light of day until around 1974. Its inventors made it clear from the outset that SEQUEL/SQL was intended to be based on the pre-existing relational model - although SQL did fall short of being a truly relational language. – nvogel Mar 29 '15 at 13:37
  • @sqlvogel -- true! Should have phrased it "SQL was implemented first". – James Anderson Mar 30 '15 at 2:10
  • C J Date in 'An Introduction to Database Systems' (p276) says Codd invented the concept of the foreign key; doesn't say when but I assume it was before the first SQL implementation. – onedaywhen Aug 22 '16 at 15:23

While you have defined a Foreign Key relationship that does not mean that is how you want to join the tables in all queries. It is the most probable method for joining the tables, but there are cases where is it not correct.

  • You may want to use a Cartesian product of the two tables or part thereof for some purpose.
  • There may be other fields on which you can join for another purpose.
  • If you are joining three or more tables, one of the tables may be related to two or more of the tables. In this case usually only one of the possible FK relationships may be appropriate in the query.
| improve this answer | |

You may be operating on a false assumption. You say 'as far as you can find out' but don't give any empirical or evidentiary proof. If the pk or fk are the best index for a query it will be used. I don't know why you are seeing this, but my guess is poorly formed queries.

Edit now that the question has been totally rewritten : The case you are describing would only be for a very small set of queries. What if there are 12 tables Joined? What if there are no FKs.... Even if there was a default join on I would still always specify the join just for readability. (I don't want to have to look at the data and then try to figure out what is being joined on)

Some Query tools actually do an auto join for you then allow you remove or edit the join. I think MS Access's Query Builder does this.

Lastly the ANSII standard states that the join must be specified. That is reason enough to not allow it.

| improve this answer | |
  • 3
    Sorry, perhaps I wasn't clear enough. I'm not talking about indexes, I'm talking about joins. Suppose I have table1 and table2, with a fk on table1.a which points to table2.b. If I join these tables I'll have to explicitly say that I want to join them on columns a and b (e.g. 'SELECT * FROM table1 JOIN table2 ON table1.a = table2.b'), while I already defined in my database scheme that those two are related. The question is why I can't do 'SELECT * FROM table1 JOIN table2' and let the DBMS automatically choose the join columns based on the fk/pk. – Tiddo Mar 16 '12 at 0:36
  • 4
    Especially the readability made sense to me! However, that the standard says so is not a really good argument IMO. Many standards have made wrong choices before (HTML for example). – Tiddo Mar 16 '12 at 8:03

There are many reasons way the database can’t safely do this, including the fact that add/removing Foreign Keys will change the meaning of pre-written queries including queries in the source code of the application. Most database also don’t have a good set of Foreign Keys that cover all possible joins you are likely to want do. Also for better or for worth, Foreign Keys often get removed to speed up systems and can't be used on tables that are loaded in the "wrong" order from file.

However there is no reason why a query design tool or the text editor can’t auto complete a join with the help of Foreign Keys in the same way as they give you intellisense on column name. You can the edit the query if the tool got it wrong and save a completely defined query. Such a tool could also usefully make use of the convention of naming Foreign Keys columns by the “parent” table name and columns with the same name in both the parent/child table etc.

(My wife still can’t understand the difference between Management Studio and Sql Server and talks about starting sql server when she starts up management studio!)

| improve this answer | |

Natural join "automatically" joins on equality of common columns, but you should only write that if that's what you want based on table meanings and your desied result. There's no "automatically" knowing how two tables "should" be joined or in any other way any table "should" appear in a query. We do not need to know constraints to query. Their presence just means the inputs may be limited and, consequently, the output may be too. You could define some kind of join_on_fk_to_pk operator that "automatically" joins per declared constraints; but if you want the meaning of the query to stay the same if only constraints change but not table meanings then you'd have to change that query to not use the new declared constaints. Just giving the query you want using join & conditions already leaves the meaning the same despite any constraint changes.

What constraints hold (including PKs, FKs, UNIQUE & CHECK) don't affect what tables mean. Of course, if the table meanings change then the contraints might change. But if the constraints change it doesn't mean that queries should change.

One does not need to know constraints to query. Knowing about constraints means we can use further expressions that without the constraint holding wouldn't return the same answer. Eg expecting via UNIQUE that a table has one row, so we can use it as a scalar. These queries can break if the constraint was assumed but not declared. But declaring a constraint that the query didn't assume cannot break it.

Is there any rule of thumb to construct SQL query from a human-readable description?

| improve this answer | |

The reason is that there is the LANGUAGE, and then there is the underlying principals. The language is sparse and lacking in many features that you would expect to see in a general purpose language. This simply happens to be a nice feature that hasn't been added to the language and probably won't be. It's not a dead language so there's some hope, but I wouldn't be optimistic.

As others have pointed out, some implementations use an extension where join (column) joins two tables based on a common column name, which is somewhat similar. But it isn't widely spread. Note that this extension is different from the SELECT * FROM employee NATURAL JOIN department; syntax which does not include a way to specify which columns to use. Neither rely upon a relationship between the tables, which makes them unreliable (the natural join syntax more than the extension).

There's no fundamental obstacle to " inner join table on PKFK" where PKFK is a keyword meaning "the foreign key relationship defined between the two tables", there might be issues with multiple fk to the same table, but that could simply cause an error. The question is do the people designing the language consider that a) a good idea and b) better to work on than some other language change...

| improve this answer | |
  • 3
    This presumes it is a good idea that they should already have done. It is also likely that they have already considered this and decided not to do it. Perhaps it's a very bad idea in practice: Sjoerd mentioned an example, where a query could break just from adding a new column and FK relationship. Lord Tydus also explains foreign keys having a different responsibility from dictating the ways your tables should be joined. – Jonathan Hobbs Mar 16 '12 at 5:10
  • 1
    @JonathanHobbs:I meant my answer to be a generally neutral.But abandoning neutrality.Sjoerd's logic is flawed.Changes to tables already break queries, adding a new column to a tables primary key is going to either break queries or start returning incorrect results. This would in fact insulate you from that to an extent, as long as the table relationship was maintained, column changes could be done safely.This would probably increase usage of FK relationships, as it would be useful for something other than RI.Most joins are either on the PK or include the Pk.To handle multi fk,use column name. – jmoreno Mar 16 '12 at 5:46

If omiting the ON clause is assumed to follow the fields based on the referential integrity, how would you do a Cartesian product?

Edit: using AUTO The advantages to this is a little less typing and you don't have to know how they are joined or remember a complicated join. If the relationhip changes, it is handled automatically, but that rarely happens except in early development.

What you have to do now is decide if all of your AUTO joins hold up during a relationahip change to match the intent of your select statement.

| improve this answer | |
  • 1
    @JeffO: the main advantage is that it expresses the intent more precisely, in a very clear declarative manner. Joins on column names tell you nothing, other than the fact that some of the contents of the columns are similar to those in another (but might not be the same type). A join on a fk ref, tells you that there is a fk ref, no column list would mean there was only 1 fk between the tables, or conversely that there is 1+ (consider a multicolumn key with more than 1 ref what happens when you mix the columns c1=fk1_c1 and c2=fk2_c2).Even with more typing on average, this would be good. – jmoreno Mar 17 '12 at 5:11
  • Using (INNER) JOIN without ON is not standard SQL. Comma, CROSS JOIN & (INNER or any OUTER) JOIN ON 0=0 return Cartesian product. – philipxy May 17 '19 at 17:31

why can't the database figure out that if I join those tables I want to join them on the pk/fk columns?

Parts of the reason are:

1 - in theory you can join tables on arbitrary columns from the two tables. While this is not common practice it is valid. Remember that SQL is like a programming language it does not understand what information is inside the columns of course and names, to SQL, don't mean much in this regards.

2 - There are different types of joins (left, righ, inner) - Inner Joins is only 1 of them.

3 - SQL standard may be guided by the principle of being a lower-level language that allows higher level dialects to form intelligence using it. The comparison is somewhat clearer if you think of a 4th generation language vs. a 3rd generation language. In fact, one tool I have used, IEF, allowed you to write something like this:

ReadEach Customer 
Where Customer Places Orders and That Customer LivesIn "California" 
and OrderValue > 100.00

In summary your suggestion is interesting and could be implemented either as part of the standard or as a stored procedure (it would default to an Inner Join).

| improve this answer | |

Tiddo, I believe you are completely right, SQL on that topic is quite dumb, and I remember having thought the same thing you did about foreign keys while learning SQL some ten years ago.

Ok, given that, I eventually had to pass that exam; and to pass it, I had to let go. SQL is more of a trainwreck than anyone is likely to admit, its standardization path is a complete disaster, and some implementations are menacingly turing complete. Still it's quite handy, in general. (I'm not a K/V luddite)

Foreign keys, then... not that handy at all. They are an important concept in the relational model, ok, but the SQL feature with the same name doesn't compare well.

Tell you straight: do not use that SQL feature called Foreign Key at all, until you hit some big system with performance problems. Explicitly telling the engine which field is a foreign key and which is not is only used for indexing, and it's invisible to the db user.

Is it misleading?

Are they going to make it more powerful now, after 30 years of people being mislead?
Not a chance.

Completely ignoring Foreign Keys until necessary... fixed SQL for me?

And why the heck all of this happened in the first place?
Well, the feature we call foreign keys was added later on, to SQL; SQL is a standard that evolved over time, bottom-up. Vendors implemented ludicrous features, while standard bodies facepalmed.

Foreign keys as said, where only meant for indexing and there was no JOIN construct available. (joins where done with SELECT queries, JOIN queries are quite recent and only meant to alias SELECT functionality) They probably though that calling that indexing flag FOREIGN KEY, was a clever naming-hack over relational db theory concepts.

| improve this answer | |
  • 13
    With regard to foreign keys, I take it you've only ever touched the MyISAM engine on MySQL? 'Cause even disregarding that little rant, every single thing in this answer is wrong. – Izkata Mar 16 '12 at 3:13
  • Fk’s are not used for indexing, in fact a common problem is to not have an index on the fk column which can have a dramatic performance impact. – jmoreno Feb 16 '19 at 13:20

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