SQL: Where Clause Predicates Exclude Null By Default

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:

And you run the following query against the table:

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

We will only get three rows back:

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:

Copycopy code to clipboard
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: