1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
|
Control Flow Functions
======================
There are four control flow functions:
* CASE
* IF/ELSE
* IFNULL
* NULLIF
Control flow functions return a value for each row processed, which represents the result of the comparison or condition specified. They can be used in ``SELECT``, ``WHERE``, ``ORDER BY``, and ``GROUP BY`` statements.
CASE
----
There are two basic examples of the ``CASE`` statment:
1. ::
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
In this version, result is returned when value is equal to compare_value. If nothing is matched, the result after ``ELSE`` is returned, or ``NULL`` is returned if there is no ``ELSE`` part.
2. ::
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
In this version, if [condition] is true, result is returned. If nothing is matched, the result after ``ELSE`` is returned, or ``NULL`` is returned if there is no ``ELSE`` part.
When [condition] is for equal comparison (=), this example syntax returns the same result as the first example.
IF/ELSE
---------
This type of control flow function checks IF a condition is satisfied (i.e. the Boolean expression returns TRUE), the IF SQL statement will execute. IF the condition is not satisfied (i.e. the Boolean expression returns FALSE), the ELSE SQL statement is executed.
Syntax for IF/ELSE: ::
IF ( Boolean_expression )
BEGIN
SQL statement
END
ELSE
BEGIN
SQL statement
END
IFNULL
-------
Given IFNULL(arg1,arg2), if arg1 is not NULL, IFNULL() returns arg1; it otherwise returns arg2. IFNULL() returns a numeric or string value, depending on how it's used.
.. code-block:: mysql
SELECT IFNULL(2,0);
-> 2
SELECT IFNULL(NULL,1);
-> 1
SELECT IFNULL(1/0,10);
-> 10
SELECT IFNULL(1/0,'yes');
-> 'yes'
NULLIF
-------
NULLIF(arg1,arg2) returns NULL if arg1 = arg2 is true, otherwise returns arg1.
.. code-block:: mysql
SELECT NULLIF(1,1);
-> NULL
SELECT NULLIF(1,2);
-> 1
|