SQL IsNull function-CodePudding

I saw a sql code using the isNull function in the where clause, similar like below:date>isNull(date1,date2)

Can someone explain what this means?

Many Thanks! Michelle

CodePudding user response：

 declare @date1 datetime = null
 declare @date2 datetime = getdate()

 select isNull(@date1,@date2)

IsNull will return the first one going left to right that is not null

If @date1 is not null, it will return @date1. If @date1 is null, then it will return @date2.

For more details the docs are pretty good:

https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-ver16

CodePudding user response：

To explain the ISNULL function is not necessary because there are lots of documentations about it, just google and read them. One of them is linked in the other answer. Necessary is - at least in my opinion - to point that using such a function is in many cases bad practice.

Why that? There are two big disadvantages of that function:

The function is a non standard function. This means it is provided in some DBMS like for example SQL Server DB, but it is missing in some others like for example Oracle DB. Using non standard SQL when Standard SQL is possible makes no sense to me. Often, people change the DBMS, so each usage of non standard SQL produces effort to rewrite these queries or commands.
The ISNULL function only accepts one value which should be checked and replaced if NULL. This means it must be use nested in case more arguments should be used. This can get very bad readable. What a sad implementation in my point of view.

How to get rid of these unpleasant limitations? Well, we can just use COALESCE. This is SQL-standard, will work on every DBMS and accepts a lot of arguments. So we can just write things like

SELECT COALESCE(column1, column2, column3,...,0) FROM yourtable;

This will take the value of the first column of all columns that we write there which is NOT NULL. If all of those columns are NULL, then zero will be selected in this example. And we will very likely never have to rewrite this when we change the DBMS.

To be fair: There are some edge cases, so it can sometimes be better or even really necessary to use the ISNULL approach. I don't want to create the impression that function is not useful at all. But very often, people just use it because they saw it somewhere and didn't think out of the box.

So whenever there is no important reason why we should or must use ISNULL, I advice to use COALESCE instead.

See for example here a question about the differences if you want to know more: Question on SO