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):
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:
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.
Result:
Difference Between INNER JOIN, LEFT JOIN and RIGHT JOIN
The difference between the join types is easy.
INNER JOIN- Only returns rows where there's a matching row in both tables.
LEFT JOIN- All rows from the left table will be returned, even if there's no matching row in the right table.
RIGHT JOIN- 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.