Description

The SQL provides the below operators to validate NULL values in a database table.

  • The operator IS NULL returns TRUE if the value is NULL or empty.
  • The operator IS NOT NULL returns TRUE if the value is not NULL or non-empty.
  • The NULL values cannot be validated with the comparison operators, like =, < , >, <>, etc., So, we need to use these operators when we need to validate NULL values.

A NULL value is considered to be a value that is not set, which is not a zero, space, or any other value.

  • If a table column is defined as optional, the database allows us to INSERT a record without passing a value to such a field. In that case, the field is set to NULL when the record is added to the table.
  • So, there are chances of having NULL values in database records.

Syntax

Here is the basic syntax for IS NULL and IS NOT NULL operators.

SELECT column_list
FROM table_name
WHERE column_name IS NULL;
SELECT column_list
FROM table_name
WHERE column_name IS NOT NULL;

Table Data

Before executing any queries, consider the below data on the tables, with NULL values against the city field on some records.

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
5 Thomas Hardy 89 Chiaroscuro Rd. NULL USA 97219
6 Christina Aguilera Gran Va, 1 Madrid Spain 28001
7 Hanna Moos Forsterstr. 57 NULL Germany 68306
8 Frdrique Citeaux 24, place Klber Strasbourg France 67000
9 Laurence Lebihan 12, rue des Bouchers NULL France 13008
10 Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen Canada T2F 8M4

IS NULL Operator

The below SELECT statement returns the rows, containing NULL or empty values against the city field.

Run this on IDE

SELECT *
FROM customers
WHERE city IS NULL;

After successful execution, the output contains the below data.

customer_id customer_name address  city country postal_code
5 Thomas Hardy 89 Chiaroscuro Rd. NULL USA 97219
7 Hanna Moos Forsterstr. 57 NULL Germany 68306
9 Laurence Lebihan 12, rue des Bouchers NULL France 13008

IS NOT NULL Operator

The below SELECT statement returns the rows, containing non-NULL or non-empty values against the city field.

Run this on IDE

SELECT *
FROM customers
WHERE city IS NOT NULL;

After successful execution, the output contains the below data.

customer_id customer_name address  city country postal_code
1 Maria Anders Obere Str. 57 Berlin Germany 12209
2 Fran Wilson C/ Araquil, 67 Madrid Spain 28023
3 Dominique Perrier 25, rue Lauriston Paris France 75016
4 Martin Blank Via Monte Bianco 34 Turin Italy 10100
6 Christina Aguilera Gran Va, 1 Madrid Spain 28001
8 Frdrique Citeaux 24, place Klber Strasbourg France 67000
10 Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen Canada T2F 8M4

Overall

We now know how to check for NULL and non-NULL values on a database table.

In the above examples, we have looked at SELECT statements, but these operators can be used on other types of statements as well.

Related Links