Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - NULL Values Involved in Comparison Operations

By: FYIcenter.com

(Continued from previous topic...)

What Happens If NULL Values Are Involved in Comparison Operations?

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

(Continued on next topic...)

  1. What Are NULL Values?
  2. How To Assign NULL Values to Variables or Columns?
  3. What Happens If NULL Values Are Involved in Arithmetic Operations?
  4. What Happens If NULL Values Are Involved in String Operations?
  5. What Happens If NULL Values Are Involved in Datetime Operations?
  6. What Happens If NULL Values Are Involved in Bitwise Operations?
  7. What Happens If NULL Values Are Involved in Comparison Operations?
  8. What Happens If NULL Values Are Involved in Boolean Operations?
  9. How To Test NULL Values Properly?
  10. How To Replace NULL Values in Expressions using ISNULL()?
  11. How To Replace Given Values with NULL using NULLIF()?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...