T-SQL Tuesday #154 – SQL Server 2022, IS DISTINCT FROM


 

SELECT CASE WHEN 1 = NULL THEN ‘True’ Else ‘False’ end, CASE WHEN NOT(1 = NULL) THEN ‘True’ Else ‘False’ end

The fact that both of these comparisons return False is confusing at times even to me, and I have written on the whole NULL comparison and negating NULL values things about as many times as I have dealt with it in production code. But using IS DISTINCT FROM, this is no longer the case:

SELECT CASE WHEN 1 IS DISTINCT FROM NULL THEN ‘True’ Else ‘False’ end, CASE WHEN NOT 1 IS DISTINCT FROM NULL THEN ‘True’ Else ‘False’