SQL NULL ফাংশন

SQL ISNULL()、NVL()、IFNULL() এবং COALESCE() ফাংশন

নিচের "Products" টেবিল দেখুন:

P_Id ProductName UnitPrice UnitsInStock UnitsOnOrder
1 computer 699 25 15
2 printer 365 36 　
3 telephone 280 159 57

যদি "UnitsOnOrder" অপশনাল হোক এবং NULL মান ধারণ করতে পারে:

আমরা নিচের SELECT 语句 ব্যবহার করি:

SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products

In the above example, if the 'UnitsOnOrder' value is NULL, the result is NULL.

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

NVL(), IFNULL(), and COALESCE() functions can also achieve the same result.

Here, we want NULL values to be 0.

Next, if 'UnitsOnOrder' is NULL, it is not good for calculation, so if the value is NULL, ISNULL() returns 0.

SQL Server / MS Access

SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products

Oracle

Oracle does not have an 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 similar to ISNULL(). However, it works a bit differently from Microsoft's ISNULL() function.

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

SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products

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

SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products