MySQL INNER JOIN
INNER JOIN is used to return data from multiple tables. More specifically, the
INNER JOIN is for when you're only interested in returning the records where there is at least one row in both tables that match the join condition.
Consider the following tables:
If we want to select data from the two highlighted fields (
city), we could run the following query (which includes an inner join):
And the result would look like this:
In the above example, we use an inner join to display a list of cities alongside the country that it belongs to. The city info is in a different table to the country info. Therefore, we join the two tables using the
country_id field — as that is a common field in both tables (it's a foreign key field).
Here's a diagram of those two tables (with the foreign key relationship highlighted):
Qualifying the Fields
You'll notice that in the above example, we've qualified some field names with their respective table names. Specifically, the last line reads
city.country_id = country.country_id;.
We do this because the field names are the same (
country_id). If we didn't qualify it with the table names (i.e.
country_id = country_id;) MySQL wouldn't know which column we were referring to — the one in the
city table or the one in the
If we didn't qualify the columns, MySQL would throw an ambiguous column error. It would say something like this:
When qualifying your column names, you can use table aliases to shorten your query and save typing.
For example, the above example could be rewritten to this:
Inner Joins with
GROUP BY and Aggregate Functions
In the following example, we switch it around and provide a list of countries in one column, with the number of cities that each country contains in another column.
LEFT JOIN and
The difference between the join types is easy.
- Only returns rows where there's a matching row in both tables.
- All rows from the left table will be returned, even if there's no matching row in the right table.
- All rows from the right table will be returned, even if there's no matching row in the left table.
The examples on this page use the Sakila sample database.