SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions
All the Functions returns the specified value if the value in the specified column inside these function is NULL. In other words, All these functions are used to check the value is NULL or not, if the values are NULL, then specified value is return.
IFNULL( ):
🡆Purpose: Returns replacement_value if expression is NULL; otherwise, returns expression.
🡆Database Compatibility: Primarily used in MySQL and Google BigQuery.
SELECT IFNULL(UnitsOnOrder, 0) AS OrderQuantity FROM Products;
IS NULL ( ) :
🡆Purpose: Replaces NULL values with a specified replacement value.
🡆Syntax: ISNULL(expression, replacement_value)
🡆Compatibility: Primarily used in SQL Server and MS Access. MySQL also has an ISNULL() function, but it returns 1 if the expression is NULL and 0 otherwise, rather than replacing the NULL value directly.
SELECT ISNULL(UnitsOnOrder, 0) AS OrderQuantity FROM Products;
COALESCE( ) :
🡆Purpose: Returns the first non-NULL expression among a list of expressions. This function is highly versatile as it can handle multiple arguments.
🡆Syntax: COALESCE(expression1, expression2, …, expressionN)
🡆Compatibility: Widely supported across various SQL databases, including SQL Server, MySQL, Oracle, PostgreSQL, and more.
SELECT COALESCE(UnitsOnOrder, UnitsInStock, 0) AS AvailableUnits FROM Products;
NVL( ) :
🡆Purpose: Replaces NULL values with a specified replacement value. Similar in functionality to IFNULL() and ISNULL().
🡆Syntax: NVL(expression, replacement_value)
🡆Compatibility: Primarily used in Oracle and Db2.
SELECT NVL(UnitsOnOrder, 0) AS OrderQuantity FROM Products;
IFNULL(), ISNULL(), and NVL() typically take two arguments (the expression and the replacement value). COALESCE() can take two or more arguments, allowing for a more complex chain of fallback values.