CODEWITHSUNDEEP
postgresqlsubquery
kskaradzinski
kskaradzinski

Reputation: 5084

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.

Upvotes: 121

Views: 204002

Answers (3)

wildplasser
wildplasser

Reputation: 44250

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
  );

Upvotes: 37

Frank Heikens
Frank Heikens

Reputation: 127086

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.

Upvotes: 13

Mark Byers
Mark Byers

Reputation: 838216

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
)

Upvotes: 239

Related Questions