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

"IS NULL" - Testing NULL Values in SQL Server

Q

How To Test NULL Values Properly in SQL Server Transact-SQL?

✍: FYIcenter.com

A

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 tutorial 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

⇒Working with NULL Values in SQL Server Transact-SQL

⇒⇒SQL Server Transact-SQL Tutorials

2017-02-03, 676👍, 0💬

ISNULL() - Replacing NULL Values in Expressions in SQL Server
How To Replace NULL Values in Expressions using ISNULL() in SQL Server Transact-SQL? As you learned from previous tutorials, NULL values presented in expressions will cause the final results to be NULL. Sometimes, you want NULL values to be replaced with some default values, like 0, '', or 'NULL', s... 2017-02-03, 1133👍, 0💬

"IS NULL" - Testing NULL Values in SQL Server
How To Test NULL Values Properly in SQL Server Transact-SQL? 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 sh... 2017-02-03, 677👍, 0💬

NULL Values Involved in Boolean Operations in SQL Server
What Happens If NULL Values Are Involved in Boolean Operations in SQL Server Transact-SQL? If NULL values are involved in Boolean operations, the result will vary depending on the operator type. For AND operator, FALSE takes precedence over NULL. The result can be summarized in a table below: AND TR... 2017-02-03, 673👍, 0💬

NULL Values Involved in Comparison Operations in SQL Server
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 va... 2017-02-03, 647👍, 0💬

All rights in the contents of this web site are reserved by the individual author. fyicenter.com does not guarantee the truthfulness, accuracy, or reliability of any contents.