In your database tables, the values can be NULL for the columns with Allow NULL constraint. As retrieving records from that table, these are displayed as NULL.
The SQL ISNULL function enables you replacing the NULL values with the specified value as retrieving data from the SQL Server database. For example:
SELECT ISNULL (col_name , NULL_replacement_value )
From the above syntax, it is obvious that the ISNULL function takes two arguments; The expression, that can be a table column name of any type.
The other is replacement value.
The next section shows you a few examples of using ISNULL SQL function with different data types.
A simple example of ISNULL function
Before going into the SQL Server database table, let me show a simple example of using the ISNULL function.
The query with replacement value:
SELECT ISNULL(NULL, ‘Alternative Value’) AS “Replacement Value”;
The example of using ISNULL with the character column
We have a sto_employees table that stores employee information. Apart from other information, the phone number is also stored in the table. Most of the entries are NULL for this column in our example table.
The example below uses two queries; one with the simple SELECT statement to retrieve complete table data. The other query fetches the records with ISNULL function and “Not Available” is used as alternative text.
See the queries and results of both queries:
SELECT emp_name, emp_salary, emp_age, phone FROM sto_employees;
SELECT emp_name, emp_salary, emp_age, ISNULL(phone, 'Not Available') As Phone FROM sto_employees;
Note: In MySQL database, the ISNULL function is different. This is used to test whether an expression is NULL.
It returns 1 for NULL and 0 for a NOT NULL value.
SELECT ISNULL(“Some Value”);
This should return 0.
An example of using datetime column with ISNULL
Similarly, you may use the datatime column for displaying some alternate value for the NULL entries. The same table as used in the above example has the join_date column. A few entries are kept NULL for the demo.
Just like the string, the date alternate value is also enclosed in single quotes.
SELECT emp_name, emp_salary, emp_age, join_date FROM sto_employees WHERE id BETWEEN 1 AND 8;
With ISNULL function:
SELECT emp_name, emp_salary, emp_age, ISNULL(join_date,'2018/01/01') AS "Join Date" FROM sto_employees WHERE id BETWEEN 1 AND 8;
The example with the numeric column
For the numeric column demo, I will use the AVG aggregate function in a numeric column. We will calculate the average salary from the employees’ table. In the table, a few records contain NULL entries for demo only:
All records from employees table:
SELECT emp_name, emp_salary, emp_age
AVG without using the ISNULL:
SELECT AVG(emp_salary) AS “AVG Without ISNULL” FROM sto_employees;
After using the ISNULL and providing a value:
SELECT AVG(ISNULL(emp_salary,4500)) AS "AVG With ISNULL" FROM sto_employees;
In that case, all NULL values in the emp_salary column are replaced by 4500.
Difference between ISNULL and IS NULL
Do not mix up the ISNULL function with IS NULL condition. The difference is a space between the two, but “IS NULL” is used with the WHERE clause.
You cannot use ISNULL function in the WHERE clause. The IS NULL in WHERE clause can be used for returning the NULL records for the specified column. For example:
SELECT emp_name, emp_salary, emp_age, join_date FROM sto_employees WHERE join_date IS NULL;