NOT IN with Nulls - unclear behaviour

Question

The following query returns NO data:

select * from ediImport 
WHERE glnfact NOT IN (select GLN from Clients)

After some search, I found the data I want, this way:

select * from ediImport 
WHERE glnfact NOT IN (select GLN from Clients WHERE gln is not null)

But I feel like the first query SHOULD return the information (and I think it WOULD return it in Access).
So my questions:
- why is first query not working
- is there a better, more efficient way to do this ? I found EXISTS and ANY but I can't see any advantage over the old school way.

---

Note: I don't want to use a left join here since my REAL need is to perform an UPDATE:

UPDATE ediImport SET Status = 2 
WHERE glnfact NOT IN (select GLN from Clients WHERE gln is not null)

Answer 1

First query returns no data because of comparing null values. When you compare 2 values result could be

• TRUE if they equals
• FALSE if they're not
• UNKNOWN if one or two of the values is null

So when you use not in sql should compare your value glnfact with all values of GLN in the subquery and if all compares return FALSE, then it returns TRUE for the whole not in clause. If one of the value of GLN is null, comparing it with glnfact returns UNKNOWN so not in clause is UNKNOWN.

Answer 2

^{Answering because of your edit}

If you want to update Status = 2 in the table ediImport which is NULL in the table Clients, you CAN use LEFT JOIN in UPDATE statement like this:

UPDATE ei SET ei.Status = 2
  FROM ediImport as ei
  LEFT JOIN Clients c
    ON ei.glnfact = c.gln
 WHERE c.gln IS NULL;

See this sample SQLFiddle

This is equivalent to UPDATE using NOT IN. See this SQLFiddle