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.
This question is related to
postgresql
subquery
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.
Source: Stackoverflow.com