mySQL Self Join - inner join or join

Question

quick clarification. When joining contents from the same table, must I always declare join type as INNER?

For example,

SELECT p1.country_code,
       p1.size AS size2010,
       p2.size AS size2015
FROM populations AS p1
  INNER JOIN populations AS p2
    ON  p1.country_code = p2.country_code;

(on the table shown in this screenshot)

When I tried to remove the keyword INNER, it was wrong. Is it because JOIN does not exist in SQL?

Thanks and cheers.

Answer 1

You do want a self (inner) join here, but you are missing logic which limits each table to a certain year:

SELECT
    p1.country_code,
    p1.size AS size2010,
    p2.size AS size2015
FROM populations AS p1
INNER JOIN populations AS p2
    ON p1.country_code = p2.country_code
WHERE
    p1.year = 2010 AND
    p2.year = 2015;