SQL Server - NULL values with IN and NOT IN (Solution), Sql Server

SQL Server - NULL values with IN and NOT IN (Solution)

--------------------------------------------------------------------------------
-- Create source data
--------------------------------------------------------------------------------
CREATE TABLE #ValueList
(
    ID     INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    val_1  VARCHAR(10),
    metric INT
);

INSERT
  INTO #ValueList
      (val_1,metric)
VALUES('a',  10),
      ('b',  20),
      ('c',  30),
      (NULL, 40)
;

-- View our sample data
SELECT 'Show All Data' AS [marker];

SELECT *
  FROM #ValueList
;

--------------------------------------------------------------------------------
-- Solution: Using COALESCE 
--------------------------------------------------------------------------------
SELECT 'Solution 1' AS [marker];

SELECT *
  FROM #ValueList
 WHERE COALESCE(val_1, 'Z') NOT IN ('a', 'b')
;

--------------------------------------------------------------------------------
-- Solution: Using an OR condition to allow NULLs
--------------------------------------------------------------------------------
SELECT 'Solution 2' AS [marker];

SELECT *
  FROM #ValueList
 WHERE(   val_1 NOT IN ('a', 'b')
       OR val_1 IS NULL
      )
;

--------------------------------------------------------------------------------
-- Solution: Using WHERE NOT EXISTS and a lookup table
--------------------------------------------------------------------------------
SELECT 'Solution 3' AS [marker];

-- Create a lookup list
--------------------------------------------------------------------------------
CREATE TABLE #LookupList
(
    val_1 VARCHAR(10) NOT NULL PRIMARY KEY
);

INSERT
  INTO #LookupList
      (val_1)
VALUES('a'),
      ('b')
;

-- Filter using WHERE (NOT) EXISTS
--------------------------------------------------------------------------------
SELECT *
  FROM #ValueList vl
 WHERE NOT EXISTS
      (SELECT *
         FROM #LookupList ll
        WHERE ll.val_1 = vl.val_1
      )
;

SELECT *
  FROM #ValueList vl
 WHERE EXISTS
      (SELECT *
         FROM #LookupList ll
        WHERE ll.val_1 = vl.val_1
      )
;


run \| edit \| history \| help	0