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.

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

Example

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

Overall

We now know how to create a SELECT statement with IS NULL operator to validate a field value.

Related Links