Back to list Standard SQL How to Find Records with NULL in a Column Database: SQL MySQL PostgreSQL Oracle MS SQL Server SQLite Operators:WHERE, IS NULL 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: SQL Basics SQL Basics in SQL Server SQL Practice Set Recommended articles: The SQL Coalesce Function: Handling Null Values See also: How to Use LIKE in SQL How to Filter Rows without NULL in a column Tags: SQL MySQL PostgreSQL Oracle MS SQL Server SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.