INNER JOIN
How an INNER JOIN combines two tables and returns only the rows that have a match in both.
Journeyman · SQL · MySQL · Updated
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.

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

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'sproduct_id,product_name, andsupplier_id, a foreign key.suppliers: each supplier'ssupplier_id, the primary key, itscompany_nameand itscountry.
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_nameandcompany_name, in that order. - Match each product's
supplier_idto the supplier'ssupplier_id. - Keep only the rows whose supplier's
countryis'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.