SQL NULL VALUES | IS NULL and IS NOT NULL Operator


SQL NULL value is used to represent a missing value or unknown data in a field. By default, a table field can hold NULL values.

SQL NULL values are treated differently from other data values by sql database engine. Ex: A SQL NULL value is different than a zero value or a column that contains spaces.

SQL NULL value mainly used as a container for unknown data or inapplicable values.


Search Keys

  • sql null
  • is null sql
  • oracle isnull
  • isnull in sql server
  • is not null sql
  • data null
  • sql case when null
  • sql where not null
  • t sql isnull
  • sql ifnull
  • null value in database
  • sql null date
  • if isnull sql
  • sql isnull example
  • isnull function
  • sql null to zero
  • sql update set null

Sample Database Table - Books

In this below table, the "Description" field in the "Books" table is optional. This means that if we add a new row with no value for the "Description" field, the "Description" field will be stored with a SQL NULL value.

Look at the following "Books" table:

BookId BookName Description
101 Sql Complete Reference It descripes how to write and execute SQL statement into database.
102 Sql Commands
103 Pl Sql Quick Programming How to write and execute SQL programming using pl sql.
104 Sql Query Injection
105 The Power Of Pl Sql
106 Sql Data Analysis It explains how to build and manage SQL database
107 Pl Sql Programming SQL and programming by pl sql.
108 Sql Query Performance How to improve SQL database performance.

It is not possible to check for SQL NULL values with comparison operators. If you use any other operators to work with NULL values, the query result will not include actual NULL value records.

So we will have to use the "IS NULL" and "IS NOT NULL" operators when working with NULL values.


SQL "IS NULL" Operator Example

The following sql statement will fetch only the records with NULL values in the "Description" field?


SELECT * FROM Books
WHERE Description IS NULL;

The above query result is:

BookId BookName Description
102 Sql Commands
104 Sql Query Injection
105 The Power Of Pl Sql

SQL "IS NOT NULL" Operator Example

The following sql statement will fetch only the records with no NULL values in the "Description" field?


SELECT * FROM Books
WHERE Description IS NOT NULL;

The above query result is:

BookId BookName Description
101 Sql Complete Reference It descripes how to write and execute SQL statement into database.
103 Pl Sql Quick Programming How to write and execute SQL programming using pl sql.
106 Sql Data Analysis It explains how to build and manage SQL database
107 Pl Sql Programming SQL and programming by pl sql.
108 Sql Query Performance How to improve SQL database performance.

Search Keys

  • select where null
  • sql not null query
  • where not null sql
  • sql null check
  • sql null comparison
  • sql insert null value
  • sql replace null
  • case null
  • null field
  • sql server isnull example
  • sql server set value to null
  • sql field not null
  • sql query isnull
  • null comparison
  • sql query to get not null values
  • sql compare null values
  • select where null sql server