MySQL --- Explicit INNER JOIN with selection from multiple tables

Question

Whenever I have a need to do an inner join where I have to select columns from multiple tables, I have got the syntax working correctly with implicit joins, without any difficulty. However, I have struggled to get it working with explicit inner joins.

Let me illustrate with an example from the MySQL World Database

My illustrative query, using implicit inner join, is as follows:

SELECT Name, Language, Percentage
FROM Country, CountryLanguage WHERE Code = CountryCode ;

This works as expected. I can have the columns in any order, from either table, without any issues.

I would like to have the explicit INNER JOIN version of the above query (using "INNER JOIN" and "ON").

Answer 1

You can simply replace the , in your implicit join with the word JOIN:

SELECT Name, Language, Percentage
FROM Country
JOIN CountryLanguage 
WHERE Code = CountryCode

and the query will work fine. You can also replace WHERE with ON and again it will work fine. Finally if you want to explicitly name the tables where the columns come from (and this is the preferred approach), you would use:

SELECT c.Name, cl.Language, cl.Percentage
FROM Country c
JOIN CountryLanguage cl
ON c.Code = cl.CountryCode