TSQL - What is the difference between ISNULL () AND NULLIF() Function In TSQL

ISNULL( ) function replaces the Null value with placed value. The use of ISNULL ( ) function is very common in different situations such as changing the Null value to some value in Joins, in Select statement etc.

NULLIF ( ) function returns us Null if two arguments passed to functions are equal. NULLIF( ) function works like Case statement. If both arguments are not same then it will return us first argument value.

Let's create small example to see these functions in action

SELECT *INTO   #tmp

FROM   (SELECT N'1'       AS [Id],
               N'Aamir'   AS [FName],
               N'Shahzad' AS [LName]
        UNION ALL
        SELECT N'2'      AS [Id],
               N'Robert' AS [FName],
               N'Ladson' AS [LName]
        UNION ALL
        SELECT N'3'      AS [Id],
               N'John'   AS [FName],
               N'Rivers' AS [LName]
        UNION ALL
        SELECT N'4'    AS [Id],
               N'Mike' AS [FName],
               NULL    AS [LName]
        UNION ALL
        SELECT N'4'    AS [Id],
               N'Tina' AS [FName],
               N'Tina' AS [LName]) t;
SELECT [Id],
       [FName],
       [LName]FROM   #tmp

See the records of #Temp Table by using Select statement.

Fig 1: #Temp table Records

Now we want to replace Null in LName column with "Unknown" if any value is Null. We also want to create new output column in our Select statement with name "ColValueNotEqual". If FName and LName are different then we want to get FName value and If FName and LName are same then we want to get Null for this column. Let's use ISNULL ( ) and NULLIF ( ) functions to get required results.

SELECT [Id],
       [FName],
       ISNULL([LName], 'Unknown') AS LName,
       NULLIF(FName, LName)       AS ColValueNotEqual

FROM   #tmp 

Fig 2: ISNULL() and NULLIF() in TSQL

In Fig 2, We can see that LName Null value is replaced by "Unknown" as we have used ISNULL() function. ColValueNotEqual is returning us FName in case when FName and LName are not equal and returning us Null when FName and LName column's values are equal as we have used NULLIF() function.