Sunday, June 5, 2016

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.

No comments:

Post a Comment

Popular Posts