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 mac which does not exist in the consols table, but still it isn't giving any results.
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
)
WHERE mac IS NOT NULL clause in the subquery is not needed, since In(...) always removes NULLs (and duplicates). Because a set cannot contain NULLs
IS NOT NULL. The nested SELECT was returning a few NULLS, and that was tripping up the IN(SELECT...).
IS NOT NULL causes this to work.NULL in a NOT IN clause doesn't work because a comparison to NULL is neither true nor false. sqlbadpractices.com/using-not-in-operator-with-null-values
is not null if the subquery produces no matching values and at least one null value. From section 9.22 of the current (version 10) PostgreSQL manual: "[…] if there are no equal right-hand values and at least one right-hand row yields null, the result of the NOT IN construct will be null, not true."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
);
NOT EXISTS vs ... NOT IN
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.
consols.maccolumnNULLorNOT NULL?