Introduction to the use of IsNull functions in SQL _mssql

ISNULL

Replaces NULL with the specified replacement value.

Grammar
ISNULL (Check_expression, Replacement_value)

Parameters
Check_expression

An expression that will be checked for null. Check_expression can be of any type.

Replacement_value

An expression to be returned when check_expression is null. Replacement_value must have the same type as check_expresssion.

return type
Returns the same type as check_expression.

Comments
Returns the value of the expression if check_expression is not NULL, otherwise return replacement_value.

Example
A. Use ISNULL with AVG
The following example finds the average price of all books and replaces all NULL entries in the prices column of the titles table with the value $10.00.

Use pubs
Go
SELECT AVG (ISNULL (Price, $10.00))
From titles
Go

Here is the result set:

--------------------------
14.24

(1 row (s) affected)

B. Use of ISNULL
The following example selects the title, type, and price for all the books in the titles table. If the price of a title is NULL, the price shown in the result set is 0.00.

Use pubs
Go
SELECT SUBSTRING (title, 1,) as title, type as type,
ISNULL (Price, 0.00) as Price
From titles
Go

Here is the result set:

Title Type Price
--------------- ------------ --------------------------
The Busy execut business 19.99
Cooking with Co business 11.95
You Can combat business 2.99
Straight Talk A Business 19.99
Silicon Valley Mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The psychology undecided

SQL ISNULL (), NVL (), Ifnull (), and COALESCE () functions

Take a look at the "Products" table below:

p_id	ProductName	UnitPrice	UnitsInStock	UnitsOnOrder
1	Computer	699	25	15
2	Printer	365	36
3	Telephone	280	159	57

If "UnitsOnOrder" is optional, and can contain NULL values.

We use the following SELECT statement:

SELECT productname,unitprice* (unitsinstock+unitsonorder) from

In the example above, if a "UnitsOnOrder" value is null, the result is null.

Microsoft's ISNULL () function is used to specify how NULL values are handled.

NVL (), ifnull () and coalesce () functions can also achieve the same result.

Here, we want the NULL value to be 0.

Below, if "UnitsOnOrder" is null, it is not conducive to calculation, so ISNULL () returns 0 if the value is null.

SQL Server/ms Access

SELECT productname,unitprice* (Unitsinstock+isnull (unitsonorder,0)) from

Oracle

Oracle has no ISNULL () function. However, we can use the NVL () function to achieve the same result:

SELECT productname,unitprice* (UNITSINSTOCK+NVL (unitsonorder,0)) from

Mysql

MySQL also has functions similar to ISNULL (). But it works a little differently from Microsoft's ISNULL () function.

In MySQL, we can use the ifnull () function, just like this:

SELECT productname,unitprice* (Unitsinstock+ifnull (unitsonorder,0)) from

Or we can use the coalesce () function, just like this:

SELECT productname,unitprice* (Unitsinstock+coalesce (unitsonorder,0)) from