PostgreSQL 'NOT IN' and subquery

PostgresqlSubquery

Postgresql Problem Overview

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.

Postgresql Solutions

Solution 1 - Postgresql

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
)

Solution 2 - Postgresql

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

Solution 3 - Postgresql

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.

Categories
Recommended Postgresql Solutions
Recommended Subquery Solutions

Previous Solution:

What is a subnormal floating point number?

C++C++11Floating PointIeee 754

Next Solution:

using Jasmines spyon upon a private method

JavascriptUnit TestingJasmine

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionkskaradzinskiView Question on Stackoverflow
Solution 1 - PostgresqlMark ByersView Answer on Stackoverflow
Solution 2 - PostgresqlwildplasserView Answer on Stackoverflow
Solution 3 - PostgresqlFrank HeikensView Answer on Stackoverflow