The INNER JOIN Keywords

The form of inner join syntax just discussed uses the comma operator in the FROM clause to name the
joined tables. Another inner join syntax uses the INNER JOIN keywords. With this syntax, those
keywords replace the comma operator between table names in the FROM clause. Also, with INNER JOIN,the conditions that indicate how to perform record matching for the tables move from the WHERE clause to become part of the FROM clause.
There are two syntaxes for specifying matching conditions with INNER JOIN queries:

• Add ON and an expression that states the required relationship between tables. Suppose that a join
performs a country code match between the CountryLanguage and Country tables. The join
would be written as follows:

SELECT Country.Name, CountryLanguage.Language FROM CountryLanguage INNER JOIN Country
ON CountryLanguage.CountryCode = Country.Code;

• If the name of the joined column is the same in both tables, add USING() rather than ON after the
table names, and list the name within the parentheses. For example, if the country code column
happened to be named CountryCode in both tables, the query could be written like this:

SELECT Country.Name FROM City INNER JOIN CountryLanguage USING (CountryLanguage.CountryCode)
WHERE CountryLanguage.Language='Turkish';

If tables using more than one pair of like-named columns are being joined, list the column names
within the parentheses of the USING() clause separated by commas.

The ON Clause

Similar to WHERE, the ON clause in a join specifies how to match records in the joined tables, and
eliminates non-corresponding combinations of rows from the output. The ON clause also can include
additional conditions to further restrict the output and answer more specific questions. Here are some
examples:

• In which countries is the Swedish language spoken? To answer this, include a condition that
identifies the language desired:

mysql> SELECT Country.Name, CountryLanguage.Language
-> FROM CountryLanguage INNER JOIN Country
-> ON CountryLanguage.CountryCode = Country.Code
-> AND Language = 'Swedish';


+---------+----------+
| Name | Language |
+---------+----------+
| Norway | Swedish |
| Sweden | Swedish |
| Finland | Swedish |
| Denmark | Swedish |
+---------+----------+