Table of Contents
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.
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:
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
student_id | studentname | admissionno | admisssiondate | enrollmentno | date_of_birth | 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.
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