ISNULL SQL Function | How to use SQL ISNULL Function with Example

SQL Server isnull function In this article, you will learn IsNull SQL Function and How to use the SQL Server (Transact-SQL) IsNull() Function to replace NULL with a specified value in expressions or table records with examples.

Overview: ISNULL() SQL Server function

We can replace NULL values with a specific value using the ISNULL() SQL Server Function. The following syntax for the SQL ISNULL() the function is as follows.

ISNULL(expression, replacement)

The ISNULL SQL Server Function accepts two arguments as follows:
Expression: we specify the expression in which we need to check NULL values.
Replacement: We want to replace the NULL with a specific value, mean replacement is the value to be returned if the expression is NULL. furthermore, The replacement should be convertible to a value of the type of the expression.

Note:
The SQL ISNULL() function returns the replacement if the first parameter expression evaluates to NULL. Before returning a value, SQL Server converts the data type of replacement to the data type of expression if the types of the two arguments are different.
In case the expression is not NULL, the SQL ISNULL() function returns the value of the expression.

Example: ISNULL() SQL Server function

Therefore, first, we define the parameters while designing a table in SQL Server
The Datatypes for a particular column
In SQL Server, Allow NULL or Not Null values.

CREATE TABLE table_name
( 
  column1 datatype [ NULL],
  column2 datatype [NOT NULL ],
  ...
);

CREATE TABLE table_name

(

column1 datatype [ NULL],

column2 datatype [NOT NULL ],

...

);

Therefore, In SQL Server, If we do not provide any value for column allow NULL values, then SQL Server assumes a NULL by the default value.

CREATE TABLE Student
(StudentID     INT IDENTITY(1, 1) NOT NULL, 
 StudentName   VARCHAR(50) NOT NULL,
 StudentBranch   VARCHAR(50) NOT NULL, 
 StudentFees INT NULL
);

CREATE TABLE Student

(StudentID INT IDENTITY(1, 1) NOT NULL,

StudentName VARCHAR(50) NOT NULL,

StudentBranch VARCHAR(50) NOT NULL,

StudentFees INT NULL

);

Furthermore, we need to insert a few records in the Student table. let’s insert the records.

INSERT INTO Student
(StudentName, 
 StudentBranch,
 StudentFees
)
VALUES
('Mohit Thakur', 
 'Science',
 '51000'
);
INSERT INTO Student(StudentName, StudentBranch)
VALUES('David Charles', 'Art');

INSERT INTO Student

(StudentName,

StudentBranch,

StudentFees

)

VALUES

('Mohit Thakur',

'Science',

'51000'

);

INSERT INTO Student(StudentName, StudentBranch)

VALUES('David Charles', 'Art');

Therefore let’s see the records in the Student table, you will see that a NULL value against StudentID 2 because we did not insert any value for this column.

SELECT *
FROM Student

1 2	SELECT * FROM Student

ISNULL SQL SERVER FUNCTION OUTPUT

ISNULL SQL to replace a value in existing column values Example

as you can see that I have created the Student table and insert NULL values in it. therefore, we can use ISNULL SQL to replace existing NULL values with a specific value.
let see the example, we want to return Student Fees 21,000 if it is NULL in the Student table. therefore, the following query, we can use SQL ISNULL function to replace the value.

SELECT StudentID, 
     ISNULL(StudentFees, 21000) StudentFees
  FROM Student;

SELECT StudentID,

ISNULL(StudentFees, 21000) StudentFees

FROM Student;

OUTPUT

Furthermore, Let’s update the NULL value in the Student table using the given below following update statement.

Update Student set StudentFees =52562 where StudentID =2

1	Update Student set StudentFees =52562 where StudentID =2

Therefore, we do not have any NULL value in the Student table now, if we run the query with SQL Server ISNULL, then it returns actual values of the rows. let’s see Student Table

SELECT * FROM Student

1	SELECT * FROM Student

SQL Server ISNULL

ISNULL SQL function in an Argument Example

Therefore, in the example, SQL ISNULL function returns the second argument value because the first argument is NULL:

SELECT ISNULL(NULL, 50) result;

1	SELECT ISNULL(NULL, 50) result;

SQL SELECT output
Furthermore, In the above given following examples, we can see the following.

If the first argument is NULL, then it returns the value of the second argument.
If the first argument is NOT NULL, then it returns the first argument value as output.

ISNULL SQL Server with aggregate functions Example

