SQL Server - NULL values with IN and NOT IN, Sql Server

SQL Server - NULL values with IN and NOT IN

--------------------------------------------------------------------------------
-- 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
;

--------------------------------------------------------------------------------
-- Hard coded list: NOT IN
--------------------------------------------------------------------------------
SELECT 'Test 1' AS [marker];

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

SELECT 'Test 2' AS [marker];

SELECT *
  FROM #ValueList
 WHERE val_1 NOT IN ('a', 'b', NULL) -- Returns no results
;

--------------------------------------------------------------------------------
-- Hard coded list: IN
--------------------------------------------------------------------------------
SELECT 'Test 3' AS [marker];

SELECT *
  FROM #ValueList
 WHERE val_1 IN ('a', 'b') -- Skips NULL
;

SELECT 'Test 4' AS [marker];

SELECT *
  FROM #ValueList
 WHERE val_1 IN ('a', 'b', NULL) -- Skips NULL
;


run \| edit \| history \| help	0