In MariaDB, we can use the CASE operator to compare a list of conditions and return a different result depending on which condition (if any) is matched.
The CASE expression is included in the SQL standard (ISO/IEC 9075), and most major RDBMSs support it.
MariaDB also has the CASE statement, which is slightly different to the CASE operator. This article is about the CASE operator.
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).
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.
In MariaDB, the NULLIF() function returns NULL if both of its arguments are equivalent. Otherwise it returns the first argument.
MySQL has an IFNULL() function that allows us to easily replace NULL values with another value.
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).