The IsNull function in SQL and its application

SQL has a variety of functions, the following will introduce you to the SQL IsNull function, including its syntax, comments, return type, etc., for your reference, I hope you learn SQL can be helpful

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

The expression that will 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
If Check_expression is not NULL, the value of the expression is returned, otherwise Replacement_value is returned.

Example
A. Using ISNULL with AVG
The following example finds the average price for all books and replaces all NULL entries in the Value column of the titles table with the values $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. Using ISNULL
The following example selects the title, type, and price for all books in the titles table. If the price of a title is NULL, then 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 products

In the example above, if there is a "unitsonorder" value of NULL, then 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.

The following, if "UnitsOnOrder" is null, is not conducive to the calculation, so if the value is null then ISNULL () returns 0.

SQL Server/ms Access

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

Oracle

Oracle does not have the ISNULL () function. However, we can use the NVL () function to achieve the same result:

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

Mysql

MySQL also has a function like ISNULL (). But the way it works is a little different from Microsoft's ISNULL () function.

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

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

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

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

The IsNull function in SQL and its application