PostgreSQL‘ NOTIN’和子查询

我尝试执行这个查询:

SELECT mac, creation_date
FROM logs
WHERE logs_type_id=11
AND mac NOT IN (select consols.mac from consols)

但是没有结果。我测试过了，我知道语法有问题。在 MySQL 中，这样的查询可以很好地工作。我添加了一行，以确保有一个 mac在 consols表中不存在，但它仍然没有给出任何结果。

171154 次浏览

小开

最佳答案

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
)

小开

When using NOT IN, you should also consider NOT EXISTS, which handles the null cases silently. See also PostgreSQL Wiki

SELECT mac, creation_date
FROM logs lo
WHERE logs_type_id=11
AND NOT EXISTS (
SELECT *
FROM consols nx
WHERE nx.mac = lo.mac
);

小开

You could also use a LEFT JOIN and IS NULL condition:

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.