How to Filter Rows without NULL in a column

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.

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:

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.