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.