SQL NULL functions are functions that handle NULL values in SQL. These functions are used to manipulate and replace NULL values with a specific value or to check whether a value is NULL or not. In SQL, a NULL value is a value that is undefined or unknown. It is different from an empty string or a zero value.
ISNULL() Function
The ISNULL() function is used to check if a value is NULL or not. If the value is NULL, the function returns a specified value, otherwise it returns the original value. The syntax for the ISNULL() function is as follows:
ISNULL(expression, replacement_value)
In the above syntax, “expression” is the value to be checked and “replacement_value” is the value to be returned if the expression is NULL.
Example
Consider the following table:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | NULL |
| 3 | Bob Smith | 6000 |
| 4 | Alice Johnson | NULL |
Table “employees”
Suppose we want to replace all the NULL values in the “Salary” column with a default value of 5000. The following SQL statement demonstrates the use of the ISNULL() function for this purpose:
SELECT ID, Name, ISNULL(Salary, 5000) AS 'Salary' FROM employees;
The above SQL statement will return the following result:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | 5000 |
| 3 | Bob Smith | 6000 |
| 4 | Alice Johnson | 5000 |
COALESCE() Function
The COALESCE() function is used to return the first non-NULL value from a list of values. The syntax for the COALESCE() function is as follows:
COALESCE(expression1, expression2, ..., expression_n)
In the above syntax, “expression1”, “expression2”, …, “expression_n” are the values to be checked for NULL. The COALESCE() function returns the first non-NULL value in the list.
Example
Consider the following table:
| ID | Name | Salary | Bonus |
|---|---|---|---|
| 1 | John Doe | 5000 | NULL |
| 2 | Jane Doe | NULL | 1000 |
| 3 | Bob Smith | 6000 | NULL |
| 4 | Alice Johnson | NULL | NULL |
Table “employees”
Suppose we want to calculate the total salary of employees, including the bonus. If the bonus is not available, we want to use the salary instead. The following SQL statement demonstrates the use of the COALESCE() function for this purpose:
SELECT ID, Name, COALESCE(Salary, Bonus, 0) AS 'Total Salary' FROM employees;
The above SQL statement will return the following result:
| ID | Name | Total Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | 1000 |
| 3 | Bob Smith | 6000 |
| 4 | Alice Johnson | 0 |
NULLIF() Function
The NULLIF() function is used to compare two values. If the values are equal, the function returns NULL, otherwise it returns the first value. The syntax for the NULLIF() function is as follows:
NULLIF(expression1, expression2)
In the above syntax, “expression1” and “expression2” are the values to be compared. If the values are equal, the function returns NULL, otherwise it returns “expression1”.
Example
Consider the following table:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | 4000 |
| 3 | Bob Smith | 6000 |
Table “employees”
Suppose we want to replace the salary value of “John Doe” with NULL if it is equal to 5000. The following SQL statement demonstrates the use of the NULLIF() function for this purpose:
SELECT ID, Name, NULLIF(Salary, 5000) AS 'Salary' FROM employees;
The above SQL statement will return the following result:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | NULL |
| 2 | Jane Doe | 4000 |
| 3 | Bob Smith | 6000 |
As seen in the above example, the NULLIF function is used to compare “expression1” and “expression2”. If the values are equal, the function returns NULL, otherwise it returns “expression1”. The function is useful in cases where you want to replace a specific value with NULL if it matches a certain condition.
NVL() Function
The NVL() function is used to replace NULL values with a specified value. The syntax for the NVL() function is as follows:
NVL(expression, replacement_value)
In the above syntax, “expression” is the value to be checked and “replacement_value” is the value to be returned if the expression is NULL.
Example
Consider the following table:
| ID | Name | Salary |
|---|---|---|
| 1 | John Doe | 5000 |
| 2 | Jane Doe | NULL |
| 3 | Bob Smith | 6000 |
| 4 | Alice Johnson | NULL |
Table “employees”
Suppose we want to replace all the NULL values in the “Salary” column with a default value of 5000. The following SQL statement demonstrates the use of the NVL() function for this purpose:
SELECT ID, Name, NVL(Salary, 5000) AS 'Salary' FROM employees;
The above SQL statement will return the same result as the ISNULL() function example.
IFNULL() Function
The IFNULL() function is used to replace NULL values with a specified value. The syntax for the IFNULL() function is as follows:
IFNULL(expression, replacement_value)
In the above syntax, “expression” is the value to be checked and “replacement_value” is the value to be returned if the expression is NULL.
Example
Consider the same table “employees” as in the previous examples. Suppose we want to replace all the NULL values in the “Salary” column with a default value of 5000. The following SQL statement demonstrates the use of the IFNULL() function for this purpose:
SELECT ID, Name, IFNULL(Salary, 5000) AS 'Salary' FROM employees;
The above SQL statement will return the same result as the ISNULL() and NVL() function examples.
In conclusion, SQL NULL functions are used to handle NULL values in SQL and provide a way to replace NULL values with a specific value or to check if a value is NULL or not. The ISNULL(), COALESCE(), NVL(), and IFNULL() functions are commonly used to accomplish these tasks.
