MySQL INNER JOIN

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:

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

SELECT city, country

FROM city

INNER JOIN country ON

city.country_id = country.country_id;

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

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

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:

SELECT city, country

FROM city a

INNER JOIN country b ON

a.country_id = b.country_id;

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.