PostgreSQL'NOT IN'和子查询 [英] PostgreSQL 'NOT IN' and subquery

问题描述

我正在尝试执行此查询：

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)

但是我没有结果。我测试了一下，而且我知道语法有问题。在MySQL中，这样的查询可以完美地工作。我添加了一行以确保在 consols 表中不存在一个 mac ，但是

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 mac which does not exist in the consols table, but still it isn't giving any results.

推荐答案

使用NOT IN时，应确保所有值都不为NULL：

When using NOT IN you should ensure that none of the values are 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
)

这篇关于PostgreSQL'NOT IN'和子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！