PostgreSQL 'NOT IN' and subquery

Question

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.

Is the `consols.mac` column `NULL` or `NOT NULL`? – Mark Byers Dec 11 '11 at 09:40 — Mark Byers, Dec 11 '11 at 09:40

score 222 · Accepted Answer · answered Dec 11 '11 at 09:39

222

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
)

answered Dec 11 '11 at 09:39

Mark Byers

811,555
193
1,581
1,452

4

Note: the `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 – wildplasser Jan 19 '15 at 23:23
9

@wildplasser I dunno about that. It wasn't working for me, until I added the `IS NOT NULL`. The nested `SELECT` was returning a few `NULLS`, and that was tripping up the `IN(SELECT...)`. – robins35 Apr 06 '17 at 00:22
5

I would greatly appreciate an explanation as to why the `IS NOT NULL` causes this to work. – mbarkhau Apr 19 '17 at 14:34
12

It appears that using `NULL` in a `NOT IN` clause doesn't work because a comparison to `NULL` is neither true nor false. http://www.sqlbadpractices.com/using-not-in-operator-with-null-values/ – mbarkhau Apr 19 '17 at 14:53
2

I'll add my voice to the group that notices that without "is not null" the query returns no rows, but when adding "is not null" I get the desired values. Great answer. – Ray Toal Apr 22 '18 at 03:10
5

The query will return no rows in absence of the `is not null` if the subquery produces no matching values and at least one `null` value. From [section 9.22](https://www.postgresql.org/docs/current/static/functions-subquery.html#FUNCTIONS-SUBQUERY-NOTIN) 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." – Christopher Lewis May 31 '18 at 21:08

score 36 · Answer 2 · edited May 23 '19 at 18:56

36

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

edited May 23 '19 at 18:56

Gerbrand

1,561
1
12
20

answered Dec 11 '11 at 11:32

wildplasser

43,142
8
66
109

7

Also note a huge performance loss when using `NOT EXISTS` vs `... NOT IN` – IcanDivideBy0 Mar 31 '16 at 14:35
1

@IcanDivideBy0 In most cases they generate the same query plan. Have you tested it? – wildplasser Mar 31 '16 at 17:56
3

There's also a performance gain in using NOT IN instead of NOT EXISTS, in case of subqueries. See https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN – Gerbrand May 23 '19 at 13:20

score 12 · Answer 3 · answered Dec 11 '11 at 12:27

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.

PostgreSQL 'NOT IN' and subquery

3 Answers3

Linked

Related