MySQL内部连接查询以获取其他表中不存在的记录

I have table 1, all_countries, as follows-

我有表1，all_countries，如下 -

id   |  country
------------------
1    |  USA
2    |  China
3    |  India
4    |  France
5    |  UK
6    |  Australia

and I also have table 2, supported_countries, as -

我也有表2，supported_countries， -

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

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?

它工作正常，除非supported_countries表为空，它不显示任何记录。如何实现这个结果？

3 个解决方案

#1

A LEFT JOIN will do that elegantly;

LEFT JOIN将优雅地做到这一点;

SELECT a.* 
FROM all_countries a
LEFT JOIN supported_countries s
  ON a.country = s.country
WHERE s.id IS NULL;

Demo here.

在这里演示。

#2

Try something like the following:

尝试以下内容：

SELECT * FROM all_countries 
  WHERE country NOT IN (SELECT country FROM supported_countries)

#3

SELECT ac.country FROM all_countries ac LEFT JOIN supported_countries sc ON sc.country_name = ac.country_name WHERE ISNULL(sc.country_name)

SELECT ac.country FROM all_countries ac LEFT JOIN supported_countries sc ON sc.country_name = ac.country_name WHERE ISNULL（sc.country_name）

#1