none
NULL in IN clause

    Question

  • Hi all,

    Could you have an NULL value in a IN Clause ?

    eg: SELECT * FROM table WHERE Field IN ('a', NULL, 'b') ?

    I could not get that to work ?

    Thanks
    Tuesday, March 10, 2009 7:42 PM

Answers

  • Try something like this...

    SELECT *   
    FROM table   
    WHERE (Field IN ('a''b'OR Field IS NULL

    Hope this helps.
    Rafael Medeiros
    • Marked as answer by CSharpAlex Friday, March 13, 2009 3:13 PM
    Tuesday, March 10, 2009 8:59 PM

All replies

  • No. Null is not equal to NULL.
    Tuesday, March 10, 2009 7:53 PM
  • Try:

    select 17
    where 1 in (0, 1, null);

    The IN operator is translated as "1 = 0 OR 1 = 1 OR 1 = NULL". Only NULL values in the left side will yield NULL because NULL is not equal to any other value, including NULL. Those nulls in the left side will not be part of the result.

    select 17
    where NULL in (NULL, 0);


    Now, if you are using NOT IN, then the whole comparison will yield null, because of the translation.

    select 17
    where 1 not in (0, 2, null);

    is equivalent to:

    select 17
    where not (1 = 0 OR 1 = 2 OR 1 = NULL)';

    which is equivalent to:

    select 17
    where 1 <> 0 and 1 <> 2 and 1 <> null;

    Because the expressions are connected using the logical operator AND, then if one of them yields unknown then the whole expression yields unknown also.


    AMB

    Tuesday, March 10, 2009 7:58 PM
  • Try something like this...

    SELECT *   
    FROM table   
    WHERE (Field IN ('a''b'OR Field IS NULL

    Hope this helps.
    Rafael Medeiros
    • Marked as answer by CSharpAlex Friday, March 13, 2009 3:13 PM
    Tuesday, March 10, 2009 8:59 PM
  • Actually there is way - by doing SET the ANSI_NULLS OFF. But in my opinion, not a good idea.

     
    SET ANSI_NULLS OFF 
     
    DECLARE @T Table (ID INT, NAMES VARCHAR(1))  
     
    INSERT INTO @T SELECT 
    1,'A' UNION ALL SELECT 
    2, 'B' UNION ALL SELECT 
    NULL,'C' 
     
    SELECT NAMES  
    FROM @T  
    WHERE ID IN (1,NULL


    For more on ANSI_NULLS read from BOL - SET ANSI_NULLS (Transact-SQL)
    Mangal Pardeshi
    SQL With Mangal
    Technical Skill is the mastery of complexity, while Creativity is the master of simplicity
    • Proposed as answer by Shivs.. _ Tuesday, March 24, 2009 10:27 AM
    Friday, March 13, 2009 8:02 AM
  • Hi,

    Answer posted by Rafael looks good to me. This is one of the simplest method's.

    Select * from table
    where (field in ('a','b') or field is null)
    Friday, March 13, 2009 1:29 PM
  • how about 

    SELECT * FROM table WHERE isnull(Field,'') IN ('a', '', 'b')

    note this will include empty strings ('')

    You might want to exclude them

    Friday, March 13, 2009 3:03 PM