**SQL commands ‘with’ ‘join’ and ‘where’ are in a conflict when they be used together, Why? **
WITH OceaniaCountries AS (
WHERE continent = ‘Oceania’)
ON population_years.country_id = OceaniaCountries.id;
WHERE population_years.year = 2005
It running well with or without the ‘where’ clause, accordingly ‘where’ clause doesn’t work.
WHY? I want to increase efficiency by using the ‘with’ clause.
THE GIVEN SOLUTION：
INNER JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2005
AND continent = ‘Oceania’;