Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Synonym for IFNULL. This Oracle-compatible function returns the first argument if it is not NULL, or the second argument if the first is NULL.
NVL is a synonym for IFNULL.
This page is licensed: CC BY-SA / Gnu FDL
Compare a value against a list of conditions. This Oracle-compatible function returns a corresponding result when a match is found, or a default value otherwise.
DECODE_ORACLE is a synonym for the version of the DECODE function, and is available in all modes.
This page is licensed: CC BY-SA / Gnu FDL
Replace NULL values with a fallback. This function returns the first argument if it's not NULL; otherwise, it returns the specified replacement value.
IFNULL(expr1,expr2)
NVL(expr1,expr2)Return values based on NULL status. This function returns the second argument if the first is not NULL, and the third argument if the first is NULL.
SELECT IFNULL(1,0);
+-------------+
| IFNULL(1,0) |
+-------------+
| 1 |
+-------------+
SELECT IFNULL(NULL,10);
+-----------------+
| IFNULL(NULL,10) |
+-----------------+
| 10 |
+-----------------+
SELECT IFNULL(1/0,10);
+----------------+
| IFNULL(1/0,10) |
+----------------+
| 10.0000 |
+----------------+
SELECT IFNULL(1/0,'yes');
+-------------------+
| IFNULL(1/0,'yes') |
+-------------------+
| yes |
+-------------------+SELECT NVL2(NULL,1,2);
+----------------+
| NVL2(NULL,1,2) |
+----------------+
| 2 |
+----------------+
SELECT NVL2('x',1,2);
+---------------+
| NVL2('x',1,2) |
+---------------+
| 1 |
+---------------+Learn about control flow functions in MariaDB Server. This section details SQL functions like IF, CASE, and NULLIF, which enable conditional logic within your queries and stored routines.
Compare two expressions and return NULL if they are equal. If the expressions differ, the function returns the first expression.
Implement conditional logic in SQL queries. This operator evaluates conditions and returns a specific value when the first true condition is met.
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN
result ...] [ELSE result] END
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...]
[ELSE result] ENDThe first version returns the result for the first value=compare_value comparison that is true. The second version returns the result for the first condition that is true. If there was no matching result value, the result after ELSE is returned, or NULL if there is no ELSE part.
There is also a , which differs from the CASE operator described here.
Only the first matching condition is processed:
The , which differs from the CASE operator described above.
The .
This page is licensed: GPLv2, originally from
SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL |
+-------------+
SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
| 1 |
+-------------+SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;
+------------------------------------------------------------+
| CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END |
+------------------------------------------------------------+
| one |
+------------------------------------------------------------+
SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
+--------------------------------------------+
| CASE WHEN 1>0 THEN 'true' ELSE 'false' END |
+--------------------------------------------+
| true |
+--------------------------------------------+
SELECT CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
+-----------------------------------------------------+
| CASE BINARY 'B' WHEN 'a' THEN 1 WHEN 'b' THEN 2 END |
+-----------------------------------------------------+
| NULL |
+-----------------------------------------------------+SELECT
CASE true
WHEN (1=1) THEN '1=1' -- result is returned
WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' -- condition not processed
ELSE 'else'
END
;
+-------------------------------------------------------------------------------------+
| CASE true WHEN (1=1) THEN '1=1' WHEN (1=1 OR 2=2) THEN '1=1 OR 2=2' ELSE 'else' END |
+-------------------------------------------------------------------------------------+
+ 1=1 +
+-------------------------------------------------------------------------------------+TRUEexpr1 <> 0expr1 <> NULLIF()expr2expr3IF()Note: There is also an IF statement which differs from theIF() function described here.
There is also an IF statement, which differs from the IF() function described above.
This page is licensed: GPLv2, originally from fill_help_tables.sql
IF(expr1,expr2,expr3)SELECT IF(1>2,2,3);
+-------------+
| IF(1>2,2,3) |
+-------------+
| 3 |
+-------------+SELECT IF(1<2,'yes','no');
+--------------------+
| IF(1<2,'yes','no') |
+--------------------+
| yes |
+--------------------+SELECT IF(STRCMP('test','test1'),'no','yes');
+---------------------------------------+
| IF(STRCMP('test','test1'),'no','yes') |
+---------------------------------------+
| no |
+---------------------------------------+