SQL ISNULL

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

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

SQL ISNULL Function Syntax

The syntax of the ISNULL function is

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

If you observe the above syntax, the ISNULL function returns the result by accepting two arguments:

  • Check_Expression: Please specify the valid expression or column name on which you want to check for NULL values. This will check for the NULL values inside this column.
  • Replacing_Expression: Please specify the valid expression that you want to replace the NULL. This method will replace those 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 SQL Server ISNULL function example, We use the below-shown data.

Customer Table 1

ISNULL Example

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

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

In the below SQL Server ISNULL statement, We used the function to replace the NULL values in the [Lastname] column with ‘tutorialgateway’ string.

We also reassigned the old name ‘LastName’ to that result using the 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 the below statement, We used the function to replace the 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]