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 sqlfiddle.com). NOT IN is definitely working correcly in PostgreSQL — user330315
– user330315, Commented Dec 11, 2012 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
– Wojtas, Commented Dec 11, 2012 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
– sam yi, Commented Dec 11, 2012 at 19:03

Andrew Lazarus · Accepted Answer · 2012-12-13 18:42:39Z

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.

Collectives™ on Stack Overflow

Postgres NOT IN not working

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related