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) 

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.

3 Answers

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.