In PostgreSQL, the COALESCE() operator returns the first non-null value from its list of arguments. If all arguments are null, it returns null.
Tag: functions
SQL IFNULL() Explained
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 readingHow ISNULL() Works in MariaDB
In 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.
GREATEST() Function in PostgreSQL
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.
SQL NULLIF() Explained
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).
SQL COALESCE() Explained
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).
LEAST() Function in PostgreSQL
In PostgreSQL, the LEAST() function returns the smallest value from a list of any number of expressions.
The LEAST() function is not included in the SQL standard, but it’s a popular extension that many major RDBMSs support.
MariaDB NULLIF() Explained
In MariaDB, the NULLIF() function returns NULL if both of its arguments are equivalent. Otherwise it returns the first argument.
MySQL IFNULL() Explained
MySQL has an IFNULL() function that allows us to easily replace NULL values with another value.
Oracle ISNULL() Equivalent
Most major DBMSs provide us with a function for replacing null values with another value.
But the name of the function tends to differ across DBMSs. For example, SQL Server has an ISNULL() function, while others (such as MySQL, MariaDB, and SQLite) have an IFNULL() function for doing the same thing.
However, to confuse matters, MySQL and MariaDB each have an ISNULL() function that works differently to SQL Server’s function of the same name (MySQL and MariaDB’s implementation only accept a single argument, and return 1 if its null and 0 if it’s not).