SQL ISNULL CASE

user_18383833 0
Viewed 73

Can someone please explain to me in laymen terms why do we put ISNULL? I have an understanding of IS NULL but can't seem to put it together in this CASE context and what would be the impact if you didn't put it.

UpdateTime refers to when a particular row has been updated with new information

Example 1:

CASE WHEN ISNULL(DB1.UpdateTime,'') >= ISNULL(DB2.UpdateTime,'') THEN ISNULL(DB1.UpdateTime,'')
            ELSE DB2.UpdateTime
        END AS UpdateTime
FROM dbo.DB1_Result DB1 INNER JOIN dbo.DB2 DB2
ON DB1.ID = DB2.ID

Example 2:

StartTime = 01/01/2022

WHERE
  (ISNULL(DB1.UpdateTime,'') >= @StartTime

Thank you!

isnull case sql-server
1 Answers

SQL uses trinary logic: true, false, and null. null means the value is unknown, it could be anything.

If you try to compare anything with null you get null, even null. null = anything is null. null <> anything is null. null < anything is null. null > anything is also null.

Null is neither equal nor not equal to itself. null = null is null and null <> null is null. This is why we write x is null not x = null.

If you were to write

case
when DB1.UpdateTime >= DB2.UpdateTime then
  db1.UpdateTime
else
  db2.UpdateTime
end

and if either one is null the DB1.UpdateTime >= DB2.UpdateTime will be null. Case will treat that as false and return DB2.UpdateTime. If either value is null, you always get DB2.UpdateTime even if it's null and DB1.UpdateTime is not.

They don't want that. If DB2.UpdateTime is null, they want to return DB1.UpdateTime. If DB1.UpdateTime is null, they want to return DB2.UpdateTime. So they've used isnull to convert null to something that will compare as less than any time; '' works for that.

Demonstration.

Note: ELSE DB2.UpdateTime may want to be ELSE isnull(DB2.UpdateTime, '') to be consistent with THEN ISNULL(DB1.UpdateTime,'').

Note: isnull is a SQL Server extension. The SQL standard is coalesce.

Note: '' is not a timestamp. It happens to work, but other SQL servers will reject it. 1900-01-01 would be better.

user_146600
Related