SQL ISNULL Function Explained [Easy Examples]

Overview of SQL ISNULL Function

SQL ISNULL is a function which is used to check or to replace Null value of specified column or expression which is the first parameter of the function, if it is null than the function will return second parameter value as alternative or replaced value, and if first parameter value is not null than function will return the same.

SQL ISNULL is Advanced SQL function which takes two parameters first one is expression or column name and second is value which is return if expression or column contains NULL value

 

What are NULL values and NULL functions in SQL?

Null values the missing data in SQL tables it is like placeholders in the database where data ins not available, NULL value is not a part of any SQL datatype, column having any datatype can have NULL value.

Null functions are required to perform operations on the null values stored in database. We can apply functions on NULL values to check a value is null or not or to perform any operation on it.

 

SQL ISNULL Syntax

ISNULL (check_expression | column_name , value)

• check_expression | column_name : It is specified expression or table column name which is to be checked for NULL or not
• value: The specified value which is to be returned as replace or alternate value, in case the expression is NULL

 

SQL ISNULL Return Type

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the data type of the value. If a literal NULL is provided as check_expression and no value is provided, returns an int.

 

SQL ISNULL function Examples

Simple String Evaluation example

SELECT ISNULL(NULL, 'Hello') AS checkNULL

In above query we used SQL ISNULL function to check given first argument expression is NULL or not, it starts evolution from first argument which is NULL so it checks for second argument that is a string ‘Hello’ so it will return ‘Hello’ as a replacement Value

OUTPUT:

 

SQL ISNULL function example with Integer arguments

SELECT ISNULL(NULL, 100) AS NotNULL

In successive query also we apply SQL ISNULL function to check for NULLABLE value in first parameter which is NULL so it will return second parameter integer value of 100 as result

OUTPUT:

 

SQL ISNULL function example with NULL as Second Argument

SELECT ISNULL('SQL functions', NULL) AS ISNULL

In Previous query SQL ISNULL function is used to test NULL value in first argument, Having NULL value as second argument do not affect result, it will return value of first argument as ‘SQL functions’

OUTPUT:

 

SQL ISNULL function examples with column name as argument

Consider school database with two tables for examples of SQL ISNULL function

Student Table

Result Table

In above School database ,student table have null values in two columns email and city

Use SQL ISNULL function to check city column values for all students, if it is NULL than replace it with ‘Surat’

SELECT student_id, ISNULL(city, 'Surat') As 'student city'
FROM tblstudent;

In above SQL query checks city column value using ISNULL function for NULL, and replace it with Surat.

OUTPUT:

 

SQL ISNULL function with Aggregate function

Aggregate function like AVG, SUM, MIN, MAX can be used with SQL ISNULL function, we can give result of SQL ISNULL as an input of Aggregate functions

For example, to find average obtain marks of students, if obtain marks is NULL than replaced with 35 marks,

SELECT AVG(ISNULL(obtainmark, 35)) AS Avarage
FROM dbo.tblresult

In previous query, first all NULL values of obtain marks column is replace with 35 and then AVG Aggregate function to the resulting set of all not null values

OUTPUT:

 

To find total of all obtained marks and to determine average percentage of students if none of the student have NULL and both column value

SELECT AVG(ISNULL(pecentage, 35)) AS Avarage, SUM(ISNULL(obtainmark, 35)) AS SUM
FROM dbo.tblresult

• On above query SQL ISNULL function is used as nested of AVG and SUM aggregate function
• Before finding average value of percentage, SQL ISNULL function will replace NULL value of percentage with 35, same as if obtained mark of any student is not exist, it will be replaced with 35 mark using SQL ISNULL

OUTPUT:

 

SQL ISNULL function with INNER JOIN, GROUP BY and WHERE clause

To count city wise student records whose are in semester 3 use SQL ISNULL function with group by city and Inner join

SELECT dbo.tblstudent.city, COUNT(ISNULL(dbo.tblstudent.city, ‘Surat’)) AS ‘total Students’
FROM  dbo.tblstudent INNER JOIN dbo.tblresult ON dbo.tblstudent.student_id = dbo.tblresult.student_id
WHERE  (dbo.tblresult.examname = ‘sem3’)
GROUP BY dbo.tblstudent.city

• Two tables student and result ate connected using SQL Inner join
• To make groupwise student record GROUP By city clause applied, and to make count of student records of each city Aggregate Count function is used
• SQL ISNULL function is applied on city column to check null value in the same, and if exist replaced it with city name ‘Surat’

