none
null behaviour

    Question

  • I have a column where I have a unique constraint…

     

     

    I bring data from a source…

     

    And I do:

     

    insert into target

    select * from source

    where Id not in (select id from target)

     

     

    And when the id from source Is null… it will insert it, why?

     

     

    Null is an unknown value… why it assumes the NULL is not in the target?

    Thursday, June 14, 2018 9:23 AM

All replies

  • NULL is unknown

    so it cant compare between two unknowns and determine whether two unknowns are same or not

    hence it inserts the NULL and will cause UNIQUE constraint to fail

    this is how NULL is treated by SQLServer under default conditions

    If you want to consider NULL as a value you need to tweak ANSI NULL settings

    Also if you think from query perspective

    where Id not in (select id from target)


    is equivalent to

    where Id <> idvalue1
    AND Id <> idvalue2
    ...
    

    where idvalue1 ,idvalue2 etc are values in target table

    so when you consider NULL value in target table what happens is that comparison would be like
    NULL <> NULL which will always returns false and hence NULL value from source will be returned by the NOT IN and will try to insert to your target


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Thursday, June 14, 2018 9:32 AM
  • Additional see NULL and UNKNOWN (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, June 14, 2018 9:45 AM
    Moderator
  • Apart from the points above, try is ISNULL(x,y) , to flush out NULLs

    Also, unique constraint accepts a single NULL value , set the destination column as NOT NULL.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, June 14, 2018 11:39 AM
  • insert into target
    select * from source
    where Id not in (select id from target)
    

    Y'know, you may have a point.

    Null is never in a list, null is never not in a list, the predicate has no answer for null, so it would seem the predicate is not true for null, so null should not be inserted.

    … unless there is some specific ANSI standard reason for special handling in such a case, like "not in" being taken for the negative of "in", and since "in" would (also) not include null, then "not in" *would* include null.  I don't know, I'm just speculating, but there are some jewels like this in the ANSI standard.

    Josh

    Thursday, June 14, 2018 4:15 PM
  • And when the id from source Is null? it will insert it, why?

    Huh? It does not insert. Look here:

    CREATE TABLE target (id int NULL UNIQUE)
    INSERT target VALUES (1), (2), (3)
    CREATE TABLE source (id int NULL UNIQUE)
    INSERT source VALUES (1), (2), (3), (NULL), (15)
    go
    insert into target
    select * from source
    where id not in (select id from target)
    go
    SELECT id FROM target
    go
    DROP TABLE target, source

    15 is inserted. The NULL is not.

    Please provide a complete repro of what you are seeing.

    Thursday, June 14, 2018 9:31 PM
  • What's going on here?  The index says Erland has a late post to this thread, but I don't see it.

    Josh

    Friday, June 15, 2018 5:25 AM
  • What's going on here?  The index says Erland has a late post to this thread, but I don't see it.

    That is because I post through the NNTP bridge. There can sometimes be a delay before such posts show up in the thread. I can see my post now.

    Friday, June 15, 2018 7:10 AM
  • And when the id from source Is null? it will insert it, why?

    Huh? It does not insert. Look here:

    CREATE TABLE target (id int NULL UNIQUE)
    INSERT target VALUES (1), (2), (3)
    CREATE TABLE source (id int NULL UNIQUE)
    INSERT source VALUES (1), (2), (3), (NULL), (15)
    go
    insert into target
    select * from source
    where id not in (select id from target)
    go
    SELECT id FROM target
    go
    DROP TABLE target, source

    15 is inserted. The NULL is not.

    Please provide a complete repro of what you are seeing.

    Just tried this

    Yep only 15 gets inserted here

    and in case target also contains an instance of NULL, no rows get inserted

    see

    I think its understandable as condition will become like

    WHERE ( 1 <> 1
    AND 1 <> 2
    AND 1 <> 3
    AND 1 <> NULL
    ...
    

    1 <> NULL is going to be always false hence 

    select * from source
    where id not in (select id from target)

    is going to always return a empty resultset

    and target tables contents will remain as is


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 15, 2018 7:56 AM
  • and in case target also contains an instance of NULL, no rows get inserted

    Yes, that is a common trap with NOT IN and nullable columns. This is why NOT EXISTS is better. But now we are straying from the original question, at least as long as maca128 does not clarify what he/she actually saw.

    Friday, June 15, 2018 8:01 AM
  • and in case target also contains an instance of NULL, no rows get inserted

    Yes, that is a common trap with NOT IN and nullable columns. This is why NOT EXISTS is better. But now we are straying from the original question, at least as long as maca128 does not clarify what he/she actually saw.

    yes

    Let he/she come back

    I was just reverting with my observations based on the posted code


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, June 15, 2018 8:30 AM