Combining tables with INNER JOIN

Reminder: data spread across tables

In the previous module you saw the relational design: information is spread across several tables and each product references its category through a foreign key (category_id points to categories.id) instead of repeating the name. Here we start from that finished design and focus on the question that remains: how do we join those tables back together to query them?

Joining back: INNER JOIN

To see the category name next to the product name you have to combine both tables. That is a JOIN: you pair rows from one table with rows from another according to a condition.

SELECT products.name, categories.name
FROM products
INNER JOIN categories ON products.category_id = categories.id;

• FROM products: the starting table.
• INNER JOIN categories: the table you want to combine.
• ON ...: the matching condition, usually foreign_key = primary_key.

The INNER JOIN only keeps the rows that have a match on both sides. A product with a category_id that does not exist in categories (or is NULL) disappears from the result. We will look at this in detail in the next lesson.

Since two tables can have columns with the same name (here, name in both), it is a good idea to qualify them with the table name: products.name, categories.name.

Examples

INNER JOIN: product and its category name

-- Only products that have an assigned category appear.
SELECT products.name, categories.name AS category
FROM products
INNER JOIN categories ON products.category_id = categories.id;