Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
Loading...
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 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
Return one of two values based on a condition. This function evaluates a boolean expression and returns the first value if true, or the second if false.
IF(expr1,expr2,expr3)If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2; otherwise it returns expr3. IF() returns a numeric or string value, depending on the context in which it is used.
Note: There is also an which differs from theIF() function described here.
There is also an , which differs from the IF() function described above.
This page is licensed: GPLv2, originally from
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
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 |
+---------------------------------------+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)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 |
+-------------------+WHEN expr1 = expr2 THEN NULL ELSE expr1 END.This page is licensed: GPLv2, originally from fill_help_tables.sql
NULLIF(expr1,expr2)SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
| NULL |
+-------------+
SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
| 1 |
+-------------+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 NVL2(NULL,1,2);
+----------------+
| NVL2(NULL,1,2) |
+----------------+
| 2 |
+----------------+
SELECT NVL2('x',1,2);
+---------------+
| NVL2('x',1,2) |
+---------------+
| 1 |
+---------------+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 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 +
+-------------------------------------------------------------------------------------+