PostgreSQL 'NOT IN' 和子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8463043/
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 'NOT IN' and subquery
提问by skowron-line
I'm trying to execute this query:
我正在尝试执行此查询：
SELECT mac, creation_date
FROM logs
WHERE logs_type_id=11
AND mac NOT IN (select consols.mac from consols)
But I get no results. I tested it, and I know that there is something wrong with the syntax. In MySQL such a query works perfectly. I've added a row to be sure that there is one macwhich does not exist in the consolstable, but still it isn't giving any results.
但我没有得到任何结果。我测试了一下，我知道语法有问题。在 MySQL 中，这样的查询工作得很好。我添加了一行以确保表mac中不存在一行consols，但它仍然没有给出任何结果。
回答by Mark Byers
When using NOT IN you should ensure that none of the values are NULL:
使用 NOT IN 时，应确保所有值都不为 NULL：
SELECT mac, creation_date
FROM logs
WHERE logs_type_id=11
AND mac NOT IN (
SELECT mac
FROM consols
WHERE mac IS NOT NULL -- add this
)
回答by wildplasser
When using NOT IN, you should also consider NOT EXISTS, which handles the null cases silently. See also PostgreSQL Wiki
使用 NOT IN 时，您还应该考虑 NOT EXISTS，它以静默方式处理空情况。另见PostgreSQL 维基
SELECT mac, creation_date
FROM logs lo
WHERE logs_type_id=11
AND NOT EXISTS (
SELECT *
FROM consols nx
WHERE nx.mac = lo.mac
);
回答by Frank Heikens
You could also use a LEFT JOIN and IS NULL condition:
您还可以使用 LEFT JOIN 和 IS NULL 条件：
SELECT
mac,
creation_date
FROM
logs
LEFT JOIN consols ON logs.mac = consols.mac
WHERE
logs_type_id=11
AND
consols.mac IS NULL;
An index on the "mac" columns might improve performance.
“mac”列上的索引可能会提高性能。
