The best SQL NULL values - IS NULL and IS NOT NULL Tutorial In 2024, In this tutorial you can learn SQL NULL values,NULL values in SQL processing,SQL IS NULL,SQL IS NOT NULL,
NULL values represent missing unknown data.
Column default, the table can be stored NULL values.
This chapter explains the IS NULL and IS NOT NULL operators.
If a column in the table is optional, then we can insert new records or update existing records without adding value to the column. This means that the field will be NULL values are saved.
Handling of NULL values and other values are different.
NULL is used as unknown or not applicable placeholder values.
Note: You can not compare NULL and 0; that they are not equivalent.
Look at the following "Persons" table:
P_Id | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Hansen | Ola | Sandnes | |
2 | Svendson | Tove | Borgvn 23 | Sandnes |
3 | Pettersen | Kari | Stavanger |
If the "Persons" table in the "Address" column is optional. This means that if a column insert a record with no values in the "Address", "Address" column will be saved with a NULL value.
So how do we test for NULL values?
Unable to use a comparison operator to test the NULL value, such as =, <, or <>.
We must use the IS NULL and IS NOT NULL operators.
How do we select only the records with NULL values in the "Address" column in it?
We must use the IS NULL operator:
The result set as follows:
LastName | FirstName | Address |
---|---|---|
Hansen | Ola | |
Pettersen | Kari |
Tip: Always use IS NULL to look for NULL values.
How do we select only the "Address" column record a NULL value without it?
We must use the IS NOT NULL operator:
The result set as follows:
LastName | FirstName | Address |
---|---|---|
Svendson | Tove | Borgvn 23 |
In the next section, we understand the ISNULL (), NVL (), IFNULL () and COALESCE () function.