What Happens If NULL Values Are Involved in Comparison Operations in SQL Server Transact-SQL?

If NULL values are involved in comparison operations, the result will be Boolean NULL values. This behavior is very interesting because you would expect a comparison operation returns only one of the two values: TRUE and FALSE. But SQL Server may return you a third value: NULL.

The tutorial script below proves that "1>NULL" is not returning TRUE or FALSE. It is returning a third value, NULL:

IF 1>NULL PRINT '1>NULL is returning TRUE'
ELSE PRINT '1>NULL is not returning TRUE'
GO
1>NULL is not returning TRUE

IF NOT 1>NULL PRINT '1>NULL is returning FALSE'
ELSE PRINT '1>NULL is not returning FALSE'
GO
1>NULL is not returning FALSE

Another test proves that "'FYI'=NULL" is not returning TRUE or FALSE. It is returning a third value, NULL:

IF 'FYI'=NULL PRINT '''FYI''=NULL returns TRUE'
ELSE PRINT '''FYI''=NULL does not return TRUE'
GO
'FYI'=NULL does not return TRUE

IF NOT 'FYI'=NULL PRINT '''FYI''=NULL returns FALSE'
ELSE PRINT '''FYI''=NULL does not return FALSE'
GO
'FYI'=NULL does not return FALSE

⇒ NULL Values Involved in Boolean Operations in SQL Server

⇐ NULL Values Involved in Bitwise Operations in SQL Server

⇑ Working with NULL Values in SQL Server Transact-SQL

⇑⇑ SQL Server Transact-SQL Tutorials