POSTGRES-misunderstand with NOT IN

Question

On my postgres database:

When i do

SELECT* FROM ETIQUETTES WHERE DO_PIECE='BL7591'

I have

...|do_piece|ar_ref    |...
---------------------------
...|BL7591  |40L.PA.NAT|...
---------------------------
...|BL7591  |37LL.POM. |...

When i do

SELECT* FROM F_DOCLIGNE WHERE DO_PIECE='BL7591'

I have

...|do_piece|ar_ref    |...
---------------------------
...|BL7591  |46L-E.PA.N|...
---------------------------
...|BL7591  |37LL.POM. |...

then when i do my request with IN

SELECT* FROM ETIQUETTES AS a WHERE DO_PIECE='BL7591' AND a.AR_REF IN (SELECT AR_REF FROM F_DOCLIGNE WHERE DO_PIECE='BL7591')

i have has expected

...|do_piece|ar_ref    |...
---------------------------
...|BL7591  |37LL.POM. |...

but when i do my request with NOT IN

SELECT* FROM ETIQUETTES AS a WHERE DO_PIECE='BL7591' AND a.AR_REF NOT IN (SELECT AR_REF FROM F_DOCLIGNE WHERE DO_PIECE='BL7591')

i have nothing

...|do_piece|ar_ref    |...
---------------------------

instead of

...|do_piece|ar_ref    |...
---------------------------
...|BL7591  |40L.PA.NAT|...

I have tried with NOT EXIST or LEFT JOIN / NULL but the result is always the same... I dont't understand why and how ta have the good result... Can you help me to understand my mistake please?

There is a `NULL` in the `F_DOCLIGNE.AR_REF` column. Can you post the `NOT EXISTS` you tried?` — ypercubeᵀᴹ, Oct 25 '13 at 10:43
your problem is probably because of nulls in the subquery, but take a look at this question also - http://stackoverflow.com/questions/18577622/sql-in-query-produces-strange-result/18579128 and please use aliases in subqueries — Roman Pekar, Oct 25 '13 at 10:54
NULLS are the usual suspects, but OTOH the query with the data from the OQ produces the intended results here. IOW: the question is wrong. — joop, Oct 25 '13 at 11:02
@joop. The question is ok. If the data is what OP says they are, the query with `NOT IN` should have a row with `'40L.PA.NAT'` in the result. — ypercubeᵀᴹ, Oct 25 '13 at 11:29
Exactly. When I try to reproduce the problem, I **do** get the intended behavior. So the question will probably be different from the real situation on the OP's database. (whitespace in the strings? Nullability of the strings ?) — joop, Oct 25 '13 at 11:44
Another closely related, recent question: http://stackoverflow.com/questions/19517899/not-in-in-postgresql-not-working/19528722 — Erwin Brandstetter, Oct 25 '13 at 17:56

ypercubeᵀᴹ · Accepted Answer · 2013-10-25T11:30:47.413

1

If there is a NULL in the F_DOCLIGNE.AR_REF column, this is expected behaviour - and a common trap of NOT IN.

This is how you can rewrite with NOT EXISTS - where you don't have such a problem:

SELECT e.* 
FROM ETIQUETTES AS e 
WHERE e.DO_PIECE = 'BL7591' 
  AND NOT EXISTS 
      (SELECT 1 
       FROM F_DOCLIGNE AS d 
       WHERE d.DO_PIECE = 'BL7591'
         AND d.AR_REF = e.AR_REF
      );

In Postgres, I think the LEFT JOIN / IS NULL has better efficiency than NOT EXISTS:

SELECT e.* 
FROM ETIQUETTES AS e 
  LEFT JOIN F_DOCLIGNE AS d 
    ON  d.DO_PIECE = 'BL7591'
    AND d.AR_REF = e.AR_REF
WHERE e.DO_PIECE = 'BL7591' 
  AND d.AR_REF IS NULL ;

Now, if there is nor row with NULL and your data are exactly as you say they are, this should not happen. The NOT IN query should return one row, with 'BL7591' , '37LL.POM.'

I suggest you check the table and column names in your queries and also prefix the columns with the table aliases - to be sure that there is no misspelling.

edited Oct 25 '13 at 11:30

answered Oct 25 '13 at 10:50

ypercubeᵀᴹ

113,259
19
174
235

1

Contrary to popular belief, the `LEFT JOIN WHERE NULL` version does **NOT** have better performance than the `NOT EXISTS` version in postgres. Please check the resulting query plans; these should be the same. – joop Oct 25 '13 at 10:58
results from F_DOCLIGNE table can have NULL AR_REF, yes. I already have tried NOT EXISTS and LEFT JOIN solutions (3 hours with no results) but with mistakes because yours are good! It's work. Thanks a lot! – Tokazio Oct 25 '13 at 11:51
1

@joop: `LEFT JOIN / IS NULL` and `NOT EXISTS` can (and often do) result in different query plans. `NOT EXISTS` is often faster, `LEFT JOIN / IS NULL` is often shorter syntax, and sometimes faster. I recently [summarized in this related answer](http://stackoverflow.com/questions/19363481/select-rows-which-are-not-present-in-other-table/19364694#19364694). – Erwin Brandstetter Oct 25 '13 at 17:59
@ErwinBrandstetter: I have been trying to find a scenario where a different plan is generated for `NOT EXISTS` and `LEFT JOIN+IS NULL`. I failed. Could you produce one? (maybe I should start a new question for this?) – wildplasser Oct 26 '13 at 17:37

POSTGRES-misunderstand with NOT IN

1 Answers1