--- title: "ISNULL (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/06/2017" ms.prod: "sql-non-specified" ms.reviewer: "" ms.suite: "" ms.technology: - "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" f1_keywords: - "ISNULL" - "ISNULL_TSQL" - "IFNULL_TSQL" dev_langs: - "TSQL" helpviewer_keywords: - "replacing null values" - "null values [SQL Server], ISNULL" - "null values [SQL Server], replacement values" - "ISNULL function" ms.assetid: 6f3e5802-864b-4e77-9862-657bb5430b68 caps.latest.revision: 42 author: "BYHAM" ms.author: "rickbyh" manager: "jhubbard" --- # ISNULL (Transact-SQL) [!INCLUDE[tsql-appliesto-ss2008-all_md](../../includes/tsql-appliesto-ss2008-all-md.md)] Replaces NULL with the specified replacement value. ![Topic link icon](../../database-engine/configure-windows/media/topic-link.gif "Topic link icon") [Transact-SQL Syntax Conventions](../../t-sql/language-elements/transact-sql-syntax-conventions-transact-sql.md) ## Syntax ``` -- Syntax for SQL Server, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse ISNULL ( check_expression , replacement_value ) ``` ## Arguments *check_expression* Is the [expression](../../t-sql/language-elements/expressions-transact-sql.md) to be checked for NULL. *check_expression* can be of any type. *replacement_value* Is the expression to be returned if *check_expression* is NULL. *replacement_value* must be of a type that is implicitly convertible to the type of *check_expresssion*. ## Return Types Returns the same type as *check_expression*. If a literal NULL is provided as *check_expression*, returns the datatype of the *replacement_value*. If a literal NULL is provided as *check_expression* and no *replacement_value* is provided, returns an **int**. ## Remarks The value of *check_expression* is returned if it is not NULL; otherwise, *replacement_value* is returned after it is implicitly converted to the type of *check_expression*, if the types are different. *replacement_value* can be truncated if *replacement_value* is longer than *check_expression*. > [!NOTE] > Use [COALESCE (Transact-SQL)](../../t-sql/language-elements/coalesce-transact-sql.md) to return the first non-null value. ## Examples ### A. Using ISNULL with AVG The following example finds the average of the weight of all products. It substitutes the value `50` for all NULL entries in the `Weight` column of the `Product` table. ``` USE AdventureWorks2012; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] `--------------------------` `59.79` `(1 row(s) affected)` ### B. Using ISNULL The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in `AdventureWorks2012`. If the maximum quantity for a particular special offer is NULL, the `MaxQty` shown in the result set is `0.00`. ``` USE AdventureWorks2012; GO SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity' FROM Sales.SpecialOffer; GO ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] | Description | DiscountPct | MinQty | Max Quantity | | --------------- | ------------- | -------- | --------------- | | No Discount | 0.00 | 0 | 0 | | Volume Discount | 0.02 | 11 | 14 | | Volume Discount | 0.05 | 15 | 4 | | Volume Discount | 0.10 | 25 | 0 | | Volume Discount | 0.15 | 41 | 0 | | Volume Discount | 0.20 | 61 | 0 | | Mountain-100 Cl | 0.35 | 0 | 0 | | Sport Helmet Di | 0.10 | 0 | 0 | | Road-650 Overst | 0.30 | 0 | 0 | | Mountain Tire S | 0.50 | 0 | 0 | | Sport Helmet Di | 0.15 | 0 | 0 | | LL Road Frame S | 0.35 | 0 | 0 | | Touring-3000 Pr | 0.15 | 0 | 0 | | Touring-1000 Pr | 0.20 | 0 | 0 | | Half-Price Peda | 0.50 | 0 | 0 | | Mountain-500 Si | 0.40 | 0 | 0 | `(16 row(s) affected)` ### C. Testing for NULL in a WHERE clause Do not use ISNULL to find NULL values. Use IS NULL instead. The following example finds all products that have `NULL` in the weight column. Note the space between `IS` and `NULL`. ``` USE AdventureWorks2012; GO SELECT Name, Weight FROM Production.Product WHERE Weight IS NULL; GO ``` ## Examples: [!INCLUDE[ssSDWfull](../../includes/sssdwfull-md.md)] and [!INCLUDE[ssPDW](../../includes/sspdw-md.md)] ### D. Using ISNULL with AVG The following example finds the average of the weight of all products in a sample table. It substitutes the value `50` for all NULL entries in the `Weight` column of the `Product` table. ``` -- Uses AdventureWorks SELECT AVG(ISNULL(Weight, 50)) FROM dbo.DimProduct; ``` [!INCLUDE[ssResult](../../includes/ssresult-md.md)] ``` -------------------------- 52.88 ``` ### E. Using ISNULL The following example uses ISNULL to test for NULL values in the column `MinPaymentAmount` and display the value `0.00` for those rows. ``` -- Uses AdventureWorks SELECT ResellerName, ISNULL(MinPaymentAmount,0) AS MinimumPayment FROM dbo.DimReseller ORDER BY ResellerName; ``` Here is a partial result set. | ResellerName | MinimumPayment | | ------------------------- | -------------- | | A Bicycle Association | 0.0000 | | A Bike Store | 0.0000 | | A Cycle Shop | 0.0000 | | A Great Bicycle Company | 0.0000 | | A Typical Bike Shop | 200.0000 | | Acceptable Sales & Service | 0.0000 | ### F. Using IS NULL to test for NULL in a WHERE clause The following example finds all products that have `NULL` in the `Weight` column. Note the space between `IS` and `NULL`. ``` -- Uses AdventureWorks SELECT EnglishProductName, Weight FROM dbo.DimProduct WHERE Weight IS NULL; ``` ## See Also [Expressions (Transact-SQL)](../../t-sql/language-elements/expressions-transact-sql.md) [IS NULL (Transact-SQL)](../../t-sql/queries/is-null-transact-sql.md) [System Functions (Transact-SQL)](../../relational-databases/system-functions/system-functions-for-transact-sql.md) [WHERE (Transact-SQL)](../../t-sql/queries/where-transact-sql.md) [COALESCE (Transact-SQL)](../../t-sql/language-elements/coalesce-transact-sql.md)