NULL Functions

SQL IFNULL(), ISNULL(), COALESCE(), va NVL() Funktsiyalari

SU

SQL NULL Funktsiyalari

SQL IFNULL(), ISNULL(), COALESCE(), va NVL() Funktsiyalari

Quyidagi "Products" jadvaliga e'tibor bering:

P_IdProductNameUnitPriceUnitsInStockUnitsOnOrder
1Jarlsberg10.451615
2Mascarpone32.5623NULL
3Gorgonzola15.67920

Aytaylik, "UnitsOnOrder" ustuni ixtiyoriy bo'lib, NULL qiymatlarni o'z ichiga olishi mumkin.

Quyidagi SELECT bayonotiga e'tibor bering:

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

Yuqoridagi misolda, agar "UnitsOnOrder" qiymatlaridan biri NULL bo'lsa, natija NULL bo'ladi.

YeChimlar

MySQL

MySQL IFNULL() funktsiyasi, agar ifoda NULL bo'lsa, alternativ qiymatni qaytarish imkonini beradi:

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

Yoki shunday qilib COALESCE() funktsiyasini ham ishlatishimiz mumkin:

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

SQL Server

SQL Server ISNULL() funktsiyasi, agar ifoda NULL bo'lsa, alternativ qiymatni qaytarish imkonini beradi:

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

Yoki shunday qilib COALESCE() funktsiyasini ham ishlatishimiz mumkin:

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

MS Access

MS Access IsNull() funktsiyasi, agar ifoda null qiymat bo'lsa, TRUE (-1) qaytaradi, aks holda FALSE (0) qaytaradi:

SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;

Oracle

Oracle NVL() funktsiyasi ham xuddi shunday natijaga erishadi:

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

Yoki shunday qilib COALESCE() funktsiyasini ham ishlatishimiz mumkin:

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

Ushbu sahifada

Xato haqida xabar berish