NULL Values on query condition

I'm trying to compare null values using not in and exists but I found some diferences Why the latest query returns null? is it related to a db parameter?

--SET ANSI_NULLS On

CREATE TABLE #Tmp (id INT)
CREATE TABLE #Tmp1 (id INT)

INSERT INTO #Tmp(id) VALUES (1)
INSERT INTO #Tmp(id) VALUES (null)


INSERT INTO #Tmp1(id) VALUES (1)
INSERT INTO #Tmp1(id) VALUES (null)

SELECT id FROM #Tmp WHERE id IN (SELECT id FROM #tmp1)

SELECT id FROM #Tmp WHERE EXISTS(SELECT 1 FROM #Tmp1 WHERE #Tmp1.id = #Tmp.id)

SELECT id FROM #Tmp WHERE id NOT IN (SELECT id FROM #tmp1)

SELECT id FROM #Tmp WHERE NOT EXISTS(SELECT 1 FROM #Tmp1 WHERE #Tmp1.id = #Tmp.id)

DROP TABLE #Tmp
DROP TABLE #Tmp1 

2 answers

  • answered 2018-10-11 19:42 Lukasz Szozda

    It is very simple:

    id IN (1, NULL)
    <=>
    id = 1 OR id = NULL
    

    And negation of alternative:

    id NOT IN (1, NULL)
    <=>
    id != 1 AND id != NULL -- yields NULL
    

    Summary:

    If you want to use NOT IN make sure your list does not contain NULL.

    SELECT id FROM #Tmp WHERE id NOT IN (SELECT id FROM #tmp1 WHERE id IS NOT NULL)
    
    SELECT id FROM #Tmp WHERE id NOT IN (SELECT COALESCE(id,-1) FROM #tmp1)
    

  • answered 2018-10-11 19:45 dfundako

    In SQL Server, remember that NULL does not return an equality check of TRUE with anything (unless you are playing with ANSI_NULLS):

    SELECT 1 
    WHERE 1=NULL
    
    SELECT 1
    WHERE NULL = NULL
    

    It only returns True when you use IS/IS NOT

    SELECT 1
    WHERE NULL IS NULL
    

    For your last query, the only record that satisfies that requirement in the WHERE clause is your NULL row. The equivalent is:

    SELECT id FROM #Tmp 
    WHERE id IS NULL
    

    To solve your issue, throw a second clause on there:

    SELECT id FROM #Tmp 
    WHERE NOT EXISTS(
    SELECT 1 
    FROM #Tmp1 
    WHERE #Tmp1.id = #Tmp.id OR #Tmp.id IS NULL
    )