Difference between joins and foreign key in DBMS
Tags: webdev, sql, mysql, database
Published: 2022-09-15 10:44:22

**Overview**: In this post, I explore joins and foreign keys by what they do and how they work independently. I also show how these two work hand in hand to ensure data integrity when using joins.

We'll refer to our sample database tables: `customers` and `orders` for demonstration.

**Customers table**

| ID  |  Name   |     City     | Age |
| :-- | :-----: | :----------: | --: |
| 1   |  John   |   New York   |  35 |
| 2   |  Agnes  | Los Angeles  |  23 |
| 3   | Raphael |   Houston    |  41 |
| 4   | Gustavo |   Nairobi    |  50 |
| 5   | Brenda  | Philadelphia |  26 |
| 6   | Whitney |   Chicago    |  18 |

**Orders table**

| ID  |     Name     | CustomerID | Amount |
| :-- | :----------: | :--------: | -----: |
| 1   |     Box      |     5      |   3500 |
| 2   |   Iron Box   |     1      |   2300 |
| 3   |  Television  |     4      |   1200 |
| 4   | Play Station |     3      |   6100 |
| 5   |   Computer   |     6      |   1800 |
| 6   |     Oven     |     2      |   2000 |

According to our sample tables shown above, rather than storing the customer details in both tables, we store `CustomerID` on our **Orders Table** that shall contain values existing in `ID` column of **Customers Table**. This technique is known as **normalization**. It helps reduce duplication of data.

# What is a `JOIN`?

SQL has the ability to combine data from two or more tables based on a related column between them.
We can achieve this using a `JOIN`.<br>
A `JOIN` is used to combine rows from two or more tables, based on a related column between them.
It is used when retrieving data from the DB.

## Using `JOIN`

We shall join our sample tables to be able to get details of **order** made by a **customer**. The two tables are related via the `CustomerID` column, which holds values that are present in `ID` column of **Customers Table**.

```sql
SELECT customers.ID, customers.Name AS customerName, orders.Name AS productName, orders.Amount FROM customers JOIN orders ON customers.ID=orders.CustomerID;
```

When we query our database, we get the following results:

| ID  | customerName | productName  | Amount |
| :-- | :----------: | :----------: | -----: |
| 5   |    Brenda    |     Box      |   3500 |
| 1   |     John     |   Iron Box   |   2300 |
| 4   |   Gustavo    |  Television  |   1200 |
| 3   |   Raphael    | Play Station |   6100 |
| 6   |   Whitney    |   Computer   |   1800 |
| 2   |    Agnes     |     Oven     |   2000 |

The results we have is data _joined_ from two tables.

However, We have a lurking problem.<br>
When we add new records to our `orders` table, we can add new values for the `CustomerID` column that do not exist in `ID` column of the `customers` table.
We do not have any checking to ensure that `orders` table only accepts new values for the `CustomerID` column when the provided value actually exists in ID` column` of `customers` table.<br>
_We do not want to have order entries created in the `orders` table for customers that **do not exist**._

We can enforce referential integrity for the `CustomerID` column in the involved tables using Foreign Key constraint.

# What is a `FOREIGN KEY`?

_Foreign keys_ are the columns of a table that point to columns of another table that **must** be a **primary key** or have a **unique constraint**. <br>
The foreign key places constraints on data in the related tables, which allows MySQL to maintain [referential integrity](http://en.wikipedia.org/wiki/Referential_integrity).<br>
In our case, we need to make `CustomerID` column of the `orders` table to be a foreign key that references to the `ID` column of the `customers` table.

<!-- In our case, we need to add a foreign key constraint to our `CustomerID` column of the `orders` table. -->

## Using `FOREIGN KEY`

We could add foreign key constraint to our `orders` table during creation, like:

```sql
CREATE TABLE orders (
    ID INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20),
    customerID INT,
    amount INT,
    CONSTRAINT fk_ref_customers FOREIGN KEY (customerID) REFERENCES customers(ID)
) ENGINE=INNODB;
```

We already have our `orders` sample table. Therefore we remain with an option of adding Foreign Key to our existing table. We will use the `ALTER TABLE` syntax.

```sql
ALTER TABLE orders
    ADD CONSTRAINT fk_ref_customers
    FOREIGN KEY(customerID) REFERENCES customers(ID);
```

Just like that...And we have enforced referential integrity between our related tables. The `CustomerID` column of the `orders` table is now a **Foreign Key** that references to the `ID` column of the `customers` table.

A new value in the `CustomerID` column of the `orders` table **must** therefore be existing in the `ID` column of the `customers` table. `customers` table can continue having new records but the `orders` table is constrained to having/adding data that exists in `customers` table for the `CustomerID` column.

# So, What are the notable differences?

- A **Foreign keys** enforce data integrity, making sure the data confirms to some rules when it is added to the DB.
- A `JOIN` is used when you retrieve/query data from the DB by giving rules on how to select the data.
- `JOIN`s work if there are _Foreign Keys_ or not.
- _Foreign Keys_ work if you extract data with or without `JOIN`s.

# Conclusion

`JOIN`s and **Foreign keys** do not do the same thing.

`JOIN`s are a way to retrieve data from multiple tables and represent them as a single table.

**Foreign key** is a constraint to enforce referential integrity. You will still need to use `JOIN`s to build your queries.

_Thanks for reading I hope you found this article useful. Let's catch up on [twitter](https://twitter.com/smitterhane)._