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.
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 –
- It is used to check the null value against the data column.
- Replacement value is used to set the desire value absence of actual data.
- It returns the same data type as check expression.
- 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
|
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;
|
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)
|
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