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屋！