OUTPUT:

 

To display each semester total obtained marks, minimum obtain marks and maximum obtained marks if all students marks are available in result table, if not mark is not available replace null with 30 marks

SELECT dbo.tblresult.examname, SUM(ISNULL(dbo.tblresult.obtainmark, 30)) AS 'Total marks', MIN(ISNULL(dbo.tblresult.obtainmark, 30)) AS 'Minimum marks', MAX(ISNULL(dbo.tblresult.obtainmark, 30)) AS 'Maximum Marks' FROM   dbo.tblresult 
INNER JOIN dbo.tblstudent ON dbo.tblresult.student_id = dbo.tblstudent.student_id 
INNER JOIN dbo.tblsubject ON dbo.tblresult.subjectid = dbo.tblsubject.subjectid
GROUP BY dbo.tblresult.examname

• In above query, SQL inner join is used to join two tables
• To retrieve each semester total, minimum and maximum marks group by clause is used
• Before applying aggregate function SUM, MIN and MAX in obtain marks we need to check for marks values so SQL ISNULL function is applied on obtain mark column

OUTPUT:

 

Comparison SQL ISNULL() V/S SQL Coalesce() Functions

• SQL ISNULL function is one of the T-SQL function where T-SQL is transact-SQL is the proprietary form of SQL used by Microsoft SQL Server, whereas SQL Coalesce function is based on ANSI SQL
• SQL IS NULL function contains only two parameters whereas SQL Coalesce function can have multiple parameters, if we want to used SQL ISNULL function with multiple parameters, we need to used it as Nested SQL ISNULL function

 

Examples of similarity between ISNULL() and Coalesce()

SELECT ISNULL(NULL, 'Hello') AS 'NotNULL Value'
SELECT COALESCE (NULL, 'Hello') AS 'NotNULL Value'

Above both queries show similarities between Coalesce and IsNull function, both queries give same result when we execute it.

OUTPUT:

 

Example of difference between ISNULL() and Coalesce()

SELECT ISNULL(NULL, NULL) AS 'NotNULL Value'
SELECT COALESCE (NULL, NULL) AS 'NotNULL Value'

When we execute above both queries,

• first query with Coalesce function will return error message ‘At least one of the arguments to COALESCE must be a typed NULL’
• Second query with ISNULL function will return NULL as return

OUTPUT:

SELECT COALESCE (NULL, NULL, 10) AS 'NotNULL Value'
SELECT ISNULL(NULL, ISNULL(NULL, 10)) AS 'NotNULL Value'

Above both queries will give same result as 10, but the way to write arguments to evaluate is difference, we can give more arguments with Coalesce function whereas with IsNULL we can give only two arguments.

OUTPUT:

 

Difference between IS NULL operator and ISNULL() function

• SQL ISNULL()function return an alternative value or replace NULL value with second argument value when an expression is NULL. IS NULL operator list all records that have NULL value
• When using SQL Is NULL Operator a Boolean expression used to filter a record or records, In SQL ISNULL function transforms a null value to another value
• IS NULL is used in WHERE clause to evaluate a condition. On the other hand, ISNULL() is a function that examine a column or a value, and return a specific value is the evaluation returns NULL as a result

 

Example of difference between ISNULL() & Is NULL operator

If we want to find student list whose email Id detail is missing or NULL, we can fetch such records using Is NULL operator with Where condition statement in select query

SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, email, city, class_id
FROM  tblstudent
WHERE  (email IS NULL)

In above query we use Is NULL operator to check for null value in emailed column of student table

OUTPUT:

 

Executing same query with ISNULL function

SELECT student_id, studentname, admissionno, admissiondate, enrollmentno, date_of_birth, ISNULL(email, 'abc@gmail.com') AS Email, city, class_id
FROM   tblstudent

In above query, ISNULL function applied on column name email to check for NULL value, and replace it with ‘abc@gmail.com’ email id

OUTPUT:

 

Summary

In this tutorial article, we have first covered introduction of SQL NULL values, NULL function , mainly focus on SQL ISNULL function in this article, SQL ISNULL function is used to test and replace NULL value in database table with another value which is mention as the second argument of function, we have also given practical examples with multiple tables , Where clause, Group By and Order By clause also, after covering practical examples, we have compared SQL ISNULL with Is NULL operator and SQL coalesce function with practical example of differences

 

References

SQL COALESCE NULL function
SQL functions

 

Further Reading

SQL ISNULL