INNER JOIN

How an INNER JOIN combines two tables and returns only the rows that have a match in both.

Journeyman · SQL · MySQL · Updated

By

The INNER JOIN is the most widely used type of join. When we use an INNER JOIN the database will return all rows from both tables IF a related foreign key exists.

person and task tables are linked by person_id

To JOIN two tables, you must specify which field should be used to link the data.

SELECT *
FROM person
INNER JOIN task
    ON person.person_id = task.person_id

query result of an inner join

Notice that Katrina from the person table is not in the result, and neither are mopping and change oil from the task table. No task row links to Katrina, and those two tasks have no person_id.

Do it: list the products from suppliers in Japan

A buyer is planning a trip to Japan and wants a list of the products that come from suppliers there, each beside its supplier's name.

Two tables hold what the list needs:

  • products: each product's product_id, product_name, and supplier_id, a foreign key.
  • suppliers: each supplier's supplier_id, the primary key, its company_name and its country.

They hold these rows (the Tables tab shows the SQL that makes them):

supplier_id company_name country
1 Exotic Liquids UK
4 Tokyo Traders Japan
6 Mayumi's Japan
7 Pavlova, Ltd. Australia
product_id product_name supplier_id
1 Chai 1
2 Chang 1
3 Aniseed Syrup 1
9 Mishi Kobe Niku 4
10 Ikura 4
13 Konbu 6
14 Tofu 6
15 Genen Shouyu 6
16 Pavlova 7
74 Longlife Tofu 4

For example, Ikura's supplier_id is 4. Supplier 4 is Tokyo Traders, in Japan, so Ikura is on the list, beside Tokyo Traders. Chai's supplier is 1, Exotic Liquids, in the UK, so Chai isn't.

Your query should give exactly these rows, in this order:

product_name company_name
Genen Shouyu Mayumi's
Ikura Tokyo Traders
Konbu Mayumi's
Longlife Tofu Tokyo Traders
Mishi Kobe Niku Tokyo Traders
Tofu Mayumi's

Your job: write one SELECT query that joins products to suppliers with INNER JOIN.

  • Show two columns, product_name and company_name, in that order.
  • Match each product's supplier_id to the supplier's supplier_id.
  • Keep only the rows whose supplier's country is 'Japan'.
  • Sort the rows by product_name, A to Z.

Both tables have a supplier_id column, so say which table you mean each time you use it: products.supplier_id or suppliers.supplier_id.

Practiced live on the lesson page: write the SQL right there and check it.

Cheat sheets