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

Advertisement

 

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.

Function Description
ISNULL() Used to check for NULL value in first argument and replace it with second argument value
IFNULL() Returns the first argument value if it is NULL otherwise returns the second value
COALESCE() Returns the first not NULL value from list of arguments
NVL() used to replace NULL value with the desired value given by user

 

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:

Advertisement

SQL ISNULL Function Explained [Easy Examples]

 

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 Explained [Easy Examples]

 

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:

Advertisement

SQL ISNULL Function Explained [Easy Examples]

 

SQL ISNULL function examples with column name as argument

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

Student Table

student_id studentname admissionno admisssiondate enrollmentno date_of_birth email city class_id
101 reema 10001 02-02-2000 e15200002 02-03-1990 reema@gmail.com 2
102 kriya 10002 04-05-2001 e16200003 04-08-1991 kriya@gmail.com 1
103 meena 10003 06-05-1999 e15200004 02-09-1989 Vadodara 3
104 carlin 2001 04-01-1998 e14200001 04-04-1989 carlin@gmail.com Vapi 1
105 dhiren 2002 02-02-1997 e13400002 02-03-1987 dhiru@gmail.com Surat 2
106 hiren 2003 01-01-1997 e13400001 03-03-1987 hiren@gmail.com 2
107 mahir 10004 06-09-2000 e15200003 07-09-1990 Vapi 3
108 nishi 2004 02-04-2001 e16200001 03-02-1991 nishi@gmail.com Vadodara 1

Result Table

result_id student_id examname examdate subjectid obtainmark totalmarks percentage grade status
3001 101 sem1 07-08-2001 1 80 100 80 A+ pass
3002 101 sem1 08-08-2001 2 76 100 76 A+ pass
3003 102 sem3 05-05-2000 3 67 100 67 A pass
3004 102 sem3 06-05-2000 4 89 100 89 A+ pass
3005 102 sem3 07-05-2000 5 90 100 90 A+ pass
3006 103 sem5 08-09-1998 6 55 100 55 B pass
3007 105 sem3 09-09-2001 2 78 100 78 A+ pass

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.

Advertisement

OUTPUT:

SQL ISNULL Function Explained [Easy Examples]

 

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:

SQL ISNULL Function Explained [Easy Examples]

 

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

Advertisement
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 Explained [Easy Examples]

 

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:

SQL ISNULL Function Explained [Easy Examples]

 

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:

SQL ISNULL Function Explained [Easy Examples]

 

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.

Advertisement

OUTPUT:

SQL ISNULL Function Explained [Easy Examples]

 

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:

SQL ISNULL Function Explained [Easy Examples]

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:

SQL ISNULL Function Explained [Easy Examples]

 

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

Advertisement
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:

SQL ISNULL Function Explained [Easy Examples]

 

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:

SQL ISNULL Function Explained [Easy Examples]

 

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

 

Categories SQL

Didn't find what you were looking for? Perform a quick search across GoLinuxCloud

If my articles on GoLinuxCloud has helped you, kindly consider buying me a coffee as a token of appreciation.

Buy GoLinuxCloud a Coffee

For any other feedbacks or questions you can either use the comments section or contact me form.

Thank You for your support!!

Leave a Comment

X