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.

FunctionDescription
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

Advertisement

OUTPUT:

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:

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

Advertisement

Student Table

student_idstudentnameadmissionnoadmisssiondateenrollmentnodate_of_birthemailcityclass_id
101reema1000102-02-2000e1520000202-03-1990reema@gmail.com2
102kriya1000204-05-2001e1620000304-08-1991kriya@gmail.com1
103meena1000306-05-1999e1520000402-09-1989Vadodara3
104carlin200104-01-1998e1420000104-04-1989carlin@gmail.comVapi1
105dhiren200202-02-1997e1340000202-03-1987dhiru@gmail.comSurat2
106hiren200301-01-1997e1340000103-03-1987hiren@gmail.com2
107mahir1000406-09-2000e1520000307-09-1990Vapi3
108nishi200402-04-2001e1620000103-02-1991nishi@gmail.comVadodara1

Result Table

result_idstudent_idexamnameexamdatesubjectidobtainmarktotalmarkspercentagegradestatus
3001101sem107-08-200118010080A+pass
3002101sem108-08-200127610076A+pass
3003102sem305-05-200036710067Apass
3004102sem306-05-200048910089A+pass
3005102sem307-05-200059010090A+pass
3006103sem508-09-199865510055Bpass
3007105sem309-09-200127810078A+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.

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

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.

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

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

 

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