Not equal operator on NULL

Question

Could someone please explain the following behavior in SQL   SELECT   FROM MyTable WHERE MyColumn    NULL  0 Results  SELECT   FROM MyTable WHERE MyColumn  lt  gt  NULL  0 Results  SELECT   FROM MyTable WHERE MyColumn IS NOT NULL  568 Results

User · Answer

NULL Cannot be compared to any value using the comparison operators  NULL   NULL is false  Null is not a value  The IS operator is specially designed to handle NULL comparisons

User · Answer

We use  SELECT   FROM MyTable WHERE ISNULL MyColumn                to return all rows where MyColumn is NULL or all rows where MyColumn is an empty string   To many an  end user   the NULL vs  empty string issue is a distinction without a need and point of confusion

User · Answer

I would like to suggest this code I made to find if there is a change in a value  i being the new value and d being the old  although the order does not matter   For that matter  a change from value to null or vice versa is a change but from null to null is not  of course  from value to another value is a change but from value to the same it is not    CREATE FUNCTION  dbo   ufn equal with nulls         i sql variant       d sql variant   RETURNS bit AS BEGIN     DECLARE  in bit   0   dn bit   0     if  i is null set  in   1     if  d is null set  dn   1      if  in  lt  gt   dn         return 0      if  in   1 and  dn   1         return 1      if  in   0 and  dn   0 and  i    d         return 1      return 0  END   To use this function  you can  declare  tmp table  a int  b int  insert into  tmp values  1 1    1 2    1 null    null 1    null null   ---- in select ---- select     dbo   ufn equal with nulls  a b  as     from  tmp  ---- where equal ---- select    equal  as  Predicate  from  tmp where   dbo   ufn equal with nulls  a b    1  ---- where not equal ---- select    not equal  as  Predicate  from  tmp where   dbo   ufn equal with nulls  a b    0   The results are   ---- in select ---- a   b     1   1   1 1   2   0 1   NULL    0 NULL    1   0 NULL    NULL    1  ---- where equal ---- 1   1   equal NULL    NULL    equal  ---- where not equal ---- 1   2   not equal 1   NULL    not equal NULL    1   not equal   The usage of sql variant makes it compatible for variety of types

User · Answer

The only test for NULL is IS NULL or IS NOT NULL   Testing for equality is nonsensical because by definition one doesn t know what the value is   Here is a wikipedia article to read   https   en wikipedia org wiki Null  SQL

User · Answer

lt  gt  is Standard SQL-92     is its equivalent   Both evaluate for values  which NULL is not -- NULL is a placeholder to say there is the absence of a value   Which is why you can only use IS NULL IS NOT NULL as predicates for such situations   This behavior is not specific to SQL Server  All standards-compliant SQL dialects work the same way   Note  To compare if your value is not null  you use IS NOT NULL  while to compare with not null value  you use  lt  gt   YOUR VALUE   I can t say if my value equals or not equals to NULL  but I can say if my value is NULL or NOT NULL  I can compare if my value is something other than NULL

User · Answer

In SQL  anything you evaluate   compute with NULL results into UNKNOWN  This is why SELECT   FROM MyTable WHERE MyColumn    NULL or SELECT   FROM MyTable WHERE MyColumn  lt  gt  NULL gives you 0 results    To provide a check for NULL values  isNull function is provided   Moreover  you can use the IS operator as you used in the third query   Hope this helps

User · Answer

Old question  but the following might offer some more detail   null represents no value or an unknown value  It doesn   t specify why there is no value  which can lead to some ambiguity   Suppose you run a query like this   SELECT   FROM orders WHERE delivered ordered    that is  you are looking for rows where the ordered and delivered dates are the same   What is to be expected when one or both columns are null   Because at least one of the dates is unknown  you cannot expect to say that the 2 dates are the same  This is also the case when both dates are unknown  how can they be the same if we don   t even know what they are   For this reason  any expression treating null as a value must fail  In this case  it will not match  This is also the case if you try the following   SELECT   FROM orders WHERE delivered lt  gt ordered    Again  how can we say that two values are not the same if we don   t know what they are   SQL has a specific test for missing values   IS NULL   Specifically it is not comparing values  but rather it seeks out missing values   Finally  as regards the    operator  as far as I am aware  it is not actually in any of the standards  but it is very widely supported  It was added to make programmers from some languages feel more at home  Frankly  if a programmer has difficulty remembering what language they   re using  they   re off to a bad start

User · Answer

NULL is not anything   it is unknown   NULL does not equal anything   That is why you have to use the magic phrase IS NULL instead of   NULL in your SQL queries  You can refer this  http   weblogs sqlteam com markc archive 2009 06 08 60929 aspx

User · Answer

I just don t see the functional and seamless reason for nulls not to be comparable to other values or other nulls  cause we can clearly compare it and say they are the same or not in our context  It s funny  Just because of some logical conclusions and consistency we need to bother constantly with it  It s not functional  make it more functional and leave it to philosophers and scientists to conclude if it s consistent or not and does it hold  universal logic      Someone may say that it s because of indexes or something else  I doubt that those things couldn t be made to support nulls same as values  It s same as comparing two empty glasses  one is vine glass and other is beer glass  we are not comparing the types of objects but values they contain  same as you could compare int and varchar  with null it s even easier  it s nothing and what two nothingness have in common  they are the same  clearly comparable by me and by everyone else that write sql  because we are constantly breaking that logic by comparing them in weird ways because of some ANSI standards  Why not use computer power to do it for us and I doubt it would slow things down if everything related is constructed with that in mind   It s not null it s nothing   it s not apple it s apfel  come on    Functionally is your friend and there is also logic here  In the end only thing that matter is functionality and does using nulls in that way brings more or less functionality and ease of use  Is it more useful   Consider this code   SELECT CASE WHEN NOT  1   null or  1 is null and null is null   THEN 1 ELSE 0 end   How many of you knows what will this code return  With or without NOT it returns 0  To me that is not functional and it s confusing  In c  it s all as it should be  comparison operations return value  logically this too produces value  because if it didn t there is nothing to compare  except  nothing       They just  said   anything compared to null  returns  0 and that creates many workarounds and headaches   This is the code that brought me here   where a    b OR  a is null and b IS not null  OR  a IS not null and b IS null    I just need to compare if two fields  in where  have different values  I could use function  but

User · Answer

NULL has no value  and so cannot be compared using the scalar value operators   In other words  no value can ever be equal to  or not equal to  NULL because NULL has no value   Hence  SQL has special IS NULL and IS NOT NULL predicates for dealing with NULL

User · Answer

Note that this behavior is the default  ANSI  behavior   If you    SET ANSI NULLS OFF   http   msdn microsoft com en-us library ms188048 aspx  You ll get different results   SET ANSI NULLS OFF will apparently be going away in the future

[sql] Not equal <> != operator on NULL

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to null