How to use IsNull in T-SQL

Enjoy the ongoing promo:

Python Intermediate Features Course On Udemy

The IsNull method is a built-in function that takes two parameters. The first parameter is the expression or variable that the IsNull method checks and returns true if the variable contains Null. The second parameter is the replacement value that the IsNull method returns if the expression is Null.

Below is an instance of a table with Null 

 

The student table contains student numbers and student names, and there are NULL in rows 3, 8 and 10.

Those three rows represent the case where there could be NULL in the database table.

Step 1.

To use the Isnull method, let us declare a variable StudentName and set it to Null.

Like this:  declare @StudentName as nvarchar(10) = Null

Then let use the Isnull method to check for the variable value, like this:

 

select Isnull(@StudentName, 'No name') as StudentName

By now you should know the result we are expecting right?

When we run the two lines of code together 

what happened is this:

If the StudentName is not equal to Null it returns the student name otherwise it returns No Name.

Step 2. 

The Isnull method can select all the Nulls in tblStudent1 and replace it with 'No Name'.

The code above replaces three rows 3, 8 and 10 in the student table with No Name instead of Null.

This shows that you can also use the Isnull method to create table.

How to Use "IsNull"  Within CASE to Calculate Data from Tables.

For example, I will shown two tables here as A and B, this could be any table from your database.

CASE

WHEN ISNULL(A, 0)=0

THEN

ISNULL(A, 0)

WHEN

ISNULL(A, 0) > ISNULL(B, 0)

THEN

ISNULL(A, 0) - ISNULL(B, 0)

ELSE

ISNULL(B, 0) - ISNULL(A, 0)

END,

ISNULL(B, 0) AS B,

As shown above the is null can be use to calculate and get result from two or more table using the join.

The join part is not shown, but here we consider two tables A and B tables. 

Below is the analysis of what the code above does.

Case

When isNull return 0

Then the result is 0

Case 2

When A is not null and is greater than B

Then subtract B from A

else is the case when A is not null and is less than B

then subtract A from B.

Note that Isnull is faster than coalesce and it is also better than coalesce where you've got two different types of  data type.