Why are foreign keys more used in theory than in practice?

The reason foreign key constraints exist is to guarantee that the referenced rows exist.

"The foreign key identifies a column or a set of columns in one table that refers to a column or set of columns in another table. The values in one row of the referencing columns must occur in a single row in the referenced table.

Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL). This way references can be made to link information together and it is an essential part of database normalization." (Wikipedia)

RE: Your question: "I can't imagine the need to join tables by fields that aren't FKs":

When defining a Foreign Key constraint, the column(s) in the referencing table must be the primary key of the referenced table, or at least a candidate key.

When doing joins, there is no need to join with primary keys or candidate keys.

The following is an example that could make sense:

CREATE TABLE clients (    client_id       uniqueidentifier  NOT NULL,    client_name     nvarchar(250)     NOT NULL,    client_country  char(2)           NOT NULL);CREATE TABLE suppliers (    supplier_id       uniqueidentifier  NOT NULL,    supplier_name     nvarchar(250)     NOT NULL,    supplier_country  char(2)           NOT NULL);

And then query as follows:

SELECT     client_name, supplier_name, client_country FROM     clients INNER JOIN    suppliers ON (clients.client_country = suppliers.supplier_country)ORDER BY    client_country;

Another case where these joins make sense is in databases that offer geospatial features, like SQL Server 2008 or Postgres with PostGIS. You will be able to do queries like these:

SELECT    state, electorate FROM     electorates INNER JOIN     postcodes on (postcodes.Location.STIntersects(electorates.Location) = 1);

Source: ConceptDev - SQL Server 2008 Geography: STIntersects, STArea

You can see another similar geospatial example in the accepted answer to the post "Sql 2008 query problem - which LatLong’s exists in a geography polygon?":

SELECT     G.Name, COUNT(CL.Id)FROM    GeoShapes GINNER JOIN     CrimeLocations CL ON G.ShapeFile.STIntersects(CL.LatLong) = 1GROUP BY     G.Name;

These are all valid SQL joins that have nothing to do with foreign keys and candidate keys, and can still be useful in practice.

Foreign keys have less to do with joins than with keeping database integrity. Proof of that is that you can join tables in any way you want, even in ways that don't necessarily make sense.

I can't imagine the need to join tables by fields that aren't FKs. Can someone give an example that makes sense?

FOREIGN KEYs can only be used to enforce referential integrity if the relationship between the entities of the ER model is reflected with an equijoin between two relations in the relational model.

This is not always true.

Here's an example from the article in my blog I wrote some time ago:

• What is entity-relationship model?

This model describes goods and price ranges:

And here's the relational implementation of the model:

CREATE TABLE Goods (ID, Name, Price)CREATE TABLE PriceRange (Price, Bonus)

As you can see, the PriceRange table has only one price-related attribute, Price, but the model has two attributes: StartPrice and EndPrice.

This is because relational model allows transforming the sets and the entity PriceRange can be easily reconstructed using SQL operations.

GoodsID  Name               Price1   Wormy apple        0.092   Bangkok durian     9.993   Densuke watermelon 999.994   White truffle      99999.99PriceRangePrice   Bonus0.01       1%1.00       3%100.00    10%10000.00  30%

We store only the lower bound of each range. Upper bound can easily be inferred.

Here's the query to find the bonus for each good:

SELECT  *FROM    GoodsJOIN    PriceRangeON      PriceRange.Price =        (        SELECT  MAX(Price)        FROM    PriceRange        WHERE   PriceRange.Price <= Goods.Price        )

We see that these relational model implements the ER model fairly well, but no foreign key can be declared between these relations, since the operation used to bind them is not an equijoin.