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.
Related Links
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 |
Related Links