In this topic, we described about the IS NULL Function with detailed example.
IS NULL function returns specified value if the expression is NULL otherwise it returns the expression. By using ISNULL function we can replace the NULL values with specified value in result set only. By using ISNULL function we cannot change the values in the table. IS NULL returns numeric or string values based on the scenario. IS NULL function works on SQL SERVER 2008 onwards.
ISNULL (Expression1, Expression2|Value) FROM table ;
- Expression1 – It represents the name of the expression or column name on which we want to test NULL values.
- Expression2 – It represents the name of the expression or column name which we want to insert values on nullable column.
- Value – It represents the value that we need to specify on required column which have the NULL values.
Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.
Scenario – Replacing NULL values in the existing table with specified value in result set.
Requirement – Replacing NULL values in employee_details with specified value in result set. The query was as follows –
SELECT emp_id, emp_name, designation, ISNULL(manager_id,'Employee0')manager_id FROM employee_details;
By executing above query we can get specified value on manager_id column in employee_details table on result set. The output was as follows –
Scenario – If we trying to put specified values on NOT NULL column in existing table by using ISNULL.
Requirement – If we trying to specify on designation NOT NULL column in employee_details with Analyst by using ISNULL. The query was as follows –
SELECT emp_id, emp_name, ISNULL (designation, 'Analyst') designation FROM employee_details;
By executing above query we can get existing designation values instead of getting specified Analyst value because the designation column does not have NULL values. The output was as follows –
Scenario – If we trying to combine multiple columns which have NULL values getting without NULL values in result set on existing table by using ISNULL function.
Requirement – If we trying to add emp_id, emp_name, manager_id, designation columns on employee_details table without getting NULL values by using ISNULL function. The query was as follows –
SELECT emp_name + ISNULL(manager_id, ' ') + designation 'NAME WITH DESIGNATION' FROM employee_details;
By executing above query, we can get output without NULL values in result set from employee_details table. The output was as follows -
|NAME WITH DESIGNATION|
Scenario – Replacing NULL values in the existing table with another specified column value in result set by using ISNULL.
Requirement – Replacing NULL values in employee_details with designation value in result set by using ISNULL. The query was as follows –
SELECT emp_id,emp_name,ISNULL(manager_id, designation) manager_id FROM employee_details;
By executing above query we can replace NULL values on manager_id column in existing employee_details table with designation column values. The output was as follows -