Back to cookbooks list Articles Cookbook

How to Filter Rows without NULL in a column

Database:

Operators:

  • WHERE
  • IS NOT NULL

Table of Contents

Problem:

You want to find records without a NULL in a column.

Example:

Our database has a table named product with data in three columns: id, name, and price.

idnameprice
1butterNULL
2milk2.35
3bread3.25
4cheeseNULL

Let’s find the names and prices of products that have a price (without a NULL). Do this by using the IS NOT NULL operator.

Solution:

SELECT name, 
  price
FROM product
WHERE price IS NOT NULL;

Here’s the result of the query:

nameprice
milk2.35
bread3.25

This returns only records without NULL in the column, so only the names and prices of milk and bread are present. Butter and cheese don’t have prices (the column price is NULL) so you don’t see them.

Discussion:

To display records without NULL in a column, use the operator IS NOT NULL. You only need the name of the column (or an expression) and the operator IS NOT NULL (in our example, the price IS NOT NULL). Put this condition in the WHERE clause (in our example, WHERE price IS NOT NULL), which filters rows.

If the condition is true, it denotes the column doesn’t store a NULL. The row is returned if it has a value in this column (the column isn’t NULL). From above, the query returns only two products, milk and bread, because they have values in price.

Recommended courses:

Recommended articles:

See also: