posted 6 years ago
There are some good answers here, so most of my answer will simply repeat them. Nonetheless, i do not think it is stated clearly.
Foreign Keys and joins having absolutely nothing to do with each other. Zero, nothing. Foreign Keys are technically not even SQL (concepts), they are part of the database concept of Relational Integrity, often referred to as RI. Let me explain.
When creating a table, you often want to constrain the data allowed in a column. This comes in three main flavors: Data Type, Validation, and Existence. The Data type must be defined directly after the column name (unless it is implicitly defined by a Foreign Key), Validations are optional Check and Unique Constraints (and Indexes), and Existence comes (when direct) via Foreign Keys. There are variations, but this is the main form of checking the data is what you expect and allow it to be.
A Foreign Key works by making sure the datum (when not null) exists in another table. For example:
A join is a SQL concept to describe the relationship between multiple tables in the FROM clause of a SQL statement. ANSI SQL also uses it as a SQL keyword. For example:
That is a basic "Cartesian" join. No Foreign Keys required.
--
As it so happen, there is a high correlation between tables that have foreign keys and table you will want to join, so much so, that tools that help you write queries will offer to join tables for you based on those joins, and that will often be exactly what you want. Regardless, please make sure you understand the difference between the two concepts, and repeat the mantra: RI and joins are not the same thing!
--
OK, once we're here, it's time for a SQL joke: A query walked into a bar and walking over to two tables asked, "May i join you?"