locked
=NULL,=ISNULL(), is NULL RRS feed

  • Question

  • Hi all,
          1.Who can tell me what 's different between TranSact-Sql =NULL,ISNULL() and IS NULL?
          2.If I want to insert to database NULL value ,what type I should use in C#?
    Tuesday, December 22, 2009 6:30 PM

Answers

  • Another issue that lurks in the = NULL syntax:

    NULL values are particularly problematic for a query that might involve the NOT IN syntax. 

    select
      id, someKindOfType
    from whatever
    where someKindOfType not in
    ( select someKindOfType from someOtherTable )

    If the "someOtherTable" contains any rows in which "someKindOfType" is null, this query will never return any rows because the results of the "not in" comparison will always be either FALSE or NULL.

    Three-state logic comes into play when the comparison evaluates to a logical NULL, so you might also want to look into 3-state logic.

    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    • Edited by Kent Waldrop Tuesday, October 25, 2011 2:42 PM
    Tuesday, December 22, 2009 6:49 PM
  • Hi all,
          1.Who can tell me what 's different between TranSact-Sql =NULL,ISNULL() and IS NULL?
          2.If I want to insert to database NULL value ,what type I should use in C#?

    = NULL is an archaic form to check whether or not a column contains a NULL values from the 90's and pre-dates the Microsoft Transact SQL code all the way back to the 80's.  This form is now usually syntax that is recognized as valid but usually does not function as intended.  You should avoid this code.  The logical result of this test is neither true nor false; this logical comparator always has a logical value of NULL.

    The ISNULL() function might also pre-date the Microsoft Transact SQL; this function returns the first parameter passed to the function unless the first parameter is null.  If the first parameter is null, the function returns the second parameter.  Alternatives to the ISNULL() function are to either use CASE syntax (which normally I would not swtich to) or to use a COALESCE function.  The ISNULL() function will provide a modest performance improvement over COALESCE in some circumstances, but ISNULL() is specific to Transact SQL but is not cross compatible with either Oracle or DB2.  COALESCE will perform somewhat less efficiently than ISNULL() but is cross compatible with both Oracle and DB2.

    The IS NULL syntax is a the current method for checking to see if a value is null in ANSI SQL.  This is the syntax that replaced the previous archaic "= NULL" syntax for checking whether or not a value is null.  This logical comparator will always return TRUE or FALSE and will not result in a logical value of NULL.

    I cannot answer part 2; help please?
    • Edited by Kent Waldrop Tuesday, December 22, 2009 6:54 PM
    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:39 PM
  • NULL means nothing. Check out this code. If V is not inserted and the table allows NULL, it will be the default value. If you want to return values where V is null, the way to check it is to use IS NULL. You can't say WHERE V = NULL because NULL is not comparable to anything.
    No, ISNULL is a function that checks if the value being returned is null, and if it's change it. Check out the last SELECT in the code below.

    DECLARE @t TABLE (ID INT, V VARCHAR(20))
    INSERT INTO @t(ID)
    SELECT 1 UNION ALL
    SELECT 2
    
    SELECT * FROM @t WHERE V IS NULL
    
    SELECT ID,ISNULL(V,'Not available') FROM @t
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:41 PM
  • 1)
    =NUll ---> Used to update statement for updating column values as Null

    Eg
    Update Emp Set Date_Of_Resign = Null Where Emp_ID=10

    2)IsNUll() --> used for check the column value as null or not, if column value is not null , return the column value ,else return the assigned value

    Eg
    Select Isnull(Date_Of_Resign,'2000-01-01') from Emp

    3)IS NULL
    Used in Where clause to filter the null value columns

    Eg

    Select * from EMp where Date_Of_Resign Is null

    If you want to insert the null value to columns , you can use the Nullable data types

    DateTime?
    Int?




    Happy Codings, RDRaja
    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:41 PM
  • For question #2, you could use dbnull.value or convert.dbnull

    DBNull Class<!---->
    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:50 PM

