Skip to content
Lew Dawson

SQL: Where Clause Predicates Exclude Null By Default

TodayIRevisited, SQL, Programming1 min read

This is a common one that has probably bitten every person who has ever used SQL. When one defines a where predicate on a SQL statement, only values that evaluate to true against the predicate AND are not null will be returned.

For example, if we have the following data in the dim_department table:

department_nameemployee_count
complianceNULL
legal1
it3
finance5

And you run the following query against the table:

1SELECT
2 department_name,
3 employee_count
4FROM dim_department
5WHERE employee_count >= 0
6;

We will only get three rows back:

department_nameemployee_count
legal1
it3
finance5

The row where department_name = 'compliance' will be excluded from the result set. We must explicitly add a null-inclusion statement to the predicate to get back this row:

1SELECT
2 department_name,
3 employee_count
4FROM dim_department
5WHERE employee_count >= 0
6OR employee_count IS NULL
7;

Now we will get all four rows back:

department_nameemployee_count
complianceNULL
legal1
it3
finance5
© 2021 by Lew Dawson. All rights reserved.