We can use SQL ISNULL with aggregate functions such as AVG and SUM as well. therefore, Suppose that we want to perform the SUM of StudentFees the present in the Student table. If StudenFees is NULL, it should be replaced with 20000 before adding the Fees.
furthermore, Before we move, update the StudentFees as NULL for StudentID 2 using the following query.

Update Student set StudentFees =NULL where StudentID =2

1	Update Student set StudentFees =NULL where StudentID =2

Therefore, the given following query, we replaced the NULL value with value 20000 first and then performed SUM on it. furthermore, you can visualize it with the following screenshot result.

SELECT SUM(ISNULL(StudentFees, 20000))
FROM Student;

1 2	SELECT SUM(ISNULL(StudentFees, 20000)) FROM Student;

ISNULL SQL SUM Output
Furthermore, as similar, we can also use SQL ISNULL function to replace NULL values and calculate the average value with AVG() function.

SELECT AVG(ISNULL(StudentFees, 20000))
FROM Student;

1 2	SELECT AVG(ISNULL(StudentFees, 20000)) FROM Student;

AVG Function Output

SQL ISNULL() function with character string example

There is the given following example uses the ISNULL() function to return the string 'Welcome' because it is the first argument and not NULL:

SELECT 
    ISNULL('Wellcome', 'Hello') Result;

1 2	SELECT ISNULL('Wellcome', 'Hello') Result;

AVG Function Output

Difference between SQL Server ISNULL with IS NULL

Therefore, I hope you will be confused between SQL Server ISNULL and IS NULL. you can use the IS NULL to identify NULL values in a table. furthermore, for example, if you want to identify records in the Student table with NULL values in the StudentFees column, then you can use the IS NULL in WHERE clause.

SELECT *
FROM Student
WHERE StudentFees IS NULL;

SELECT *

FROM Student

WHERE StudentFees IS NULL;

In the given below the following screenshot, therefore, we cannot use ISNULL SQL Server to find NULL values. furthermore, We use it to replace NULL values with a specific value. then furthermore, we can see the Student having a NULL StudentFees.

Difference b/w SQL Server ISNULL with IS NULL

Note: Applies To
The ISNULL SQL function can be used in the given following versions of SQL Server (Transact-SQL):
SQL Server 2012, SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2008, SQL Server 2008 R2, and SQL Server 2005

Question: Differences Between IsNull() and Coalesce() Functions

The SQL COALESCE() the function which is based on the ANSI SQL standard whereas ISNULL SQL function is a Transact-SQL function
An expression involving ISNULL with non-null parameters is considered to be NOT NULL, furthermore, while expressions involving COALESCE with non-null parameters are considered to be NULL.
The SQL ISNULL() the function contains only two parameters. therefore, The SQL COALESCE() function contains multiple parameters. furthermore, If we use more than two parameters with the ISNULL function then we must use nested ISNULL functions.

Example 1: ISNULL SQL function

SELECT ISNULL(NULL, NULL, 'Welcome')

1	SELECT ISNULL(NULL, NULL, 'Welcome')

COALESCE() function

SELECT COALESCE(NULL, NULL, 'Wellcome')

1	SELECT COALESCE(NULL, NULL, 'Wellcome')

Therefore, The ISNULL() SQL function looks at the first value and the second parameter value is automatically limited to that length furthermore, but the COALESCE() SQL function does not have this restriction.

Example: 2

declare @test varchar(3)  
select isnull(@test, 'XYZ') AS ISNULLResult  
select coalesce(@test, 'XYZ') AS coalesceResult

declare @test varchar(3)

select isnull(@test, 'XYZ') AS ISNULLResult

select coalesce(@test, 'XYZ') AS coalesceResult

In the following given above image output, the test variable has length 2.

The SQL ISNULL() function contains the various types of parameters. Therefore, the SQL COALESCE() function doesn’t limit the number of arguments, furthermore, but they must all be of the same data type.

Example 3: ISNULL() SQL function

DECLARE @a VARCHAR(6)='Welcome',  
@b INT =6  
SELECT ISNULL(@a, @b) AS ISNULLResult

DECLARE @a VARCHAR(6)='Welcome',

@b INT =6

SELECT ISNULL(@a, @b) AS ISNULLResult

The COALESCE() function:

DECLARE @a VARCHAR(6)='Wellcome',  
@b INT =6  
SELECT COALESCE(@a, @b) AS COALESCEResult

DECLARE @a VARCHAR(6)='Wellcome',