The 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:

Diagram of two tables: City and Country

If we want to select data from the two highlighted fields (country and city), we could run the following query (which includes an inner join):

And the result would look like this:

Screenshot of using an inner join

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):

Diagram of the two tables

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 country table.

If we didn't qualify the columns, MySQL would throw an ambiguous column error. It would say something like this:

Screenshot of MySQL error message

Using Aliases

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.

To do this we use the COUNT() aggregate function to count the number of cities for each country, then the GROUP BY clause to group the results by country.


Screenshot of inner join with COUNT() and GROUP BY


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.