NULL is Not Included in NOT IN Results

Help Center
1

Introduction

When using the NOT IN filter, query results do not include rows where the column value used in the comparison is NULL, even though NULL is not in the list of values to match. By the end of this article, you will know how to include NULL values in your NOT IN queries effectively.

TL;DR

  • SQL comparisons involving NULL result in UNKNOWN.

  • NOT IN filters exclude rows where the compared column is NULL.

  • Use an additional check for NULL to include these rows.

  • Firebolt SQL syntax requires specific handling for this.

Step-by-step guide

All the example SQL code uses the Ultra Fast Gaming data set. To familiarize yourself with this data set, visit this link: Ultra Fast Gaming Firebolt Sample Dataset.

Step 1: Understanding the Behavior of NULL in SQL Comparisons

In SQL, NULL represents an unknown value. Therefore, queries with NOT IN filters will exclude rows where the column value is NULL because the result of the comparison is UNKNOWN.

Example SQL code:

WITH tbl1 AS (
SELECT playerid , agecategory FROM players WHERE playerid IN (858)
UNION ALL
SELECT NULL , 'text' 
)
SELECT * FROM tbl1 WHERE playerid NOT IN (1,2,3);

-- This query returns the row for playerid 858, as 858 does not match 1,2,3

Step 2: Handling NULL Values in NOT IN Filters

To correctly handle NULL values, modify your query to include an OR condition that explicitly checks for NULL values.

Example SQL code:

WITH tbl1 AS (
SELECT playerid , agecategory FROM players WHERE playerid IN (858)
UNION ALL
SELECT NULL , 'text' 
)
SELECT * FROM tbl1 WHERE playerid NOT IN (1,2,3) OR playerid IS NULL;

-- This query includes rows where playerid is NULL and 858

Full example code

WITH tbl1 AS (
SELECT playerid , agecategory FROM players WHERE playerid IN (858)
UNION ALL
SELECT NULL , 'text' 
)
SELECT * FROM tbl1 WHERE playerid NOT IN (1,2,3);


WITH tbl1 AS (
SELECT playerid , agecategory FROM players WHERE playerid IN (858)
UNION ALL
SELECT NULL , 'text' 
)
SELECT * FROM tbl1 WHERE playerid NOT IN (1,2,3) OR playerid IS NULL;