Back to cookbooks list Articles Cookbook

How to Find Records with NULL in a Column

Database:

Operators:

  • WHERE
  • IS NULL

Table of Contents

Problem:

You want to find records with NULL in a column.

Example:

Our database has a table named children with data in four columns: id, first_name, middle_name, and last_name.

idfirst_namemiddle_namelast_name
1JohnCarlJackson
2TomNULLThomson
3LisaAliceNULL
4AnneNULLSmith

Let’s find the id, first name, and last name of children without a middle name (NULL in column middle_name). We use the IS NULL operator for this.

Solution:

SELECT id,
  first_name,
  last_name
FROM children
WHERE middle_name IS NULL;

Here’s the result:

idfirst_namelast_name
2TomThomson
4AnneSmith

This returns only records with NULL in middle_name, so Tom and Anne are present, but not John and Lisa who have middle names.

Discussion:

Use the IS NULL operator in a condition with WHERE to find records with NULL in a column. Of course, you can also use any expression instead of a name of a column and check if it returns NULL. Nothing more than the name of a column and the IS NULL operator is needed (in our example, middle_name IS NULL). Put this condition in the WHERE clause to filter rows (in our example, WHERE middle_name IS NULL). If the condition is true, the column stores a NULL and this row is returned. Above, the query returns only two records with children Tom and Anne, who don’t have middle names, so the column middle_name stores NULL.

Recommended courses:

Recommended articles:

See also: