All pages
Powered by GitBook
1 of 8

Control Flow Functions

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.

CASE OPERATOR

Syntax

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] END

Description

The 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 CASE statement, which differs from the CASE operator described here.

Examples

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 |
+-----------------------------------------------------+

Only the first matching condition is processed:

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                                                                                 +
+-------------------------------------------------------------------------------------+

See Also

This page is licensed: GPLv2, originally from fill_help_tables.sql

DECODE_ORACLE

DECODE_ORACLE is a synonym for the Oracle mode version of the DECODE function, and is available in all modes.

This page is licensed: CC BY-SA / Gnu FDL

IF Function

Syntax

IF(expr1,expr2,expr3)

Description

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 IF statement which differs from theIF() function described here.

Examples

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                                    |
+---------------------------------------+

See Also

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

IFNULL

Syntax

IFNULL(expr1,expr2)
NVL(expr1,expr2)

Description

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returnsexpr2. IFNULL() returns a numeric or string value, depending on the context in which it is used.

NVL() is an alias for IFNULL().

Examples

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               |
+-------------------+

See Also

This page is licensed: GPLv2, originally from fill_help_tables.sql

NULLIF

Syntax

NULLIF(expr1,expr2)

Description

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

Examples

SELECT NULLIF(1,1);
+-------------+
| NULLIF(1,1) |
+-------------+
|        NULL |
+-------------+

SELECT NULLIF(1,2);
+-------------+
| NULLIF(1,2) |
+-------------+
|           1 |
+-------------+

See Also

This page is licensed: GPLv2, originally from fill_help_tables.sql

NVL

NVL is a synonym for IFNULL.

This page is licensed: CC BY-SA / Gnu FDL

NVL2

Syntax

NVL2(expr1,expr2,expr3)

Description

The NVL2 function returns a value based on whether a specified expression is NULL or not. If expr1 is not NULL, then NVL2 returns expr2. If expr1 is NULL, then NVL2 returns expr3.

Examples

SELECT NVL2(NULL,1,2);
+----------------+
| NVL2(NULL,1,2) |
+----------------+
|              2 |
+----------------+

SELECT NVL2('x',1,2);
+---------------+
| NVL2('x',1,2) |
+---------------+
|             1 |
+---------------+

See Also

This page is licensed: CC BY-SA / Gnu FDL