SQL ISNULL

The SQL ISNULL function validates whether an expression is NULL or not. If it is NULL, then the SQL Server ISNULL function will replace the null value with replacement value. Otherwise, it will return the original value.

For example, while you are selecting the discount price of all products and, what if your result returns few NULL values. To get rid of these situations, we can use ISNULL in SQL Server.

SQL ISNULL Function Syntax

The syntax of the ISNULL function in SQL Server is

SELECT ISNULL ([Check_Expression], [Replace_Expression])
FROM [Source]

If you observe the above syntax, SQL Server ISNULL function accepts two arguments:

  • Check_Expression: Please specify the valid expression or column name on which you want to check for NULL values. ISNULL function will check for the NULL values inside this column.
  • Replacing_Expression: Please specify the valid expression that you want to replace the NULL. ISNULL function will replace the NULL values inside the Check_Expression with this Replacing_Expression.

TIP: If the Check_Expression is NULL, then Replacing_Expression will be returned. Otherwise, Check_Expression will return.

For this ISNULL function example, We use the below-shown data

SQL ISNULL 1

SQL ISNULL function

If you observe the above screenshot, there are few null values in the LastName column, and [AddressLine2] holds only null values. In this ISNULL function example, we are going to replace the Lastname column with ‘tutorialgateway’ and [AdressLine2] column with ‘** Same as AdressLine 1 **’ using ISNULL function

SELECT [CustomerKey]
      ,[FirstName]
      ,ISNULL([LastName], 'tutorialgateway') AS [LastName]
      ,[EmailAddress]
      ,[YearlyIncome]
      ,[EnglishOccupation]
      ,[AddressLine1]
      ,ISNULL([AddressLine2], '** Same as AdressLine 1 **') AS [AddressLine2]
      ,[Phone]
  FROM [SQL Tutorial].[dbo].[TenCustomers]
SQL ISNULL 2

In below statement, We used Sql Server ISNULL function to replace the NULL values in [Lastname] column with ‘tutorialgateway’ string. We also reassigned the old name ‘LastName’ to that result using ALIAS Column. Instead of directly writing the string value as the second argument, you can use SQL Server variables as well.

ISNULL([LastName], 'tutorialgateway') AS [LastName]

In below statement, We used ISNULL function to replace the NULL values in [AdressLine2] column with ‘** Same as AdressLine 1 **’ string. We also reassigned the old name [AddressLine2] to that result using the ALIAS Column.

ISNULL([AddressLine2], '** Same as AdressLine 1 **') AS [AddressLine2]