SQL NULL Value
This chapter explains the is null and is not NULL operators.
A NULL value is an unknown data that is missing. By default, the columns of the table can hold NULL values.
If a column in the table is optional, we can insert a new record or update an existing record without adding a value to the column. This means that the field will be saved with a NULL value.
NULL values are handled differently than other values.
NULL is used as a placeholder for unknown or non-applicable values.
Note: Cannot compare NULL and 0; they are not equivalent.
NULL Value handling for SQL
Take a look at the "Persons" table below:
Id |
LastName |
FirstName |
Address |
| City
1 |
Adams |
John |
|
London |
2 |
Bush |
George |
Fifth Avenue |
New York |
3 |
Carter |
Thomas |
|
Beijing |
If the "Address" column in the "Persons" table is optional. This means that if you insert a record with no value in the Address column, the address column is saved with a NULL value.
So how do we test for NULL values?
You cannot use a comparison operator to test for NULL values, such as =, <, or <>.
We must use the is null and is not NULL operator.
SQL is NULL
How do we just pick a record with a NULL value in the "Address" column?
We must use the IS NULL operator :
SELECT from WHEREisNULL
Result set:
LastName |
FirstName |
Address |
Adams |
John |
|
Carter |
Thomas |
|
Tip: Always use is NULL to find null values.
SQL is not NULL
How do we choose a record that does not have a NULL value in the Address column?
We must use the is not NULL operator:
SELECT from WHERE is not NULL
Result set:
LastName |
FirstName |
Address |
Bush |
George |
Fifth Avenue |
Null deserves special handling
SQL ISNULL (), NVL (), Ifnull (), and COALESCE () functions
Take a look at the "Products" table below:
p_id |
ProductName |
UnitPrice |
UnitsInStock |
UnitsOnOrder |
1 |
Computer |
699 |
25 |
15 |
2 |
Printer |
365 |
36 |
|
3 |
Telephone |
280 |
159 |
57 |
If "UnitsOnOrder" is optional, and can contain NULL values.
We use the following SELECT statement:
SELECT Productname,unitprice*(UnitsInStock+ from Products
In the example above, if there is a "unitsonorder" value of NULL, then the result is null.
Microsoft's ISNULL () function is used to specify how NULL values are handled.
NVL (), Ifnull (), and coalesce () functions can also achieve the same result.
Here, we want the NULL value to be 0.
The following, if "UnitsOnOrder" is null, is not conducive to the calculation, so if the value is null then ISNULL () returns 0.
SQL Server/ms Access
SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0)) from products
Oracle
Oracle does not have the ISNULL () function. However, we can use the NVL () function to achieve the same result:
SELECT productname,unitprice* (UNITSINSTOCK+NVL (unitsonorder,0)) from products
Mysql
MySQL also has a function like ISNULL (). But the way it works is a little different from Microsoft's ISNULL () function.
In MySQL, we can use the ifnull () function, just like this:
SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0)) from products
Or we can use the coalesce () function, just like this:
SELECT productname,unitprice* (Unitsinstock+coalesce (unitsonorder,0)) from products
SQL NULL value "from the Press"