Getting different results from Subquery and JOIN which seem to be the same

I’m doing a challenge problem on DataCamp. It uses two tables, “economies” which contains economic information by country code, and “countries” which contains general country information by country code.

The challenge is: Get country code, inflation rate, and unemployment rate in 2015 from the “economies” table, where the gov_form is not ‘Constitutional Monarchy’ or ‘%Republic%’ in the “countries” table.

With a LEFT JOIN I get 20 results:

SELECT e.code, e.inflation_rate, e.unemployment_rate, c.gov_form
FROM economies AS e
LEFT JOIN countries AS c
   ON c.code = e.code
WHERE c.gov_form <> 'Constitutional Monarchy'
   AND c.gov_form NOT LIKE '%Republic%'
   AND e.year = 2015
ORDER BY e.inflation_rate;

With a subquery I get 26 results:

SELECT code, inflation_rate, unemployment_rate
  FROM economies
  WHERE year = 2015 AND code NOT IN
    (SELECT code
     FROM countries
     WHERE (gov_form = 'Constitutional Monarchy' OR gov_form LIKE '%Republic%'))
ORDER BY inflation_rate;

It seems that the JOIN version is excluding countries that do not exist in the “countries” table (they only exist in the “economies” table), for example ROU(Romania). However, I thought LEFT JOIN with “economies” on the left would include all rows from that table and not drop them regardless of whether they exist in the right table?

Any advice is appreciated.

Answer

In the first query you destroy the effect of the left join by putting conditions on the fields of the joined table. For example, c.gov_form <> 'Constitutional Monarchy' will be false when there is no country record that fulfills the join condition, because then c.gov_form is null.

Solve this by moving those conditions into the join on clause:

SELECT e.code, e.inflation_rate, e.unemployment_rate, c.gov_form
FROM economies AS e
LEFT JOIN countries AS c
   ON c.code = e.code
   AND c.gov_form <> 'Constitutional Monarchy'
   AND c.gov_form NOT LIKE '%Republic%'
WHERE e.year = 2015
ORDER BY e.inflation_rate;