-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathconditional.html
163 lines (145 loc) · 7.34 KB
/
conditional.html
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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>5.3. Conditional Expressions — Presto 0.123 Documentation</title>
<link rel="stylesheet" href="../_static/presto.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '0.123',
COLLAPSE_INDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<link rel="top" title="Presto 0.123 Documentation" href="../index.html" />
<link rel="up" title="5. Functions and Operators" href="../functions.html" />
<link rel="next" title="5.4. Conversion Functions" href="conversion.html" />
<link rel="prev" title="5.2. Comparison Functions and Operators" href="comparison.html" />
</head>
<body>
<div class="header">
<h1 class="heading"><a href="../index.html">
<span>Presto 0.123 Documentation</span></a></h1>
<h2 class="heading"><span>5.3. Conditional Expressions</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="comparison.html">5.2. Comparison Functions and Operators</a>
</span>
<span class="right">
<a href="conversion.html">5.4. Conversion Functions</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="conditional-expressions">
<h1>5.3. Conditional Expressions</h1>
<div class="section" id="case">
<h2>CASE</h2>
<p>The standard SQL <tt class="docutils literal"><span class="pre">CASE</span></tt> expression has two forms.
The “simple” form searches each <tt class="docutils literal"><span class="pre">value</span></tt> expression from left to right
until it finds one that equals <tt class="docutils literal"><span class="pre">expression</span></tt>:</p>
<div class="highlight-none"><div class="highlight"><pre>CASE expression
WHEN value THEN result
[ WHEN ... ]
[ ELSE result ]
END
</pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">result</span></tt> for the matching <tt class="docutils literal"><span class="pre">value</span></tt> is returned.
If no match is found, the <tt class="docutils literal"><span class="pre">result</span></tt> from the <tt class="docutils literal"><span class="pre">ELSE</span></tt> clause is
returned if it exists, otherwise null is returned. Example:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span>
<span class="k">CASE</span> <span class="n">a</span>
<span class="k">WHEN</span> <span class="mi">1</span> <span class="k">THEN</span> <span class="s1">'one'</span>
<span class="k">WHEN</span> <span class="mi">2</span> <span class="k">THEN</span> <span class="s1">'two'</span>
<span class="k">ELSE</span> <span class="s1">'many'</span>
<span class="k">END</span>
</pre></div>
</div>
<p>The “searched” form evaluates each boolean <tt class="docutils literal"><span class="pre">condition</span></tt> from left
to right until one is true and returns the matching <tt class="docutils literal"><span class="pre">result</span></tt>:</p>
<div class="highlight-none"><div class="highlight"><pre>CASE
WHEN condition THEN result
[ WHEN ... ]
[ ELSE result ]
END
</pre></div>
</div>
<p>If no conditions are true, the <tt class="docutils literal"><span class="pre">result</span></tt> from the <tt class="docutils literal"><span class="pre">ELSE</span></tt> clause is
returned if it exists, otherwise null is returned. Example:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span><span class="p">,</span>
<span class="k">CASE</span>
<span class="k">WHEN</span> <span class="n">a</span> <span class="o">=</span> <span class="mi">1</span> <span class="k">THEN</span> <span class="s1">'aaa'</span>
<span class="k">WHEN</span> <span class="n">b</span> <span class="o">=</span> <span class="mi">2</span> <span class="k">THEN</span> <span class="s1">'bbb'</span>
<span class="k">ELSE</span> <span class="s1">'ccc'</span>
<span class="k">END</span>
</pre></div>
</div>
</div>
<div class="section" id="if">
<h2>IF</h2>
<p>The <tt class="docutils literal"><span class="pre">IF</span></tt> function is actually a language construct
that is equivalent to the following <tt class="docutils literal"><span class="pre">CASE</span></tt> expression:</p>
<blockquote>
<div><div class="highlight-none"><div class="highlight"><pre>CASE
WHEN condition THEN true_value
[ ELSE false_value ]
END
</pre></div>
</div>
</div></blockquote>
<dl class="function">
<dt>
<tt class="descname">if</tt><big>(</big><em>condition</em>, <em>true_value</em><big>)</big></dt>
<dd><p>Evaluates and returns <tt class="docutils literal"><span class="pre">true_value</span></tt> if <tt class="docutils literal"><span class="pre">condition</span></tt> is true,
otherwise null is returned and <tt class="docutils literal"><span class="pre">true_value</span></tt> is not evaluated.</p>
</dd></dl>
<dl class="function">
<dt>
<tt class="descname">if</tt><big>(</big><em>condition</em>, <em>true_value</em>, <em>false_value</em><big>)</big></dt>
<dd><p>Evaluates and returns <tt class="docutils literal"><span class="pre">true_value</span></tt> if <tt class="docutils literal"><span class="pre">condition</span></tt> is true,
otherwise evaluates and returns <tt class="docutils literal"><span class="pre">false_value</span></tt>.</p>
</dd></dl>
</div>
<div class="section" id="coalesce">
<h2>COALESCE</h2>
<dl class="function">
<dt>
<tt class="descname">coalesce</tt><big>(</big><em>value</em><span class="optional">[</span>, <em>...</em><span class="optional">]</span><big>)</big></dt>
<dd><p>Returns the first non-null <tt class="docutils literal"><span class="pre">value</span></tt> in the argument list.
Like a <tt class="docutils literal"><span class="pre">CASE</span></tt> expression, arguments are only evaluated if necessary.</p>
</dd></dl>
</div>
<div class="section" id="nullif">
<h2>NULLIF</h2>
<dl class="function">
<dt>
<tt class="descname">nullif</tt><big>(</big><em>value1</em>, <em>value2</em><big>)</big></dt>
<dd><p>Returns null if <tt class="docutils literal"><span class="pre">value1</span></tt> equals <tt class="docutils literal"><span class="pre">value2</span></tt>, otherwise returns <tt class="docutils literal"><span class="pre">value1</span></tt>.</p>
</dd></dl>
</div>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="comparison.html">5.2. Comparison Functions and Operators</a>
</span>
<span class="right">
<a href="conversion.html">5.4. Conversion Functions</a> »
</span>
</p>
</div>
<div class="footer">
</div>
</body>
</html>