SQL - ISNULL Function

In the SQL Server, ISNULL is the most useful function to check the null value against any column. In the beginning of the data table definition, we allow the null value against most of the data columns because NULL values are an important factor in Database design and a NULL value should save space over storing a string of zero length. 
If a value is null and it is used to compare equality or inequality then it would not be considered because null means I don't know the value and before go to the comparison condition, we must use the ISNULL function where it replaces NULL with the specified replacement value. ISNULL is used to handle both the empty string and NULL.

Syntax ISNULL function required two parameters: ISNULL(check_expression, replacement_value) Arguments check_expression: It is the expression to be checked for NULL. check_expression can be of any type. replacement_value: It 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 data 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.

Benefits of Isnull function

Isnull function makes the easy lives of the programmers/ developers and data scientists and they can use them in the select statement, where condition and having clause also as –

1. It is used to check the null value against the data column.
2. Replacement value is used to set the desire value absence of actual data.
3. It returns the same data type as check expression.
4. It is easily usable within the SQL statement.

To understand the IsNull, we can use the following example –

---- Pull data into temp table from derived table SELECT ResellerName, MinPayment, MaxPayment INTO #Reseller FROM ( SELECT 'Ryan Arjun' AS ResellerName, Null as MinPayment, 2500.00 as MaxPayment UNION SELECT 'Kimmy Wang' AS ResellerName, 10.00 as MinPayment, Null as MaxPayment UNION SELECT 'Paddo John' AS ResellerName, 15.50 as MinPayment, 1500.00 as MaxPayment UNION SELECT 'Rosy White' AS ResellerName, 10.00 as MinPayment, Null as MaxPayment )DT ---- Pull actual data SELECT ResellerName, MinPayment, MaxPayment From #Reseller ResellerName MinPayment MaxPayment Kimmy Wang 10.00 NULL Paddo John 15.50 1500.00 Rosy White 10.00 NULL Ryan Arjun NULL 2500.00

In the above example, we can see the Reseller table has some null value.

If we want to pull the all values by replacing the null values across the data then we need to use IsNull feature of SQL and set the default values in place of null as given below –

---- Pull data into temp table from derived table SELECT ResellerName, MinPayment, MaxPayment INTO #Reseller FROM ( SELECT 'Ryan Arjun' AS ResellerName, Null as MinPayment, 2500.00 as MaxPayment UNION SELECT 'Kimmy Wang' AS ResellerName, 10.00 as MinPayment, Null as MaxPayment UNION SELECT 'Paddo John' AS ResellerName, 15.50 as MinPayment, 1500.00 as MaxPayment UNION SELECT 'Rosy White' AS ResellerName, 10.00 as MinPayment, Null as MaxPayment )DT --- Pull data with IsNull SELECT ResellerName, MinPayment=IsNull(MinPayment,5.00), MaxPayment= IsNull(MaxPayment,1200.00) FROM #Reseller; ResellerName MinPayment MaxPayment Kimmy Wang 10.00 1200.00 Paddo John 15.50 1500.00 Rosy White 10.00 1200.00 Ryan Arjun 5.00 2500.00

If we want to pull the resellers having MinPayment value or MaxPayment value is null then we need to use the IsNull in the where condition as given below –

---- Pull data into temp table from derived table SELECT ResellerName, MinPayment, MaxPayment INTO #Reseller FROM ( SELECT 'Ryan Arjun' AS ResellerName, Null as MinPayment, 2500.00 as MaxPayment UNION SELECT 'Kimmy Wang' AS ResellerName, 10.00 as MinPayment, Null as MaxPayment UNION SELECT 'Paddo John' AS ResellerName, 15.50 as MinPayment, 1500.00 as MaxPayment UNION SELECT 'Rosy White' AS ResellerName, 10.00 as MinPayment, Null as MaxPayment )DT ---- Pull actual data with IsNull in Where condition SELECT ResellerName, MinPayment, MaxPayment From #Reseller where (IsNull(MinPayment,5.00)=5.00 OR IsNull(MaxPayment,1200.00)=1200.00) ResellerName MinPayment MaxPayment Kimmy Wang 10.00 NULL Rosy White 10.00 NULL Ryan Arjun NULL 2500.00

Conclusion

We can use ISNULL multiple times in the same SQL statement for different columns, but you must need to write it separately for each column. If we are building a dynamic SQL query, we could theoretically gather a list of columns in the table and generate a query with ISNULL on each one.