How do you use the ISNULL function to replace NULL values with a specified value?
CarolTh
Posted by CarolTh
Last Updated: July 28, 2024
ISNULL NULL
The ISNULL function is used in SQL to replace NULL values with a specified value. The syntax for the ISNULL function is as follows: 
ISNULL(expression, replacement_value)
- expression: This is the value or column that you want to check for NULL. - replacement_value: This is the value that will be returned if the expression is indeed NULL. Here’s an example of how to use the ISNULL function in a SQL query:
Example 1: Basic Usage
Suppose you have a table called Employees with a column Bonus which may contain NULL values. You want to retrieve the bonus for employees, replacing any NULL values with 0. 
SELECT 
    EmployeeID,
    EmployeeName,
    ISNULL(Bonus, 0) AS Bonus
FROM 
    Employees;
In this query, if the Bonus is NULL for any employee, it will be replaced with 0 in the result set.
Example 2: Using in a WHERE Clause
You can also use ISNULL in conditions. For instance, if you want to select all employees who either have a bonus value or want to treat those with NULL bonuses as having a bonus of 0: 
SELECT 
    EmployeeID,
    EmployeeName,
    ISNULL(Bonus, 0) AS Bonus
FROM 
    Employees
WHERE 
    ISNULL(Bonus, 0) > 1000;
This will return employees whose bonuses are greater than 1000, treating NULL bonuses as 0.
Important Notes:
- The ISNULL function is specific to SQL Server. Other database systems might have similar functions like COALESCE() (which can take multiple expressions) or NVL() (specific to Oracle). - When using ISNULL, be mindful of the data types involved, as the replacement value must be compatible with the data type of the expression. This is how you can effectively use the ISNULL function to manage NULL values in SQL queries.
Default Image