Postgres NOT IN not working

Question

If I use:

SELECT *
  FROM "moves"
 INNER
  JOIN "move_conditions"
    ON "move_conditions"."move_id" = "moves"."id"
 INNER
  JOIN "conditions"
    ON "conditions"."id" = "move_conditions"."condition_id"
 WHERE "conditions"."id" IN (29, 3)

This returns the correct tables where conditions have an id of 29 or 3.

However, if I try:

SELECT *
  FROM "moves"
 INNER
  JOIN "move_conditions"
    ON "move_conditions"."move_id" = "moves"."id"
 INNER
  JOIN "conditions"
    ON "conditions"."id" = "move_conditions"."condition_id"
 WHERE "conditions"."id" NOT IN (29, 3)

The result is incorrect. Conditions with id 29 or 3 are in the result. They should not be. How do I fix this?

Please post sample data (e.g. on http://sqlfiddle.com). `NOT IN` is **definitely** working correcly in PostgreSQL — , Dec 11 '12 at 17:39
Maybe the example provided isn't as acurate as it should be but make sure that there is no `NULL` value in checked conditions: NOT IN (NULL, 29, 3) — Wojtas, Dec 11 '12 at 18:03
Try rewriting to conditions.id <> 29 and conditions.id <> 3.. and see if they are okay. Or also try casting conditions.id::int. — sam yi, Dec 11 '12 at 19:03
The description contradicts the query, which would *never* return rows with "conditions"."id" 29 or 3. Maybe you are confused by a column of the same name from one of the other tables? — Erwin Brandstetter, Dec 11 '12 at 23:57
No, the query is proper. The problem is that it is a HABTM relationship between moves and conditions. So, there are some records that are returning true. I need to say that if a move has ANY conditions that fail, then the move should be excluded. I tried using an ANY statement `WHERE NOT "move_conditions"."condition_id" = ANY(array[29, 3])` but that returns the same result. I am currently subtracting the IN conditions array from the full return - but that requires two queries so it is a bit silly. — user1149547, Dec 12 '12 at 14:49

score 1 · Accepted Answer · answered Dec 13 '12 at 18:42

Do you mean that you want to disqualify a move if any of its conditions is 29 or 3? I'd try a subquery for that.

SELECT *
  FROM "moves"
WHERE moves.id
NOT IN 
    (SELECT /* Don't know if PG infers or would be faster
                 with the DISTINCT that is implied by NOT IN */
      moves.id FROM 
      move_conditions 
      INNER JOIN
      conditions
    ON move_conditions.condition_id=conditions.id
    WHERE conditions.id IN (29,3)
    )

Or you could try

SELECT *
  FROM moves
EXCEPT
SELECT *
  FROM "moves"
INNER
JOIN "move_conditions"
   ON "move_conditions"."move_id" = "moves"."id"
INNER
JOIN "conditions"
ON "conditions"."id" = "move_conditions"."condition_id"
WHERE "conditions"."id" IN (29, 3)

although I'd expect that to be much slower.

You can convert the first version to use NOT EXISTS instead of NOT IN; versions of PG optimize those differently and one may be faster than the other.

Postgres NOT IN not working

1 Answers1