-
Notifications
You must be signed in to change notification settings - Fork 16
/
Copy pathselect.html
793 lines (770 loc) · 61.1 KB
/
select.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
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
<!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>8.21. SELECT — Presto 0.159 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.159',
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="index" title="Index" href="../genindex.html" />
<link rel="search" title="Search" href="../search.html" />
<link rel="top" title="Presto 0.159 Documentation" href="../index.html" />
<link rel="up" title="8. SQL Statement Syntax" href="../sql.html" />
<link rel="next" title="8.22. SET SESSION" href="set-session.html" />
<link rel="prev" title="8.20. ROLLBACK" href="rollback.html" />
</head>
<body role="document">
<div class="header">
<h1 class="heading"><a href="../index.html">
<span>Presto 0.159 Documentation</span></a></h1>
<h2 class="heading"><span>8.21. SELECT</span></h2>
</div>
<div class="topnav">
<p class="nav">
<span class="left">
« <a href="rollback.html">8.20. ROLLBACK</a>
</span>
<span class="right">
<a href="set-session.html">8.22. SET SESSION</a> »
</span>
</p>
</div>
<div class="content">
<div class="section" id="select">
<h1>8.21. SELECT</h1>
<div class="section" id="synopsis">
<h2>Synopsis</h2>
<div class="highlight-none"><div class="highlight"><pre><span></span>[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expr [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count | ALL ] ]
</pre></div>
</div>
<p>where <code class="docutils literal"><span class="pre">from_item</span></code> is one of</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>table_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span>from_item join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
</pre></div>
</div>
<p>and <code class="docutils literal"><span class="pre">join_type</span></code> is one of</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>[ INNER ] JOIN
LEFT [ OUTER ] JOIN
RIGHT [ OUTER ] JOIN
FULL [ OUTER ] JOIN
CROSS JOIN
</pre></div>
</div>
<p>and <code class="docutils literal"><span class="pre">grouping_element</span></code> is one of</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>()
expression
GROUPING SETS ( ( column [, ...] ) [, ...] )
CUBE ( column [, ...] )
ROLLUP ( column [, ...] )
</pre></div>
</div>
</div>
<div class="section" id="description">
<h2>Description</h2>
<p>Retrieve rows from zero or more tables.</p>
</div>
<div class="section" id="with-clause">
<h2>WITH Clause</h2>
<p>The <code class="docutils literal"><span class="pre">WITH</span></code> clause defines named relations for use within a query.
It allows flattening nested queries or simplifying subqueries.
For example, the following queries are equivalent:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span>
<span class="k">FROM</span> <span class="p">(</span>
<span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">MAX</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">t</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">x</span><span class="p">;</span>
<span class="k">WITH</span> <span class="n">x</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">MAX</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">t</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">x</span><span class="p">;</span>
</pre></div>
</div>
<p>This also works with multiple subqueries:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">WITH</span>
<span class="n">t1</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">MAX</span><span class="p">(</span><span class="n">b</span><span class="p">)</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">x</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span><span class="p">),</span>
<span class="n">t2</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span><span class="p">,</span> <span class="k">AVG</span><span class="p">(</span><span class="n">d</span><span class="p">)</span> <span class="k">AS</span> <span class="n">d</span> <span class="k">FROM</span> <span class="n">y</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">a</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="n">t1</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> <span class="n">t2</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">t1</span>
<span class="k">JOIN</span> <span class="n">t2</span> <span class="k">ON</span> <span class="n">t1</span><span class="p">.</span><span class="n">a</span> <span class="o">=</span> <span class="n">t2</span><span class="p">.</span><span class="n">a</span><span class="p">;</span>
</pre></div>
</div>
<p>Additionally, the relations within a <code class="docutils literal"><span class="pre">WITH</span></code> clause can chain:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">WITH</span>
<span class="n">x</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span> <span class="k">FROM</span> <span class="n">t</span><span class="p">),</span>
<span class="n">y</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">a</span> <span class="k">AS</span> <span class="n">b</span> <span class="k">FROM</span> <span class="n">x</span><span class="p">),</span>
<span class="n">z</span> <span class="k">AS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">b</span> <span class="k">AS</span> <span class="k">c</span> <span class="k">FROM</span> <span class="n">y</span><span class="p">)</span>
<span class="k">SELECT</span> <span class="k">c</span> <span class="k">FROM</span> <span class="n">z</span><span class="p">;</span>
</pre></div>
</div>
</div>
<div class="section" id="group-by-clause">
<h2>GROUP BY Clause</h2>
<p>The <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause divides the output of a <code class="docutils literal"><span class="pre">SELECT</span></code> statement into
groups of rows containing matching values. A simple <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause may
contain any expression composed of input columns or it may be an ordinal
number selecting an output column by position (starting at one).</p>
<p>The following queries are equivalent. They both group the output by
the <code class="docutils literal"><span class="pre">nationkey</span></code> input column with the first query using the ordinal
position of the output column and the second query using the input
column name:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="n">nationkey</span> <span class="k">FROM</span> <span class="n">customer</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="mi">2</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="n">nationkey</span> <span class="k">FROM</span> <span class="n">customer</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">nationkey</span><span class="p">;</span>
</pre></div>
</div>
<p><code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clauses can group output by input column names not appearing in
the output of a select statement. For example, the following query generates
row counts for the <code class="docutils literal"><span class="pre">customer</span></code> table using the input column <code class="docutils literal"><span class="pre">mktsegment</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">customer</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">mktsegment</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0
-------
29968
30142
30189
29949
29752
(5 rows)
</pre></div>
</div>
<p>When a <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause is used in a <code class="docutils literal"><span class="pre">SELECT</span></code> statement all output
expressions must be either aggregate functions or columns present in
the <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause.</p>
<p id="complex-grouping-operations"><strong>Complex Grouping Operations</strong></p>
<p>Presto also supports complex aggregations using the <code class="docutils literal"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code>, <code class="docutils literal"><span class="pre">CUBE</span></code>
and <code class="docutils literal"><span class="pre">ROLLUP</span></code> syntax. This syntax allows users to perform analysis that requires
aggregation on multiple sets of columns in a single query. Complex grouping
operations do not support grouping on expressions composed of input columns.
Only column names or ordinals are allowed.</p>
<p>Complex grouping operations are often equivalent to a <code class="docutils literal"><span class="pre">UNION</span> <span class="pre">ALL</span></code> of simple
<code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> expressions, as shown in the following examples. This equivalence
does not apply, however, when the source of data for the aggregation
is non-deterministic.</p>
<p><strong>GROUPING SETS</strong></p>
<p>Grouping sets allow users to specify multiple lists of columns to group on.
The columns not part of a given sublist of grouping columns are set to <code class="docutils literal"><span class="pre">NULL</span></code>.</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">shipping</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> origin_state | origin_zip | destination_state | destination_zip | package_weight
--------------+------------+-------------------+-----------------+----------------
California | 94131 | New Jersey | 8648 | 13
California | 94131 | New Jersey | 8540 | 42
New Jersey | 7081 | Connecticut | 6708 | 225
California | 90210 | Connecticut | 6927 | 1337
California | 94131 | Colorado | 80302 | 5
New York | 10002 | New Jersey | 8540 | 3
(6 rows)
</pre></div>
</div>
<p><code class="docutils literal"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code> semantics are demonstrated by this example query:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">destination_state</span><span class="p">));</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> origin_state | origin_zip | destination_state | _col0
--------------+------------+-------------------+-------
New Jersey | NULL | NULL | 225
California | NULL | NULL | 1397
New York | NULL | NULL | 3
California | 90210 | NULL | 1337
California | 94131 | NULL | 60
New Jersey | 7081 | NULL | 225
New York | 10002 | NULL | 3
NULL | NULL | Colorado | 5
NULL | NULL | New Jersey | 58
NULL | NULL | Connecticut | 1562
(10 rows)
</pre></div>
</div>
<p>The preceding query may be considered logically equivalent to a <code class="docutils literal"><span class="pre">UNION</span> <span class="pre">ALL</span></code> of
multiple <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> queries:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">origin_state</span>
<span class="k">UNION</span> <span class="k">ALL</span>
<span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span>
<span class="k">UNION</span> <span class="k">ALL</span>
<span class="k">SELECT</span> <span class="k">NULL</span><span class="p">,</span> <span class="k">NULL</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span> <span class="k">GROUP</span> <span class="k">BY</span> <span class="n">destination_state</span><span class="p">;</span>
</pre></div>
</div>
<p>However, the query with the complex grouping syntax (<code class="docutils literal"><span class="pre">GROUPING</span> <span class="pre">SETS</span></code>, <code class="docutils literal"><span class="pre">CUBE</span></code>
or <code class="docutils literal"><span class="pre">ROLLUP</span></code>) will only read from the underlying data source once, while the
query with the <code class="docutils literal"><span class="pre">UNION</span> <span class="pre">ALL</span></code> reads the underlying data three times. This is why
queries with a <code class="docutils literal"><span class="pre">UNION</span> <span class="pre">ALL</span></code> may produce inconsistent results when the data
source is not deterministic.</p>
<p><strong>CUBE</strong></p>
<p>The <code class="docutils literal"><span class="pre">CUBE</span></code> operator generates all possible grouping sets (i.e. a power set)
for a given set of columns. For example, the query:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">CUBE</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">);</span>
</pre></div>
</div>
<p>is equivalent to:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">destination_state</span><span class="p">),</span>
<span class="p">());</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> origin_state | destination_state | _col0
--------------+-------------------+-------
California | New Jersey | 55
California | Colorado | 5
New York | New Jersey | 3
New Jersey | Connecticut | 225
California | Connecticut | 1337
California | NULL | 1397
New York | NULL | 3
New Jersey | NULL | 225
NULL | New Jersey | 58
NULL | Connecticut | 1562
NULL | Colorado | 5
NULL | NULL | 1625
(12 rows)
</pre></div>
</div>
<p><strong>ROLLUP</strong></p>
<p>The <code class="docutils literal"><span class="pre">ROLLUP</span></code> operator generates all possible subtotals for a given set of
columns. For example, the query:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> origin_state | origin_zip | _col2
--------------+------------+-------
California | 94131 | 60
California | 90210 | 1337
New Jersey | 7081 | 225
New York | 10002 | 3
California | NULL | 1397
New York | NULL | 3
New Jersey | NULL | 225
NULL | NULL | 1625
(8 rows)
</pre></div>
</div>
<p>is equivalent to:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">),</span> <span class="p">());</span>
</pre></div>
</div>
<p><strong>Combining multiple grouping expressions</strong></p>
<p>Multiple grouping expressions in the same query are interpreted as having
cross-product semantics. For example, the following query:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">)),</span>
<span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<p>which can be rewritten as:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span>
<span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">)),</span>
<span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">((</span><span class="n">origin_zip</span><span class="p">),</span> <span class="p">());</span>
</pre></div>
</div>
<p>is logically equivalent to:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">));</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> origin_state | destination_state | origin_zip | _col3
--------------+-------------------+------------+-------
New York | New Jersey | 10002 | 3
California | New Jersey | 94131 | 55
New Jersey | Connecticut | 7081 | 225
California | Connecticut | 90210 | 1337
California | Colorado | 94131 | 5
New York | New Jersey | NULL | 3
New Jersey | Connecticut | NULL | 225
California | Colorado | NULL | 5
California | Connecticut | NULL | 1337
California | New Jersey | NULL | 55
(10 rows)
</pre></div>
</div>
<p>The <code class="docutils literal"><span class="pre">ALL</span></code> and <code class="docutils literal"><span class="pre">DISTINCT</span></code> quantifiers determine whether duplicate grouping
sets each produce distinct output rows. This is particularly useful when
multiple complex grouping sets are combined in the same query. For example, the
following query:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">ALL</span>
<span class="k">CUBE</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<p>is equivalent to:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">destination_state</span><span class="p">),</span>
<span class="p">());</span>
</pre></div>
</div>
<p>However, if the query uses the <code class="docutils literal"><span class="pre">DISTINCT</span></code> quantifier for the <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">DISTINCT</span>
<span class="k">CUBE</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="k">ROLLUP</span> <span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">);</span>
</pre></div>
</div>
<p>only unique grouping sets are generated:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">,</span> <span class="k">sum</span><span class="p">(</span><span class="n">package_weight</span><span class="p">)</span>
<span class="k">FROM</span> <span class="n">shipping</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="k">GROUPING</span> <span class="k">SETS</span> <span class="p">(</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">origin_zip</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">,</span> <span class="n">destination_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">origin_state</span><span class="p">),</span>
<span class="p">(</span><span class="n">destination_state</span><span class="p">),</span>
<span class="p">());</span>
</pre></div>
</div>
<p>The default set quantifier is <code class="docutils literal"><span class="pre">ALL</span></code>.</p>
</div>
<div class="section" id="having-clause">
<h2>HAVING Clause</h2>
<p>The <code class="docutils literal"><span class="pre">HAVING</span></code> clause is used in conjunction with aggregate functions and
the <code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> clause to control which groups are selected. A <code class="docutils literal"><span class="pre">HAVING</span></code>
clause eliminates groups that do not satisfy the given conditions.
<code class="docutils literal"><span class="pre">HAVING</span></code> filters groups after groups and aggregates are computed.</p>
<p>The following example queries the <code class="docutils literal"><span class="pre">customer</span></code> table and selects groups
with an account balance greater than the specified value:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="o">*</span><span class="p">),</span> <span class="n">mktsegment</span><span class="p">,</span> <span class="n">nationkey</span><span class="p">,</span>
<span class="k">CAST</span><span class="p">(</span><span class="k">sum</span><span class="p">(</span><span class="n">acctbal</span><span class="p">)</span> <span class="k">AS</span> <span class="nb">bigint</span><span class="p">)</span> <span class="k">AS</span> <span class="n">totalbal</span>
<span class="k">FROM</span> <span class="n">customer</span>
<span class="k">GROUP</span> <span class="k">BY</span> <span class="n">mktsegment</span><span class="p">,</span> <span class="n">nationkey</span>
<span class="k">HAVING</span> <span class="k">sum</span><span class="p">(</span><span class="n">acctbal</span><span class="p">)</span> <span class="o">></span> <span class="mi">5700000</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="n">totalbal</span> <span class="k">DESC</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0 | mktsegment | nationkey | totalbal
-------+------------+-----------+----------
1272 | AUTOMOBILE | 19 | 5856939
1253 | FURNITURE | 14 | 5794887
1248 | FURNITURE | 9 | 5784628
1243 | FURNITURE | 12 | 5757371
1231 | HOUSEHOLD | 3 | 5753216
1251 | MACHINERY | 2 | 5719140
1247 | FURNITURE | 8 | 5701952
(7 rows)
</pre></div>
</div>
</div>
<div class="section" id="union-intersect-except-clause">
<h2>UNION | INTERSECT | EXCEPT Clause</h2>
<p><code class="docutils literal"><span class="pre">UNION</span></code> <code class="docutils literal"><span class="pre">INTERSECT</span></code> and <code class="docutils literal"><span class="pre">EXCEPT</span></code> are all set operations. These clauses are used
to combine the results of more than one select statement into a single result set:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>query UNION [ALL | DISTINCT] query
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span>query INTERSECT [DISTINCT] query
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span>query EXCEPT [DISTINCT] query
</pre></div>
</div>
<p>The argument <code class="docutils literal"><span class="pre">ALL</span></code> or <code class="docutils literal"><span class="pre">DISTINCT</span></code> controls which rows are included in
the final result set. If the argument <code class="docutils literal"><span class="pre">ALL</span></code> is specified all rows are
included even if the rows are identical. If the argument <code class="docutils literal"><span class="pre">DISTINCT</span></code>
is specified only unique rows are included in the combined result set.
If neither is specified, the behavior defaults to <code class="docutils literal"><span class="pre">DISTINCT</span></code>. The <code class="docutils literal"><span class="pre">ALL</span></code>
argument is not supported for <code class="docutils literal"><span class="pre">INTERSECT</span></code> or <code class="docutils literal"><span class="pre">EXCEPT</span></code>.</p>
<p>Multiple set operations are processed left to right, unless the order is explicitly
specified via parentheses. Additionally, <code class="docutils literal"><span class="pre">INTERSECT</span></code> binds more tightly
than <code class="docutils literal"><span class="pre">EXCEPT</span></code> and <code class="docutils literal"><span class="pre">UNION</span></code>. That means <code class="docutils literal"><span class="pre">A</span> <span class="pre">UNION</span> <span class="pre">B</span> <span class="pre">INTERSECT</span> <span class="pre">C</span> <span class="pre">EXCEPT</span> <span class="pre">D</span></code>
is the same as <code class="docutils literal"><span class="pre">A</span> <span class="pre">UNION</span> <span class="pre">(B</span> <span class="pre">INTERSECT</span> <span class="pre">C)</span> <span class="pre">EXCEPT</span> <span class="pre">D</span></code>.</p>
<p><strong>UNION</strong></p>
<p><code class="docutils literal"><span class="pre">UNION</span></code> combines all the rows that are in the result set from the
first query with those that are in the result set for the second query.
The following is an example of one of the simplest possible <code class="docutils literal"><span class="pre">UNION</span></code> clauses.
It selects the value <code class="docutils literal"><span class="pre">13</span></code> and combines this result set with a second query
that selects the value <code class="docutils literal"><span class="pre">42</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="mi">13</span>
<span class="k">UNION</span>
<span class="k">SELECT</span> <span class="mi">42</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0
-------
13
42
(2 rows)
</pre></div>
</div>
<p>The following query demonstrates the difference between <code class="docutils literal"><span class="pre">UNION</span></code> and <code class="docutils literal"><span class="pre">UNION</span> <span class="pre">ALL</span></code>.
It selects the value <code class="docutils literal"><span class="pre">13</span></code> and combines this result set with a second query that
selects the values <code class="docutils literal"><span class="pre">42</span></code> and <code class="docutils literal"><span class="pre">13</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="mi">13</span>
<span class="k">UNION</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">VALUES</span><span class="p">(</span><span class="mi">42</span><span class="p">,</span> <span class="mi">13</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0
-------
13
42
(2 rows)
</pre></div>
</div>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="mi">13</span>
<span class="k">UNION</span> <span class="k">ALL</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">VALUES</span><span class="p">(</span><span class="mi">42</span><span class="p">,</span> <span class="mi">13</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0
-------
13
42
13
(2 rows)
</pre></div>
</div>
<p><strong>INTERSECT</strong></p>
<p><code class="docutils literal"><span class="pre">INTERSECT</span></code> returns only the rows that are in the result sets of both the first and
the second queries. The following is an example of one of the simplest
possible <code class="docutils literal"><span class="pre">INTERSECT</span></code> clauses. It selects the values <code class="docutils literal"><span class="pre">13</span></code> and <code class="docutils literal"><span class="pre">42</span></code> and combines
this result set with a second query that selects the value <code class="docutils literal"><span class="pre">13</span></code>. Since <code class="docutils literal"><span class="pre">42</span></code>
is only in the result set of the first query, it is not included in the final results.:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">13</span><span class="p">,</span> <span class="mi">42</span><span class="p">)</span>
<span class="k">INTERSECT</span>
<span class="k">SELECT</span> <span class="mi">13</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0
-------
13
(2 rows)
</pre></div>
</div>
<p><strong>EXCEPT</strong></p>
<p><code class="docutils literal"><span class="pre">EXCEPT</span></code> returns the rows that are in the result set of the first query,
but not the second. The following is an example of one of the simplest
possible <code class="docutils literal"><span class="pre">EXCEPT</span></code> clauses. It selects the values <code class="docutils literal"><span class="pre">13</span></code> and <code class="docutils literal"><span class="pre">42</span></code> and combines
this result set with a second query that selects the value <code class="docutils literal"><span class="pre">13</span></code>. Since <code class="docutils literal"><span class="pre">13</span></code>
is also in the result set of the second query, it is not included in the final result.:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="k">VALUES</span> <span class="p">(</span><span class="mi">13</span><span class="p">,</span> <span class="mi">42</span><span class="p">)</span>
<span class="k">EXCEPT</span>
<span class="k">SELECT</span> <span class="mi">13</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> _col0
-------
42
(2 rows)
</pre></div>
</div>
</div>
<div class="section" id="order-by-clause">
<h2>ORDER BY Clause</h2>
<p>The <code class="docutils literal"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause is used to sort a result set by one or more
output expressions:</p>
<div class="highlight-none"><div class="highlight"><pre><span></span>ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]
</pre></div>
</div>
<p>Each expression may be composed of output columns or it may be an ordinal
number selecting an output column by position (starting at one). The
<code class="docutils literal"><span class="pre">ORDER</span> <span class="pre">BY</span></code> clause is evaluated as the last step of a query after any
<code class="docutils literal"><span class="pre">GROUP</span> <span class="pre">BY</span></code> or <code class="docutils literal"><span class="pre">HAVING</span></code> clause. The default null ordering is <code class="docutils literal"><span class="pre">NULLS</span> <span class="pre">LAST</span></code>,
regardless of the ordering direction.</p>
</div>
<div class="section" id="limit-clause">
<h2>LIMIT Clause</h2>
<p>The <code class="docutils literal"><span class="pre">LIMIT</span></code> clause restricts the number of rows in the result set.
<code class="docutils literal"><span class="pre">LIMIT</span> <span class="pre">ALL</span></code> is the same as omitting the <code class="docutils literal"><span class="pre">LIMIT</span></code> clause.
The following example queries a large table, but the limit clause restricts
the output to only have five rows (because the query lacks an <code class="docutils literal"><span class="pre">ORDER</span> <span class="pre">BY</span></code>,
exactly which rows are returned is arbitrary):</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">orderdate</span> <span class="k">FROM</span> <span class="n">orders</span> <span class="k">LIMIT</span> <span class="mi">5</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> o_orderdate
-------------
1996-04-14
1992-01-15
1995-02-01
1995-11-12
1992-04-26
(5 rows)
</pre></div>
</div>
</div>
<div class="section" id="tablesample">
<h2>TABLESAMPLE</h2>
<p>There are multiple sample methods:</p>
<dl class="docutils">
<dt><code class="docutils literal"><span class="pre">BERNOULLI</span></code></dt>
<dd><p class="first">Each row is selected to be in the table sample with a probability of
the sample percentage. When a table is sampled using the Bernoulli
method, all physical blocks of the table are scanned and certain
rows are skipped (based on a comparison between the sample percentage
and a random value calculated at runtime).</p>
<p class="last">The probability of a row being included in the result is independent
from any other row. This does not reduce the time required to read
the sampled table from disk. It may have an impact on the total
query time if the sampled output is processed further.</p>
</dd>
<dt><code class="docutils literal"><span class="pre">SYSTEM</span></code></dt>
<dd><p class="first">This sampling method divides the table into logical segments of data
and samples the table at this granularity. This sampling method either
selects all the rows from a particular segment of data or skips it
(based on a comparison between the sample percentage and a random
value calculated at runtime).</p>
<p class="last">The rows selected in a system sampling will be dependent on which
connector is used. For example, when used with Hive, it is dependent
on how the data is laid out on HDFS. This method does not guarantee
independent sampling probabilities.</p>
</dd>
</dl>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Neither of the two methods allow deterministic bounds on the number of rows returned.</p>
</div>
<p>Examples:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">users</span> <span class="n">TABLESAMPLE</span> <span class="n">BERNOULLI</span> <span class="p">(</span><span class="mi">50</span><span class="p">);</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">users</span> <span class="n">TABLESAMPLE</span> <span class="k">SYSTEM</span> <span class="p">(</span><span class="mi">75</span><span class="p">);</span>
</pre></div>
</div>
<p>Using sampling with joins:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">o</span><span class="p">.</span><span class="o">*</span><span class="p">,</span> <span class="n">i</span><span class="p">.</span><span class="o">*</span>
<span class="k">FROM</span> <span class="n">orders</span> <span class="n">o</span> <span class="n">TABLESAMPLE</span> <span class="k">SYSTEM</span> <span class="p">(</span><span class="mi">10</span><span class="p">)</span>
<span class="k">JOIN</span> <span class="n">lineitem</span> <span class="n">i</span> <span class="n">TABLESAMPLE</span> <span class="n">BERNOULLI</span> <span class="p">(</span><span class="mi">40</span><span class="p">)</span>
<span class="k">ON</span> <span class="n">o</span><span class="p">.</span><span class="n">orderkey</span> <span class="o">=</span> <span class="n">i</span><span class="p">.</span><span class="n">orderkey</span><span class="p">;</span>
</pre></div>
</div>
</div>
<div class="section" id="unnest">
<span id="id1"></span><h2>UNNEST</h2>
<p><code class="docutils literal"><span class="pre">UNNEST</span></code> can be used to expand an <a class="reference internal" href="../language/types.html#array-type"><span class="std std-ref">ARRAY</span></a> or <a class="reference internal" href="../language/types.html#map-type"><span class="std std-ref">MAP</span></a> into a relation.
Arrays are expanded into a single column, and maps are expanded into two columns (key, value).
<code class="docutils literal"><span class="pre">UNNEST</span></code> can also be used with multiple arguments, in which case they are expanded into multiple columns,
with as many rows as the highest cardinality argument (the other columns are padded with nulls).
<code class="docutils literal"><span class="pre">UNNEST</span></code> can optionally have a <code class="docutils literal"><span class="pre">WITH</span> <span class="pre">ORDINALITY</span></code> clause, in which case an additional ordinality column
is added to the end.
<code class="docutils literal"><span class="pre">UNNEST</span></code> is normally used with a <code class="docutils literal"><span class="pre">JOIN</span></code> and can reference columns
from relations on the left side of the join.</p>
<p>Using a single column:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">student</span><span class="p">,</span> <span class="n">score</span>
<span class="k">FROM</span> <span class="n">tests</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">UNNEST</span><span class="p">(</span><span class="n">scores</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="p">(</span><span class="n">score</span><span class="p">);</span>
</pre></div>
</div>
<p>Using multiple columns:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">numbers</span><span class="p">,</span> <span class="n">animals</span><span class="p">,</span> <span class="n">n</span><span class="p">,</span> <span class="n">a</span>
<span class="k">FROM</span> <span class="p">(</span>
<span class="k">VALUES</span>
<span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">5</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'dog'</span><span class="p">,</span> <span class="s1">'cat'</span><span class="p">,</span> <span class="s1">'bird'</span><span class="p">]),</span>
<span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">7</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">9</span><span class="p">],</span> <span class="nb">ARRAY</span><span class="p">[</span><span class="s1">'cow'</span><span class="p">,</span> <span class="s1">'pig'</span><span class="p">])</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">x</span> <span class="p">(</span><span class="n">numbers</span><span class="p">,</span> <span class="n">animals</span><span class="p">)</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">UNNEST</span><span class="p">(</span><span class="n">numbers</span><span class="p">,</span> <span class="n">animals</span><span class="p">)</span> <span class="k">AS</span> <span class="n">t</span> <span class="p">(</span><span class="n">n</span><span class="p">,</span> <span class="n">a</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> numbers | animals | n | a
-----------+------------------+------+------
[2, 5] | [dog, cat, bird] | 2 | dog
[2, 5] | [dog, cat, bird] | 5 | cat
[2, 5] | [dog, cat, bird] | NULL | bird
[7, 8, 9] | [cow, pig] | 7 | cow
[7, 8, 9] | [cow, pig] | 8 | pig
[7, 8, 9] | [cow, pig] | 9 | NULL
(6 rows)
</pre></div>
</div>
<p><code class="docutils literal"><span class="pre">WITH</span> <span class="pre">ORDINALITY</span></code> clause:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">numbers</span><span class="p">,</span> <span class="n">n</span><span class="p">,</span> <span class="n">a</span>
<span class="k">FROM</span> <span class="p">(</span>
<span class="k">VALUES</span>
<span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">2</span><span class="p">,</span> <span class="mi">5</span><span class="p">]),</span>
<span class="p">(</span><span class="nb">ARRAY</span><span class="p">[</span><span class="mi">7</span><span class="p">,</span> <span class="mi">8</span><span class="p">,</span> <span class="mi">9</span><span class="p">])</span>
<span class="p">)</span> <span class="k">AS</span> <span class="n">x</span> <span class="p">(</span><span class="n">numbers</span><span class="p">)</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="k">UNNEST</span><span class="p">(</span><span class="n">numbers</span><span class="p">)</span> <span class="k">WITH</span> <span class="k">ORDINALITY</span> <span class="k">AS</span> <span class="n">t</span> <span class="p">(</span><span class="n">n</span><span class="p">,</span> <span class="n">a</span><span class="p">);</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> numbers | n | a
-----------+---+---
[2, 5] | 2 | 1
[2, 5] | 5 | 2
[7, 8, 9] | 7 | 1
[7, 8, 9] | 8 | 2
[7, 8, 9] | 9 | 3
(5 rows)
</pre></div>
</div>
</div>
<div class="section" id="joins">
<h2>Joins</h2>
<p>Joins allow you to combine data from multiple relations.</p>
<div class="section" id="cross-join">
<h3>CROSS JOIN</h3>
<p>A cross join returns the Cartesian product (all combinations) of two
relations. Cross joins can either be specified using the explit
<code class="docutils literal"><span class="pre">CROSS</span> <span class="pre">JOIN</span></code> syntax or by specifying multiple relations in the
<code class="docutils literal"><span class="pre">FROM</span></code> clause.</p>
<p>Both of the following queries are equivalent:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="o">*</span>
<span class="k">FROM</span> <span class="n">nation</span><span class="p">,</span> <span class="n">region</span><span class="p">;</span>
</pre></div>
</div>
<p>The <code class="docutils literal"><span class="pre">nation</span></code> table contains 25 rows and the <code class="docutils literal"><span class="pre">region</span></code> table contains 5 rows,
so a cross join between the two tables produces 125 rows:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">name</span> <span class="k">AS</span> <span class="n">nation</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">name</span> <span class="k">AS</span> <span class="n">region</span>
<span class="k">FROM</span> <span class="n">nation</span> <span class="k">AS</span> <span class="n">n</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span> <span class="k">AS</span> <span class="n">r</span>
<span class="k">ORDER</span> <span class="k">BY</span> <span class="mi">1</span><span class="p">,</span> <span class="mi">2</span><span class="p">;</span>
</pre></div>
</div>
<div class="highlight-none"><div class="highlight"><pre><span></span> nation | region
----------------+-------------
ALGERIA | AFRICA
ALGERIA | AMERICA
ALGERIA | ASIA
ALGERIA | EUROPE
ALGERIA | MIDDLE EAST
ARGENTINA | AFRICA
ARGENTINA | AMERICA
...
(125 rows)
</pre></div>
</div>
</div>
<div class="section" id="qualifying-column-names">
<h3>Qualifying Column Names</h3>
<p>When two relations in a join have columns with the same name, the column
references must be qualified using the relation alias (if the relation
has an alias), or with the relation name:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">nation</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">region</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span> <span class="k">AS</span> <span class="n">n</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span> <span class="k">AS</span> <span class="n">r</span><span class="p">;</span>
<span class="k">SELECT</span> <span class="n">n</span><span class="p">.</span><span class="n">name</span><span class="p">,</span> <span class="n">r</span><span class="p">.</span><span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span> <span class="n">n</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span> <span class="n">r</span><span class="p">;</span>
</pre></div>
</div>
<p>The following query will fail with the error <code class="docutils literal"><span class="pre">Column</span> <span class="pre">'name'</span> <span class="pre">is</span> <span class="pre">ambiguous</span></code>:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">region</span><span class="p">;</span>
</pre></div>
</div>
</div>
</div>
<div class="section" id="subqueries">
<h2>Subqueries</h2>
<p>A subquery is an expression which is composed of a query. The subquery
is correlated when it refers to columns outside of the subquery.
Logically, the subquery will be evaluated for each row in the surrounding
query. The referenced columns will thus be constant during any single
evaluation of the subquery.</p>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Support for correlated subqueries is limited. Not every standard form is supported.</p>
</div>
<div class="section" id="exists">
<h3>EXISTS</h3>
<p>The <code class="docutils literal"><span class="pre">EXISTS</span></code> predicate determines if a subquery returns any rows:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">WHERE</span> <span class="k">EXISTS</span> <span class="p">(</span><span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">region</span> <span class="k">WHERE</span> <span class="n">region</span><span class="p">.</span><span class="n">regionkey</span> <span class="o">=</span> <span class="n">nation</span><span class="p">.</span><span class="n">regionkey</span><span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="in">
<h3>IN</h3>
<p>The <code class="docutils literal"><span class="pre">IN</span></code> predicate determines if any values produced by the subquery
are equal to the provided expression. The result of <code class="docutils literal"><span class="pre">IN</span></code> follows the
standard rules for nulls. The subquery must produce exactly one column:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">WHERE</span> <span class="n">regionkey</span> <span class="k">IN</span> <span class="p">(</span><span class="k">SELECT</span> <span class="n">regionkey</span> <span class="k">FROM</span> <span class="n">region</span><span class="p">)</span>
</pre></div>
</div>
</div>
<div class="section" id="scalar-subquery">
<h3>Scalar Subquery</h3>
<p>A scalar subquery is a non-correlated subquery that returns zero or
one row. It is an error for the subquery to produce more than one
row. The returned value is <code class="docutils literal"><span class="pre">NULL</span></code> if the subquery produces no rows:</p>
<div class="highlight-sql"><div class="highlight"><pre><span></span><span class="k">SELECT</span> <span class="n">name</span>
<span class="k">FROM</span> <span class="n">nation</span>
<span class="k">WHERE</span> <span class="n">regionkey</span> <span class="o">=</span> <span class="p">(</span><span class="k">SELECT</span> <span class="k">max</span><span class="p">(</span><span class="n">regionkey</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">region</span><span class="p">)</span>
</pre></div>
</div>
<div class="admonition note">
<p class="first admonition-title">Note</p>
<p class="last">Currently only single column can be returned from the scalar subquery.</p>
</div>
</div>
</div>
</div>
</div>
<div class="bottomnav">
<p class="nav">
<span class="left">
« <a href="rollback.html">8.20. ROLLBACK</a>
</span>
<span class="right">
<a href="set-session.html">8.22. SET SESSION</a> »
</span>
</p>
</div>
<div class="footer" role="contentinfo">
</div>
</body>
</html>