mysql查询列表中不存在的,MySQL内部联接查询以获取其他表中不存在的记录

I have table 1, all_countries, as follows-

id | country

------------------

1 | USA

2 | China

3 | India

4 | France

5 | UK

6 | Australia

and I also have table 2, supported_countries, as -

id | country

------------------

1 | USA

2 | China

Now I need a query that would give me result that includes all countries that ARE NOT supported

So as per above example I should get

India

France

UK

Australia

I am using the following query -

SELECT ac.country FROM all_countries ac INNER JOIN supported_countries sc ON sc.country_name != ac.country_name

It works fine, except when supported_countries table is empty, it doesn't show any records. How to achieve this result?

解决方案

A LEFT JOIN will do that elegantly;

SELECT a.*

FROM all_countries a

LEFT JOIN supported_countries s

ON a.country = s.country

WHERE s.id IS NULL;

Demo here.