Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "IS NULL" - Testing NULL Values

By: FYIcenter.com

(Continued from previous topic...)

How To Test NULL Values Properly?

From previous tutorials, you learned that comparing expressions with NULL will always result NULL. So you can not use comparison operators to test if an expression represents a NULL value or not.

To test expressions for NULL values, you should use one of the following special operators:

expression IS NULL 
-- Returns TRUE if the expression is a NULL value

expression IS NOT NULL
-- Returns TRUE if the expression is not a NULL value

The turorial script below shows you how to use IS NULL and IS NOT NULL:

IF 0 IS NULL PRINT 'TRUE'
ELSE PRINT 'FALSE';
GO
FALSE

IF NULL IS NULL PRINT 'TRUE'
ELSE PRINT 'FALSE';
GO
TRUE

IF 1+NULL IS NULL PRINT 'TRUE'
ELSE PRINT 'FALSE';
GO
TRUE

IF 'NULL' IS NOT NULL PRINT 'TRUE'
ELSE PRINT 'FALSE';
GO
TRUE

(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...