MySQL MySQL内部连接查询以获取其他表中不存在的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9954749/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Inner Join Query To Get Records Not Present in Other Table
提问by skos
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 表为空时，它不显示任何记录。如何达到这个结果？
回答by Joachim Isaksson
回答by IT ppl
Try something like the following:
尝试类似以下内容：
SELECT * FROM all_countries
WHERE country NOT IN (SELECT country FROM supported_countries)
回答by Nilesh
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)
回答by Luis H Cabrejo
While @Joachim Isaksson gave me the clue, I tested this very similar query and worked on my database by replacing variables.
虽然@Joachim Isaksson 给了我线索，但我测试了这个非常相似的查询并通过替换变量来处理我的数据库。
SELECT * FROM all_countries
LEFT JOIN supported_countries ON all_countries.id = supported_countries.id
WHERE supported_countries.id IS NULL
I gave the question and that Joachim's answer my thumbs up. Cheers !
我提出了这个问题，Joachim 的回答让我竖起大拇指。干杯！
