is-null-transact-sql.md

title	IS NULL (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/16/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
f1_keywords	NULL_TSQL IS_[NOT]_NULL_TSQL IS_NULL_TSQL NULL [NOT]_TSQL IS IS_TSQL IS NULL IS [NOT] NULL [NOT]
dev_langs	TSQL
helpviewer_keywords	verifying nullability IS NOT NULL clause null values [SQL Server], verifying null values [SQL Server], IS [NOT] NULL IS [NOT] NULL clause testing nullability checking nullability
ms.assetid	cdc45cd8-e9b6-4648-8417-892fbeab15af
caps.latest.revision	37
author	BYHAM
ms.author	rickbyh
manager	jhubbard

IS NULL (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all_md]

Determines whether a specified expression is NULL.

Transact-SQL Syntax Conventions

Syntax

-- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse  
  
expression IS [ NOT ] NULL  

Arguments

expression
Is any valid expression.

NOT
Specifies that the Boolean result be negated. The predicate reverses its return values, returning TRUE if the value is not NULL, and FALSE if the value is NULL.

Result Types

Boolean

Return Code Values

If the value of expression is NULL, IS NULL returns TRUE; otherwise, it returns FALSE.

If the value of expression is NULL, IS NOT NULL returns FALSE; otherwise, it returns TRUE.

Remarks

To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.

Examples

The following example returns the name and the weight for all products for which either the weight is less than 10 pounds or the color is unknown, or NULL.

USE AdventureWorks2012;  
GO  
SELECT Name, Weight, Color  
FROM Production.Product  
WHERE Weight < 10.00 OR Color IS NULL  
ORDER BY Name;  
GO  

Examples: [!INCLUDEssSDWfull] and [!INCLUDEssPDW]

The following example returns the full names of all employees with middle initials.

-- Uses AdventureWorks  
  
SELECT FirstName, LastName, MiddleName  
FROM DIMEmployee  
WHERE MiddleName IS NOT NULL  
ORDER BY LastName DESC;  

See Also

CASE (Transact-SQL)
CREATE PROCEDURE (Transact-SQL)
CREATE TABLE (Transact-SQL)
Data Types (Transact-SQL)
Expressions (Transact-SQL)
INSERT (Transact-SQL)
LIKE (Transact-SQL)
Operators (Transact-SQL)
Logical Operators (Transact-SQL)
SELECT (Transact-SQL)
sp_help (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)