NOT IN in postgresql not working

Question

I am not getting the output as expected, because

 AND ta.task_status_type_id NOT IN ( 10 )

is not working in below query.

SELECT
    ta.task_id AS id,
    u.employee_id AS employee_id,
    ta.task_status_type_id
FROM
    task_assignments AS ta,
    users AS u
WHERE
    u.id = ta.user_id
    AND ta.id IN (
                   SELECT
                       max ( ta.id ) OVER ( partition BY ta.task_id ) AS id
                   FROM
                       task_details AS td,
                       task_assignments AS ta
                   WHERE
                       td.task_id = ta.task_id
                       AND td.developer_employee_id IS NULL
                       AND ta.task_type_id IN(6,7)
                       AND ta.task_status_type_id NOT IN ( 10 )
                       AND ta.task_status_type_id IN ( 9 )


    );

Please help in resolving the error.

why wouldn't you just say `AND ta.task_status_type_id = 9` ? And what do you mean by 'not working' ? — StevieG, Oct 22 '13 at 12:30
What is the data type of `task_assignments.task_status_type_id`? Can it be `NULL`? — PM 77-1, Oct 22 '13 at 12:34
Could it be simplified query? and by 10 you mean result of some subquery? I'm 100% sure that not in works in PostgreSQL. http://sqlfiddle.com/#!12/8c9b6/1 — Roman Pekar, Oct 22 '13 at 12:54
As *always*: Please provide Postgres version, table definitions, expected outcome, error message. — Erwin Brandstetter, Oct 22 '13 at 20:59

Erwin Brandstetter · Accepted Answer · 2021-03-15T04:26:47.737

An educated guess (for lack of more information):

NOT IN (...) returns NULL if any NULL values are involved and the tested value is not in the list. But only TRUE qualifies in a WHERE clause.

a NOT IN (b,c)

is transformed to:

a <> ALL ('{b,c}'::sometype[])

equivalent to:

(a <> b AND a <> c )

If any of these values (on either side of the operator) is NULL, you get:

(NULL AND FALSE)

That's:

NULL

And NULL is equivalent to FALSE in a WHERE clause. Only TRUE qualifies.

This has been known to cause disbelieve in users unfamiliar with tri-valued logic.

Use IS DISTINCT FROM or NOT EXISTS instead. Or LEFT JOIN / IS NULL.

Example (more guesswork)

In this particular case, you don't need the incriminated expression at all

SELECT ta.task_id AS id
     , u.employee_id
     , ta.task_status_type_id
FROM   task_assignments ta
JOIN   users            u  ON u.id = ta.user_id
WHERE  ta.id IN (
   SELECT max(ta.id) AS id
   FROM   task_details     td
   JOIN   task_assignments ta USING (task_id)
   WHERE  td.developer_employee_id IS NULL
   AND    ta.task_type_id IN (6,7)
-- AND    ta.task_status_type_id IS DISTINCT FROM 10 -- just cruft
   AND    ta.task_status_type_id = 9                 -- this expression covers it
   GROUP  BY ta.task_id
   )

If you are secretly using a list of values to be excluded that could share elements with the inclusion list:

... 
    AND    (ta.task_status_type_id IN ( ... )) IS NOT TRUE
...

Or you weed out NULL values.
Or you avoid common elements in inclusion and exclusion list.

NOT IN in postgresql not working

1 Answers1

Example (more guesswork)

Linked