INNER JOIN

Now that we understand Primary and Foreign keys, it’s time to actually merge our data together using a JOIN. The most common type of join is the INNER JOIN.

How INNER JOIN Works

An INNER JOIN compares two tables and only returns rows where there is a match in both tables.

Imagine a Venn diagram with two circles representing our users and orders tables. The INNER JOIN is the overlapping section in the middle. If a user has never placed an order, they won’t show up. If an order somehow doesn’t have a user attached, it won’t show up.

The Syntax

To perform an INNER JOIN, you need to specify three things:

The table you are starting with (FROM).
The table you are joining (INNER JOIN).
The exact condition that links them together (ON).

SELECT users.name, orders.total_amount
FROM users
INNER JOIN orders 
  ON users.id = orders.user_id;

Let’s break down that ON clause: ON users.id = orders.user_id. This is telling the database: “Take the Primary Key id from the users table, and match it against the Foreign Key user_id in the orders table.”

Resolving Ambiguity

Notice in the SELECT statement above, we wrote users.name and orders.total_amount instead of just name and total_amount.

When you join tables, it’s very common for both tables to have columns with the exact same name (like id or created_at). If you just SELECT id, the database will throw an “ambiguous column” error because it doesn’t know which id you want.

To fix this, you prefix the column name with the table name and a dot: table_name.column_name.

[!TIP] You can also use table aliases to make your joins less typing! SELECT u.name, o.total_amount FROM users u INNER JOIN orders o ON u.id = o.user_id;

In the lab, practice writing your first INNER JOIN to connect our users to their purchases!