Enjoy the ongoing promo:
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.
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.
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.
WHEN ISNULL(A, 0)=0
ISNULL(A, 0) > ISNULL(B, 0)
ISNULL(A, 0) - ISNULL(B, 0)
ISNULL(B, 0) - ISNULL(A, 0)
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.
When isNull return 0
Then the result is 0
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.