All replies

  • Hi all,
          1.Who can tell me what 's different between TranSact-Sql =NULL,ISNULL() and IS NULL?
          2.If I want to insert to database NULL value ,what type I should use in C#?

    = NULL is an archaic form to check whether or not a column contains a NULL values from the 90's and pre-dates the Microsoft Transact SQL code all the way back to the 80's.  This form is now usually syntax that is recognized as valid but usually does not function as intended.  You should avoid this code.  The logical result of this test is neither true nor false; this logical comparator always has a logical value of NULL.

    The ISNULL() function might also pre-date the Microsoft Transact SQL; this function returns the first parameter passed to the function unless the first parameter is null.  If the first parameter is null, the function returns the second parameter.  Alternatives to the ISNULL() function are to either use CASE syntax (which normally I would not swtich to) or to use a COALESCE function.  The ISNULL() function will provide a modest performance improvement over COALESCE in some circumstances, but ISNULL() is specific to Transact SQL but is not cross compatible with either Oracle or DB2.  COALESCE will perform somewhat less efficiently than ISNULL() but is cross compatible with both Oracle and DB2.

    The IS NULL syntax is a the current method for checking to see if a value is null in ANSI SQL.  This is the syntax that replaced the previous archaic "= NULL" syntax for checking whether or not a value is null.  This logical comparator will always return TRUE or FALSE and will not result in a logical value of NULL.

    I cannot answer part 2; help please?
    • Edited by Kent Waldrop Tuesday, December 22, 2009 6:54 PM
    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:39 PM
  • NULL means nothing. Check out this code. If V is not inserted and the table allows NULL, it will be the default value. If you want to return values where V is null, the way to check it is to use IS NULL. You can't say WHERE V = NULL because NULL is not comparable to anything.
    No, ISNULL is a function that checks if the value being returned is null, and if it's change it. Check out the last SELECT in the code below.

    DECLARE @t TABLE (ID INT, V VARCHAR(20))
    INSERT INTO @t(ID)
    SELECT 1 UNION ALL
    SELECT 2
    
    SELECT * FROM @t WHERE V IS NULL
    
    SELECT ID,ISNULL(V,'Not available') FROM @t
    

    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:41 PM
  • 1)
    =NUll ---> Used to update statement for updating column values as Null

    Eg
    Update Emp Set Date_Of_Resign = Null Where Emp_ID=10

    2)IsNUll() --> used for check the column value as null or not, if column value is not null , return the column value ,else return the assigned value

    Eg
    Select Isnull(Date_Of_Resign,'2000-01-01') from Emp

    3)IS NULL
    Used in Where clause to filter the null value columns

    Eg

    Select * from EMp where Date_Of_Resign Is null

    If you want to insert the null value to columns , you can use the Nullable data types

    DateTime?
    Int?




    Happy Codings, RDRaja
    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:41 PM
  • Another issue that lurks in the = NULL syntax:

    NULL values are particularly problematic for a query that might involve the NOT IN syntax. 

    select
      id, someKindOfType
    from whatever
    where someKindOfType not in
    ( select someKindOfType from someOtherTable )

    If the "someOtherTable" contains any rows in which "someKindOfType" is null, this query will never return any rows because the results of the "not in" comparison will always be either FALSE or NULL.

    Three-state logic comes into play when the comparison evaluates to a logical NULL, so you might also want to look into 3-state logic.

    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    • Edited by Kent Waldrop Tuesday, October 25, 2011 2:42 PM
    Tuesday, December 22, 2009 6:49 PM
  • For question #2, you could use dbnull.value or convert.dbnull

    DBNull Class<!---->
    http://msdn.microsoft.com/en-us/library/system.dbnull.aspx

    • Marked as answer by LancelotX Tuesday, December 22, 2009 7:29 PM
    Tuesday, December 22, 2009 6:50 PM
  • Another issue that lurks in the = NULL syntax:

    NULL values are particularly problematic for a query that might involve the NOT in syntax. 

    select
      id, someKindOfType
    from whatever
    where someKindOfType not in
    ( select someKindOfType from someOtherTable )

    If the "someOtherTable" contains any rows in which "someKindOfType" is null, this query will never return any rows because the results of the "not in" comparison will always be either FALSE or NULL.

    Thanks ,much helpful.it's particular. and also thanks everyone for helping me.
    Tuesday, December 22, 2009 7:28 PM