PostgreSQL：在哪里和不在那里
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12891971/
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
PostgreSQL: WHERE IN and NOT WHERE IN
提问by devdRew
I have two tables Aand B, Areferred to Bby field A.id_b B.id, so that any id_bvalue present in B.id.
我有两个表A和B，由字段A引用，因此任何值都存在于.BA.id_b B.idid_bB.id
I have three queries: First one:
我有三个疑问：第一个：
SELECT COUNT(b.id)
FROM B b
WHERE b.id NOT IN (
SELECT a.id_b
FROM A a)
It gives me 0;
它给了我0；
Second one, difference only in NOT:
第二个，区别仅在于NOT：
SELECT COUNT(b.id)
FROM B b
WHERE b.id IN (
SELECT a.id_b
FROM A a)
This query gives me: 1899
这个查询给了我： 1899
Third one:
第三个：
SELECT COUNT(b.id)
FROM B b
And this query gives me 3599
这个查询给了我 3599
SAME result in:
相同的结果：
SELECT a.id_b
FROM A a
guaranty me spliting B.idon two sets, and count of elements in both sets must much total elements count, but I have: 1899 + 0 != 3599.
保证我B.id分成两组，并且两组中的元素数必须很多总元素数，但我有：1899 + 0 != 3599。
How that could be?
那怎么可能？
采纳答案by devdRew
Found the reason. There was records in A, which has NULLvalues in A.id_b. That's why query:
找到原因了。A 中有记录，其NULL值在A.id_b. 这就是为什么查询：
SELECT COUNT(b.id)
FROM B b
WHERE b.id NOT IN (
SELECT a.id_b
FROM A a)
was returning 0.
正在返回0。
回答by mvp
This query tells us that table B has total of 3599 rows:
这个查询告诉我们表 B 总共有 3599 行：
SELECT COUNT(b.id)
FROM B b
Next query tells us that every single one id from B was used in A:
下一个查询告诉我们 B 中的每一个 id 都在 A 中使用过：
SELECT COUNT(b.id)
FROM B b
WHERE b.id NOT IN (
SELECT a.id_b
FROM A a)
Further, this query tells is that table B has 1899 id's that are mentioned in table A:
此外，此查询表明表 B 具有表 A 中提到的 1899 个 ID：
SELECT COUNT(b.id)
FROM B b
WHERE b.id IN (
SELECT a.id_b
FROM A a)
The only possible explanation to this is that some B.id were used in table A as A.id_b more than once per row. Running this query will show list of all duplicate mentions of id_b in table A:
对此唯一可能的解释是表 A 中的某些 B.id 被用作 A.id_b 每行不止一次。运行此查询将显示表 A 中所有重复提及 id_b 的列表：
SELECT a.id_b
FROM A a
GROUP BY a.id_b
HAVING count(a.id_b) > 1
