In MariaDB, the NVL2() function allows us to replace a value with another value, the new value being determined by whether or not the initial value is null.
It’s similar to the NVL() function, except that NVL2() accepts three arguments instead of two. This allows us to specify a different value to return in the event the first argument is not null.
Some DBMSs have an NVL2() function that allows us to replace a value with another value, the new value being determined by whether or not the initial value is null.
It’s similar to the NVL() function, except that NVL2() accepts exactly three arguments. This allows us to specify a different value to return in the event the first argument is not null.
In contrast, with the NVL() function, some DBMSs accept only two arguments (which makes the function a synonym for the IFNULL() function) while others accept an unlimited number of arguments (which makes it a synonym for the COALESCE() function).
Some DBMSs provide an NVL() function, which can be used when working with potentially NULL values. The function allows us to replace any NULL values with another value.
Some RDBMSs provide an ISNULL() function that can be used when dealing with potentially null values.
MySQL, MariaDB, and Oracle Database each have an ISNULL() function that returns 1 if its argument is null, and 0 if it’s not.
SQL Server also has an ISNULL() function, but it works differently. It works more like how the IFNULL() function works in some other RDBMSs.
Other RDBMSs, such as PostgreSQL and SQLite don’t include an ISNULL() function, but they do support the IS NULL predicate (as do the other RDBMSs).
In PostgreSQL, the COALESCE() operator returns the first non-null value from its list of arguments. If all arguments are null, it returns null.
Some RDBMSs provide an IFNULL() function that can be used when dealing with potentially null values. In particular, MySQL, MariaDB, and SQLite each have an IFNULL() function that replaces any null values with another value.
Other RDBMSs, such as SQL Server, Oracle, and PostgreSQL provide similar functionality via functions of a different name.
Continue readingIn MariaDB, the ISNULL() function checks whether an expression is null or not. If the expression resolves to null, then ISNULL() returns 1, otherwise it returns 0.
In PostgreSQL, the GREATEST() function returns the greatest (or largest) value from a list of any number of expressions.
The GREATEST() function is not included in the SQL standard, but it’s a popular extension that many major RDBMSs support.
Most major RDBMSs support the NULLIF() operator, which returns NULL if both of its arguments are equivalent. If the arguments not equivalent, NULLIF() returns the first argument.
NULLIF() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).
Most major RDBMSs support the COALESCE() operator, which returns the first non-null value from its list of arguments.
COALESCE() is a SQL-standard feature (it’s included in the ISO/IEC 9075 specification).