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").

Nick · Accepted Answer · 2018-10-07 23:16:33Z

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

edited Oct 7, 2018 at 23:16

answered Oct 7, 2018 at 23:10

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Strawberry Over a year ago

I would stick with the second approach

Nick Over a year ago

@Strawberry agreed. I've noted that in the answer

Black Hole · Accepted Answer · 2018-10-07 22:55:35Z

0

Maybe it would be like

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

answered Oct 7, 2018 at 22:55

Black Hole

1,41416 silver badges17 bronze badges

2 Comments

Black Hole Over a year ago

Sorry im not edit the selects, but in that select in theory, it can select the country values with the countryLanguajes values when two are the same.

Sandeep Over a year ago

Actually your solution did work once I removed the additional comma after "Percentage" (i.e. "Percentage," to be replaced by "Percentage"). Once you make the change, I will accept your answer.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related