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.

You can also search these topics using isnull in sql server, sql case when null, sql where not null, null value in database, sql isnull example, 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

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
You can also search these topics using select where null, sql not null query, where not null sql, sql null comparison, sql insert null value, sql server isnull example, sql server set value to null, sql field not null, sql query to get not null values, sql compare null values, select where null sql server.