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
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>SELECT</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-savepoint.html" title="SAVEPOINT">
<link rel="next" href="sql-selectinto.html" title="SELECT INTO">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-select"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>SELECT — retrieve rows from a table or view</p>
</div>
<a name="id782756"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">SELECT [ ALL | DISTINCT [ ON ( <em class="replaceable"><code>expression</code></em> [, ...] ) ] ]
* | <em class="replaceable"><code>expression</code></em> [ AS <em class="replaceable"><code>output_name</code></em> ] [, ...]
[ FROM <em class="replaceable"><code>from_item</code></em> [, ...] ]
[ WHERE <em class="replaceable"><code>condition</code></em> ]
[ GROUP BY <em class="replaceable"><code>expression</code></em> [, ...] ]
[ HAVING <em class="replaceable"><code>condition</code></em> [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] <em class="replaceable"><code>select</code></em> ]
[ ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [, ...] ]
[ LIMIT { <em class="replaceable"><code>count</code></em> | ALL } ]
[ OFFSET <em class="replaceable"><code>start</code></em> ]
[ FOR { UPDATE | SHARE } [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT ] ]
where <em class="replaceable"><code>from_item</code></em> can be one of:
[ ONLY ] <em class="replaceable"><code>table_name</code></em> [ * ] [ [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ] ]
( <em class="replaceable"><code>select</code></em> ) [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] ) ]
<em class="replaceable"><code>function_name</code></em> ( [ <em class="replaceable"><code>argument</code></em> [, ...] ] ) [ AS ] <em class="replaceable"><code>alias</code></em> [ ( <em class="replaceable"><code>column_alias</code></em> [, ...] | <em class="replaceable"><code>column_definition</code></em> [, ...] ) ]
<em class="replaceable"><code>function_name</code></em> ( [ <em class="replaceable"><code>argument</code></em> [, ...] ] ) AS ( <em class="replaceable"><code>column_definition</code></em> [, ...] )
<em class="replaceable"><code>from_item</code></em> [ NATURAL ] <em class="replaceable"><code>join_type</code></em> <em class="replaceable"><code>from_item</code></em> [ ON <em class="replaceable"><code>join_condition</code></em> | USING ( <em class="replaceable"><code>join_column</code></em> [, ...] ) ]</pre>
</div>
<div class="refsect1" lang="en">
<a name="id783003"></a><h2>Description</h2>
<p> <code class="command">SELECT</code> retrieves rows from zero or more tables.
The general processing of <code class="command">SELECT</code> is as follows:
</p>
<div class="orderedlist"><ol type="1">
<li><p> All elements in the <code class="literal">FROM</code> list are computed.
(Each element in the <code class="literal">FROM</code> list is a real or
virtual table.) If more than one element is specified in the
<code class="literal">FROM</code> list, they are cross-joined together.
(See <a href="sql-select.html#sql-from"><code class="literal">FROM</code> Clause</a> below.)
</p></li>
<li><p> If the <code class="literal">WHERE</code> clause is specified, all rows
that do not satisfy the condition are eliminated from the
output. (See <a href="sql-select.html#sql-where"><code class="literal">WHERE</code> Clause</a> below.)
</p></li>
<li><p> If the <code class="literal">GROUP BY</code> clause is specified, the
output is divided into groups of rows that match on one or more
values. If the <code class="literal">HAVING</code> clause is present, it
eliminates groups that do not satisfy the given condition. (See
<a href="sql-select.html#sql-groupby"><code class="literal">GROUP BY</code> Clause</a> and
<a href="sql-select.html#sql-having"><code class="literal">HAVING</code> Clause</a> below.)
</p></li>
<li><p> The actual output rows are computed using the
<code class="command">SELECT</code> output expressions for each selected
row. (See
<a href="sql-select.html#sql-select-list"><code class="command">SELECT</code> List</a>
below.)
</p></li>
<li><p> Using the operators <code class="literal">UNION</code>,
<code class="literal">INTERSECT</code>, and <code class="literal">EXCEPT</code>, the
output of more than one <code class="command">SELECT</code> statement can
be combined to form a single result set. The
<code class="literal">UNION</code> operator returns all rows that are in
one or both of the result sets. The
<code class="literal">INTERSECT</code> operator returns all rows that are
strictly in both result sets. The <code class="literal">EXCEPT</code>
operator returns the rows that are in the first result set but
not in the second. In all three cases, duplicate rows are
eliminated unless <code class="literal">ALL</code> is specified. (See
<a href="sql-select.html#sql-union"><code class="literal">UNION</code> Clause</a>, <a href="sql-select.html#sql-intersect"><code class="literal">INTERSECT</code> Clause</a>, and
<a href="sql-select.html#sql-except"><code class="literal">EXCEPT</code> Clause</a> below.)
</p></li>
<li><p> If the <code class="literal">ORDER BY</code> clause is specified, the
returned rows are sorted in the specified order. If
<code class="literal">ORDER BY</code> is not given, the rows are returned
in whatever order the system finds fastest to produce. (See
<a href="sql-select.html#sql-orderby"><code class="literal">ORDER BY</code> Clause</a> below.)
</p></li>
<li><p> <code class="literal">DISTINCT</code> eliminates duplicate rows from the
result. <code class="literal">DISTINCT ON</code> eliminates rows that
match on all the specified expressions. <code class="literal">ALL</code>
(the default) will return all candidate rows, including
duplicates. (See <a href="sql-select.html#sql-distinct"><code class="literal">DISTINCT</code> Clause</a> below.)
</p></li>
<li><p> If the <code class="literal">LIMIT</code> or <code class="literal">OFFSET</code>
clause is specified, the <code class="command">SELECT</code> statement
only returns a subset of the result rows. (See <a href="sql-select.html#sql-limit"><code class="literal">LIMIT</code> Clause</a> below.)
</p></li>
<li><p> If the <code class="literal">FOR UPDATE</code> or <code class="literal">FOR SHARE</code>
clause is specified, the
<code class="command">SELECT</code> statement locks the selected rows
against concurrent updates. (See <a href="sql-select.html#sql-for-update-share"><code class="literal">FOR UPDATE</code>/<code class="literal">FOR SHARE</code> Clause</a> below.)
</p></li>
</ol></div>
<p>
</p>
<p> You must have <code class="literal">SELECT</code> privilege on a table to
read its values. The use of <code class="literal">FOR UPDATE</code> or
<code class="literal">FOR SHARE</code> requires
<code class="literal">UPDATE</code> privilege as well.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id783453"></a><h2>Parameters</h2>
<div class="refsect2" lang="en">
<a name="sql-from"></a><h3>
<code class="literal">FROM</code> Clause</h3>
<p> The <code class="literal">FROM</code> clause specifies one or more source
tables for the <code class="command">SELECT</code>. If multiple sources are
specified, the result is the Cartesian product (cross join) of all
the sources. But usually qualification conditions
are added to restrict the returned rows to a small subset of the
Cartesian product.
</p>
<p> The <code class="literal">FROM</code> clause can contain the following
elements:
</p>
<div class="variablelist"><dl>
<dt><span class="term"><em class="replaceable"><code>table_name</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of an existing table or
view. If <code class="literal">ONLY</code> is specified, only that table is
scanned. If <code class="literal">ONLY</code> is not specified, the table and
all its descendant tables (if any) are scanned. <code class="literal">*</code>
can be appended to the table name to indicate that descendant
tables are to be scanned, but in the current version, this is
the default behavior. (In releases before 7.1,
<code class="literal">ONLY</code> was the default behavior.) The default
behavior can be modified by changing the <a href="runtime-config-compatible.html#guc-sql-inheritance">sql_inheritance</a> configuration option.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>alias</code></em></span></dt>
<dd><p> A substitute name for the <code class="literal">FROM</code> item containing the
alias. An alias is used for brevity or to eliminate ambiguity
for self-joins (where the same table is scanned multiple
times). When an alias is provided, it completely hides the
actual name of the table or function; for example given
<code class="literal">FROM foo AS f</code>, the remainder of the
<code class="command">SELECT</code> must refer to this <code class="literal">FROM</code>
item as <code class="literal">f</code> not <code class="literal">foo</code>. If an alias is
written, a column alias list can also be written to provide
substitute names for one or more columns of the table.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>select</code></em></span></dt>
<dd><p> A sub-<code class="command">SELECT</code> can appear in the
<code class="literal">FROM</code> clause. This acts as though its
output were created as a temporary table for the duration of
this single <code class="command">SELECT</code> command. Note that the
sub-<code class="command">SELECT</code> must be surrounded by
parentheses, and an alias <span class="emphasis"><em>must</em></span> be
provided for it.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt>
<dd><p> Function calls can appear in the <code class="literal">FROM</code>
clause. (This is especially useful for functions that return
result sets, but any function can be used.) This acts as
though its output were created as a temporary table for the
duration of this single <code class="command">SELECT</code> command. An
alias may also be used. If an alias is written, a column alias
list can also be written to provide substitute names for one
or more attributes of the function's composite return type. If
the function has been defined as returning the <code class="type">record</code>
data type, then an alias or the key word <code class="literal">AS</code> must
be present, followed by a column definition list in the form
<code class="literal">( <em class="replaceable"><code>column_name</code></em> <em class="replaceable"><code>data_type</code></em> [<span class="optional">, ... </span>]
)</code>. The column definition list must match the actual
number and types of columns returned by the function.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>join_type</code></em></span></dt>
<dd>
<p> One of
</p>
<div class="itemizedlist"><ul type="disc">
<li><p><code class="literal">[ INNER ] JOIN</code></p></li>
<li><p><code class="literal">LEFT [ OUTER ] JOIN</code></p></li>
<li><p><code class="literal">RIGHT [ OUTER ] JOIN</code></p></li>
<li><p><code class="literal">FULL [ OUTER ] JOIN</code></p></li>
<li><p><code class="literal">CROSS JOIN</code></p></li>
</ul></div>
<p>
For the <code class="literal">INNER</code> and <code class="literal">OUTER</code> join types, a
join condition must be specified, namely exactly one of
<code class="literal">NATURAL</code>, <code class="literal">ON <em class="replaceable"><code>join_condition</code></em></code>, or
<code class="literal">USING (<em class="replaceable"><code>join_column</code></em> [, ...])</code>.
See below for the meaning. For <code class="literal">CROSS JOIN</code>,
none of these clauses may appear.
</p>
<p> A <code class="literal">JOIN</code> clause combines two
<code class="literal">FROM</code> items. Use parentheses if necessary to
determine the order of nesting. In the absence of parentheses,
<code class="literal">JOIN</code>s nest left-to-right. In any case
<code class="literal">JOIN</code> binds more tightly than the commas
separating <code class="literal">FROM</code> items.
</p>
<p> <code class="literal">CROSS JOIN</code> and <code class="literal">INNER JOIN</code>
produce a simple Cartesian product, the same result as you get from
listing the two items at the top level of <code class="literal">FROM</code>,
but restricted by the join condition (if any).
<code class="literal">CROSS JOIN</code> is equivalent to <code class="literal">INNER JOIN ON
(TRUE)</code>, that is, no rows are removed by qualification.
These join types are just a notational convenience, since they
do nothing you couldn't do with plain <code class="literal">FROM</code> and
<code class="literal">WHERE</code>.
</p>
<p> <code class="literal">LEFT OUTER JOIN</code> returns all rows in the qualified
Cartesian product (i.e., all combined rows that pass its join
condition), plus one copy of each row in the left-hand table
for which there was no right-hand row that passed the join
condition. This left-hand row is extended to the full width
of the joined table by inserting null values for the
right-hand columns. Note that only the <code class="literal">JOIN</code>
clause's own condition is considered while deciding which rows
have matches. Outer conditions are applied afterwards.
</p>
<p> Conversely, <code class="literal">RIGHT OUTER JOIN</code> returns all the
joined rows, plus one row for each unmatched right-hand row
(extended with nulls on the left). This is just a notational
convenience, since you could convert it to a <code class="literal">LEFT
OUTER JOIN</code> by switching the left and right inputs.
</p>
<p> <code class="literal">FULL OUTER JOIN</code> returns all the joined rows, plus
one row for each unmatched left-hand row (extended with nulls
on the right), plus one row for each unmatched right-hand row
(extended with nulls on the left).
</p>
</dd>
<dt><span class="term"><code class="literal">ON <em class="replaceable"><code>join_condition</code></em></code></span></dt>
<dd><p> <em class="replaceable"><code>join_condition</code></em> is
an expression resulting in a value of type
<code class="type">boolean</code> (similar to a <code class="literal">WHERE</code>
clause) that specifies which rows in a join are considered to
match.
</p></dd>
<dt><span class="term"><code class="literal">USING (<em class="replaceable"><code>join_column</code></em> [, ...])</code></span></dt>
<dd><p> A clause of the form <code class="literal">USING ( a, b, ... )</code> is
shorthand for <code class="literal">ON left_table.a = right_table.a AND
left_table.b = right_table.b ...</code>. Also,
<code class="literal">USING</code> implies that only one of each pair of
equivalent columns will be included in the join output, not
both.
</p></dd>
<dt><span class="term"><code class="literal">NATURAL</code></span></dt>
<dd><p> <code class="literal">NATURAL</code> is shorthand for a
<code class="literal">USING</code> list that mentions all columns in the two
tables that have the same names.
</p></dd>
</dl></div>
<p>
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-where"></a><h3>
<code class="literal">WHERE</code> Clause</h3>
<p> The optional <code class="literal">WHERE</code> clause has the general form
</p>
<pre class="synopsis">WHERE <em class="replaceable"><code>condition</code></em></pre>
<p>
where <em class="replaceable"><code>condition</code></em> is
any expression that evaluates to a result of type
<code class="type">boolean</code>. Any row that does not satisfy this
condition will be eliminated from the output. A row satisfies the
condition if it returns true when the actual row values are
substituted for any variable references.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-groupby"></a><h3>
<code class="literal">GROUP BY</code> Clause</h3>
<p> The optional <code class="literal">GROUP BY</code> clause has the general form
</p>
<pre class="synopsis">GROUP BY <em class="replaceable"><code>expression</code></em> [, ...]</pre>
<p>
</p>
<p> <code class="literal">GROUP BY</code> will condense into a single row all
selected rows that share the same values for the grouped
expressions. <em class="replaceable"><code>expression</code></em> can be an input column
name, or the name or ordinal number of an output column
(<code class="command">SELECT</code> list item), or an arbitrary
expression formed from input-column values. In case of ambiguity,
a <code class="literal">GROUP BY</code> name will be interpreted as an
input-column name rather than an output column name.
</p>
<p> Aggregate functions, if any are used, are computed across all rows
making up each group, producing a separate value for each group
(whereas without <code class="literal">GROUP BY</code>, an aggregate
produces a single value computed across all the selected rows).
When <code class="literal">GROUP BY</code> is present, it is not valid for
the <code class="command">SELECT</code> list expressions to refer to
ungrouped columns except within aggregate functions, since there
would be more than one possible value to return for an ungrouped
column.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-having"></a><h3>
<code class="literal">HAVING</code> Clause</h3>
<p> The optional <code class="literal">HAVING</code> clause has the general form
</p>
<pre class="synopsis">HAVING <em class="replaceable"><code>condition</code></em></pre>
<p>
where <em class="replaceable"><code>condition</code></em> is
the same as specified for the <code class="literal">WHERE</code> clause.
</p>
<p> <code class="literal">HAVING</code> eliminates group rows that do not
satisfy the condition. <code class="literal">HAVING</code> is different
from <code class="literal">WHERE</code>: <code class="literal">WHERE</code> filters
individual rows before the application of <code class="literal">GROUP
BY</code>, while <code class="literal">HAVING</code> filters group rows
created by <code class="literal">GROUP BY</code>. Each column referenced in
<em class="replaceable"><code>condition</code></em> must
unambiguously reference a grouping column, unless the reference
appears within an aggregate function.
</p>
<p> The presence of <code class="literal">HAVING</code> turns a query into a grouped
query even if there is no <code class="literal">GROUP BY</code> clause. This is the
same as what happens when the query contains aggregate functions but
no <code class="literal">GROUP BY</code> clause. All the selected rows are considered to
form a single group, and the <code class="command">SELECT</code> list and
<code class="literal">HAVING</code> clause can only reference table columns from
within aggregate functions. Such a query will emit a single row if the
<code class="literal">HAVING</code> condition is true, zero rows if it is not true.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-select-list"></a><h3>
<code class="command">SELECT</code> List</h3>
<p> The <code class="command">SELECT</code> list (between the key words
<code class="literal">SELECT</code> and <code class="literal">FROM</code>) specifies expressions
that form the output rows of the <code class="command">SELECT</code>
statement. The expressions can (and usually do) refer to columns
computed in the <code class="literal">FROM</code> clause. Using the clause
<code class="literal">AS <em class="replaceable"><code>output_name</code></em></code>, another
name can be specified for an output column. This name is
primarily used to label the column for display. It can also be
used to refer to the column's value in <code class="literal">ORDER BY</code> and
<code class="literal">GROUP BY</code> clauses, but not in the <code class="literal">WHERE</code> or
<code class="literal">HAVING</code> clauses; there you must write out the
expression instead.
</p>
<p> Instead of an expression, <code class="literal">*</code> can be written in
the output list as a shorthand for all the columns of the selected
rows. Also, one can write <code class="literal"><em class="replaceable"><code>table_name</code></em>.*</code> as a
shorthand for the columns coming from just that table.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-union"></a><h3>
<code class="literal">UNION</code> Clause</h3>
<p> The <code class="literal">UNION</code> clause has this general form:
</p>
<pre class="synopsis"><em class="replaceable"><code>select_statement</code></em> UNION [ ALL ] <em class="replaceable"><code>select_statement</code></em></pre>
<p>
<em class="replaceable"><code>select_statement</code></em> is
any <code class="command">SELECT</code> statement without an <code class="literal">ORDER
BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR UPDATE</code>, or
<code class="literal">FOR SHARE</code> clause.
(<code class="literal">ORDER BY</code> and <code class="literal">LIMIT</code> can be attached to a
subexpression if it is enclosed in parentheses. Without
parentheses, these clauses will be taken to apply to the result of
the <code class="literal">UNION</code>, not to its right-hand input
expression.)
</p>
<p> The <code class="literal">UNION</code> operator computes the set union of
the rows returned by the involved <code class="command">SELECT</code>
statements. A row is in the set union of two result sets if it
appears in at least one of the result sets. The two
<code class="command">SELECT</code> statements that represent the direct
operands of the <code class="literal">UNION</code> must produce the same
number of columns, and corresponding columns must be of compatible
data types.
</p>
<p> The result of <code class="literal">UNION</code> does not contain any duplicate
rows unless the <code class="literal">ALL</code> option is specified.
<code class="literal">ALL</code> prevents elimination of duplicates. (Therefore,
<code class="literal">UNION ALL</code> is usually significantly quicker than
<code class="literal">UNION</code>; use <code class="literal">ALL</code> when you can.)
</p>
<p> Multiple <code class="literal">UNION</code> operators in the same
<code class="command">SELECT</code> statement are evaluated left to right,
unless otherwise indicated by parentheses.
</p>
<p> Currently, <code class="literal">FOR UPDATE</code> and <code class="literal">FOR SHARE</code> may not be
specified either for a <code class="literal">UNION</code> result or for any input of a
<code class="literal">UNION</code>.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-intersect"></a><h3>
<code class="literal">INTERSECT</code> Clause</h3>
<p> The <code class="literal">INTERSECT</code> clause has this general form:
</p>
<pre class="synopsis"><em class="replaceable"><code>select_statement</code></em> INTERSECT [ ALL ] <em class="replaceable"><code>select_statement</code></em></pre>
<p>
<em class="replaceable"><code>select_statement</code></em> is
any <code class="command">SELECT</code> statement without an <code class="literal">ORDER
BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR UPDATE</code>, or
<code class="literal">FOR SHARE</code> clause.
</p>
<p> The <code class="literal">INTERSECT</code> operator computes the set
intersection of the rows returned by the involved
<code class="command">SELECT</code> statements. A row is in the
intersection of two result sets if it appears in both result sets.
</p>
<p> The result of <code class="literal">INTERSECT</code> does not contain any
duplicate rows unless the <code class="literal">ALL</code> option is specified.
With <code class="literal">ALL</code>, a row that has <em class="replaceable"><code>m</code></em> duplicates in the
left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear
min(<em class="replaceable"><code>m</code></em>,<em class="replaceable"><code>n</code></em>) times in the result set.
</p>
<p> Multiple <code class="literal">INTERSECT</code> operators in the same
<code class="command">SELECT</code> statement are evaluated left to right,
unless parentheses dictate otherwise.
<code class="literal">INTERSECT</code> binds more tightly than
<code class="literal">UNION</code>. That is, <code class="literal">A UNION B INTERSECT
C</code> will be read as <code class="literal">A UNION (B INTERSECT
C)</code>.
</p>
<p> Currently, <code class="literal">FOR UPDATE</code> and <code class="literal">FOR SHARE</code> may not be
specified either for an <code class="literal">INTERSECT</code> result or for any input of
an <code class="literal">INTERSECT</code>.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-except"></a><h3>
<code class="literal">EXCEPT</code> Clause</h3>
<p> The <code class="literal">EXCEPT</code> clause has this general form:
</p>
<pre class="synopsis"><em class="replaceable"><code>select_statement</code></em> EXCEPT [ ALL ] <em class="replaceable"><code>select_statement</code></em></pre>
<p>
<em class="replaceable"><code>select_statement</code></em> is
any <code class="command">SELECT</code> statement without an <code class="literal">ORDER
BY</code>, <code class="literal">LIMIT</code>, <code class="literal">FOR UPDATE</code>, or
<code class="literal">FOR SHARE</code> clause.
</p>
<p> The <code class="literal">EXCEPT</code> operator computes the set of rows
that are in the result of the left <code class="command">SELECT</code>
statement but not in the result of the right one.
</p>
<p> The result of <code class="literal">EXCEPT</code> does not contain any
duplicate rows unless the <code class="literal">ALL</code> option is specified.
With <code class="literal">ALL</code>, a row that has <em class="replaceable"><code>m</code></em> duplicates in the
left table and <em class="replaceable"><code>n</code></em> duplicates in the right table will appear
max(<em class="replaceable"><code>m</code></em>-<em class="replaceable"><code>n</code></em>,0) times in the result set.
</p>
<p> Multiple <code class="literal">EXCEPT</code> operators in the same
<code class="command">SELECT</code> statement are evaluated left to right,
unless parentheses dictate otherwise. <code class="literal">EXCEPT</code> binds at
the same level as <code class="literal">UNION</code>.
</p>
<p> Currently, <code class="literal">FOR UPDATE</code> and <code class="literal">FOR SHARE</code> may not be
specified either for an <code class="literal">EXCEPT</code> result or for any input of
an <code class="literal">EXCEPT</code>.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-orderby"></a><h3>
<code class="literal">ORDER BY</code> Clause</h3>
<p> The optional <code class="literal">ORDER BY</code> clause has this general form:
</p>
<pre class="synopsis">ORDER BY <em class="replaceable"><code>expression</code></em> [ ASC | DESC | USING <em class="replaceable"><code>operator</code></em> ] [, ...]</pre>
<p>
<em class="replaceable"><code>expression</code></em> can be the
name or ordinal number of an output column
(<code class="command">SELECT</code> list item), or it can be an arbitrary
expression formed from input-column values.
</p>
<p> The <code class="literal">ORDER BY</code> clause causes the result rows to
be sorted according to the specified expressions. If two rows are
equal according to the leftmost expression, the are compared
according to the next expression and so on. If they are equal
according to all specified expressions, they are returned in
an implementation-dependent order.
</p>
<p> The ordinal number refers to the ordinal (left-to-right) position
of the result column. This feature makes it possible to define an
ordering on the basis of a column that does not have a unique
name. This is never absolutely necessary because it is always
possible to assign a name to a result column using the
<code class="literal">AS</code> clause.
</p>
<p> It is also possible to use arbitrary expressions in the
<code class="literal">ORDER BY</code> clause, including columns that do not
appear in the <code class="command">SELECT</code> result list. Thus the
following statement is valid:
</p>
<pre class="programlisting">SELECT name FROM distributors ORDER BY code;</pre>
<p>
A limitation of this feature is that an <code class="literal">ORDER BY</code>
clause applying to the result of a <code class="literal">UNION</code>,
<code class="literal">INTERSECT</code>, or <code class="literal">EXCEPT</code> clause may only
specify an output column name or number, not an expression.
</p>
<p> If an <code class="literal">ORDER BY</code> expression is a simple name that
matches both a result column name and an input column name,
<code class="literal">ORDER BY</code> will interpret it as the result column name.
This is the opposite of the choice that <code class="literal">GROUP BY</code> will
make in the same situation. This inconsistency is made to be
compatible with the SQL standard.
</p>
<p> Optionally one may add the key word <code class="literal">ASC</code> (ascending) or
<code class="literal">DESC</code> (descending) after any expression in the
<code class="literal">ORDER BY</code> clause. If not specified, <code class="literal">ASC</code> is
assumed by default. Alternatively, a specific ordering operator
name may be specified in the <code class="literal">USING</code> clause.
<code class="literal">ASC</code> is usually equivalent to <code class="literal">USING <</code> and
<code class="literal">DESC</code> is usually equivalent to <code class="literal">USING ></code>.
(But the creator of a user-defined data type can define exactly what the
default sort ordering is, and it might correspond to operators with other
names.)
</p>
<p> The null value sorts higher than any other value. In other words,
with ascending sort order, null values sort at the end, and with
descending sort order, null values sort at the beginning.
</p>
<p> Character-string data is sorted according to the locale-specific
collation order that was established when the database cluster
was initialized.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-distinct"></a><h3>
<code class="literal">DISTINCT</code> Clause</h3>
<p> If <code class="literal">DISTINCT</code> is specified, all duplicate rows are
removed from the result set (one row is kept from each group of
duplicates). <code class="literal">ALL</code> specifies the opposite: all rows are
kept; that is the default.
</p>
<p> <code class="literal">DISTINCT ON ( <em class="replaceable"><code>expression</code></em> [, ...] )</code>
keeps only the first row of each set of rows where the given
expressions evaluate to equal. The <code class="literal">DISTINCT ON</code>
expressions are interpreted using the same rules as for
<code class="literal">ORDER BY</code> (see above). Note that the “<span class="quote">first
row</span>” of each set is unpredictable unless <code class="literal">ORDER
BY</code> is used to ensure that the desired row appears first. For
example,
</p>
<pre class="programlisting">SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;</pre>
<p>
retrieves the most recent weather report for each location. But
if we had not used <code class="literal">ORDER BY</code> to force descending order
of time values for each location, we'd have gotten a report from
an unpredictable time for each location.
</p>
<p> The <code class="literal">DISTINCT ON</code> expression(s) must match the leftmost
<code class="literal">ORDER BY</code> expression(s). The <code class="literal">ORDER BY</code> clause
will normally contain additional expression(s) that determine the
desired precedence of rows within each <code class="literal">DISTINCT ON</code> group.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-limit"></a><h3>
<code class="literal">LIMIT</code> Clause</h3>
<p> The <code class="literal">LIMIT</code> clause consists of two independent
sub-clauses:
</p>
<pre class="synopsis">LIMIT { <em class="replaceable"><code>count</code></em> | ALL }
OFFSET <em class="replaceable"><code>start</code></em></pre>
<p>
<em class="replaceable"><code>count</code></em> specifies the
maximum number of rows to return, while <em class="replaceable"><code>start</code></em> specifies the number of rows
to skip before starting to return rows. When both are specified,
<em class="replaceable"><code>start</code></em> rows are skipped
before starting to count the <em class="replaceable"><code>count</code></em> rows to be returned.
</p>
<p> When using <code class="literal">LIMIT</code>, it is a good idea to use an
<code class="literal">ORDER BY</code> clause that constrains the result rows into a
unique order. Otherwise you will get an unpredictable subset of
the query's rows [mdash ] you may be asking for the tenth through
twentieth rows, but tenth through twentieth in what ordering? You
don't know what ordering unless you specify <code class="literal">ORDER BY</code>.
</p>
<p> The query planner takes <code class="literal">LIMIT</code> into account when
generating a query plan, so you are very likely to get different
plans (yielding different row orders) depending on what you use
for <code class="literal">LIMIT</code> and <code class="literal">OFFSET</code>. Thus, using
different <code class="literal">LIMIT</code>/<code class="literal">OFFSET</code> values to select
different subsets of a query result <span class="emphasis"><em>will give
inconsistent results</em></span> unless you enforce a predictable
result ordering with <code class="literal">ORDER BY</code>. This is not a bug; it
is an inherent consequence of the fact that SQL does not promise
to deliver the results of a query in any particular order unless
<code class="literal">ORDER BY</code> is used to constrain the order.
</p>
</div>
<div class="refsect2" lang="en">
<a name="sql-for-update-share"></a><h3>
<code class="literal">FOR UPDATE</code>/<code class="literal">FOR SHARE</code> Clause</h3>
<p> The <code class="literal">FOR UPDATE</code> clause has this form:
</p>
<pre class="synopsis">FOR UPDATE [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT ]</pre>
<p>
</p>
<p> The closely related <code class="literal">FOR SHARE</code> clause has this form:
</p>
<pre class="synopsis">FOR SHARE [ OF <em class="replaceable"><code>table_name</code></em> [, ...] ] [ NOWAIT ]</pre>
<p>
</p>
<p> <code class="literal">FOR UPDATE</code> causes the rows retrieved by the
<code class="command">SELECT</code> statement to be locked as though for
update. This prevents them from being modified or deleted by
other transactions until the current transaction ends. That is,
other transactions that attempt <code class="command">UPDATE</code>,
<code class="command">DELETE</code>, or <code class="command">SELECT FOR UPDATE</code>
of these rows will be blocked until the current transaction ends.
Also, if an <code class="command">UPDATE</code>, <code class="command">DELETE</code>,
or <code class="command">SELECT FOR UPDATE</code> from another transaction
has already locked a selected row or rows, <code class="command">SELECT FOR
UPDATE</code> will wait for the other transaction to complete,
and will then lock and return the updated row (or no row, if the
row was deleted). For further discussion see <a href="mvcc.html" title="Chapter12.Concurrency Control">Chapter12, <i>Concurrency Control</i></a>.
</p>
<p> To prevent the operation from waiting for other transactions to commit,
use the <code class="literal">NOWAIT</code> option. <code class="command">SELECT FOR UPDATE
NOWAIT</code> reports an error, rather than waiting, if a selected row
cannot be locked immediately. Note that <code class="literal">NOWAIT</code> applies only
to the row-level lock(s) [mdash ] the required <code class="literal">ROW SHARE</code>
table-level lock is still taken in the ordinary way (see
<a href="mvcc.html" title="Chapter12.Concurrency Control">Chapter12, <i>Concurrency Control</i></a>). You can use the <code class="literal">NOWAIT</code> option of
<a href="sql-lock.html">LOCK</a>
if you need to acquire the table-level lock without waiting.
</p>
<p> <code class="literal">FOR SHARE</code> behaves similarly, except that it
acquires a shared rather than exclusive lock on each retrieved
row. A shared lock blocks other transactions from performing
<code class="command">UPDATE</code>, <code class="command">DELETE</code>, or <code class="command">SELECT
FOR UPDATE</code> on these rows, but it does not prevent them
from performing <code class="command">SELECT FOR SHARE</code>.
</p>
<p> It is currently not allowed for a single <code class="command">SELECT</code>
statement to include both <code class="literal">FOR UPDATE</code> and
<code class="literal">FOR SHARE</code>, nor can different parts of the statement use
both <code class="literal">NOWAIT</code> and normal waiting mode.
</p>
<p> If specific tables are named in <code class="literal">FOR UPDATE</code>
or <code class="literal">FOR SHARE</code>,
then only rows coming from those tables are locked; any other
tables used in the <code class="command">SELECT</code> are simply read as
usual.
</p>
<p> <code class="literal">FOR UPDATE</code> and <code class="literal">FOR SHARE</code> cannot be
used in contexts where returned rows can't be clearly identified with
individual table rows; for example they can't be used with aggregation.
</p>
<p> It is possible for a <code class="command">SELECT</code> command using both
<code class="literal">LIMIT</code> and <code class="literal">FOR UPDATE/SHARE</code>
clauses to return fewer rows than specified by <code class="literal">LIMIT</code>.
This is because <code class="literal">LIMIT</code> is applied first. The command
selects the specified number of rows,
but might then block trying to obtain lock on one or more of them.
Once the <code class="literal">SELECT</code> unblocks, the row might have been deleted
or updated so that it does not meet the query <code class="literal">WHERE</code> condition
anymore, in which case it will not be returned.
</p>
</div>
</div>
<div class="refsect1" lang="en">
<a name="id786144"></a><h2>Examples</h2>
<p> To join the table <code class="literal">films</code> with the table
<code class="literal">distributors</code>:
</p>
<pre class="programlisting">SELECT f.title, f.did, d.name, f.date_prod, f.kind
FROM distributors d, films f
WHERE f.did = d.did
title | did | name | date_prod | kind
-------------------+-----+--------------+------------+----------
The Third Man | 101 | British Lion | 1949-12-23 | Drama
The African Queen | 101 | British Lion | 1951-08-11 | Romantic
...</pre>
<p>
</p>
<p> To sum the column <code class="literal">len</code> of all films and group
the results by <code class="literal">kind</code>:
</p>
<pre class="programlisting">SELECT kind, sum(len) AS total FROM films GROUP BY kind;
kind | total
----------+-------
Action | 07:34
Comedy | 02:58
Drama | 14:28
Musical | 06:42
Romantic | 04:38</pre>
<p>
</p>
<p> To sum the column <code class="literal">len</code> of all films, group
the results by <code class="literal">kind</code> and show those group totals
that are less than 5 hours:
</p>
<pre class="programlisting">SELECT kind, sum(len) AS total
FROM films
GROUP BY kind
HAVING sum(len) < interval '5 hours';
kind | total
----------+-------
Comedy | 02:58
Romantic | 04:38</pre>
<p>
</p>
<p> The following two examples are identical ways of sorting the individual
results according to the contents of the second column
(<code class="literal">name</code>):
</p>
<pre class="programlisting">SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;
did | name
-----+------------------
109 | 20th Century Fox
110 | Bavaria Atelier
101 | British Lion
107 | Columbia
102 | Jean Luc Godard
113 | Luso films
104 | Mosfilm
103 | Paramount
106 | Toho
105 | United Artists
111 | Walt Disney
112 | Warner Bros.
108 | Westward</pre>
<p>
</p>
<p> The next example shows how to obtain the union of the tables
<code class="literal">distributors</code> and
<code class="literal">actors</code>, restricting the results to those that begin
with the letter W in each table. Only distinct rows are wanted, so the
key word <code class="literal">ALL</code> is omitted.
</p>
<pre class="programlisting">distributors: actors:
did | name id | name
-----+-------------- ----+----------------
108 | Westward 1 | Woody Allen
111 | Walt Disney 2 | Warren Beatty
112 | Warner Bros. 3 | Walter Matthau
... ...
SELECT distributors.name
FROM distributors
WHERE distributors.name LIKE 'W%'
UNION
SELECT actors.name
FROM actors
WHERE actors.name LIKE 'W%';
name
----------------
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody Allen</pre>
<p>
</p>
<p> This example shows how to use a function in the <code class="literal">FROM</code>
clause, both with and without a column definition list:
</p>
<pre class="programlisting">CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS $$
SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;
SELECT * FROM distributors(111);
did | name
-----+-------------
111 | Walt Disney
CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS $$
SELECT * FROM distributors WHERE did = $1;
$$ LANGUAGE SQL;
SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
f1 | f2
-----+-------------
111 | Walt Disney</pre>
<p>
</p>
</div>
<div class="refsect1" lang="en">
<a name="id786326"></a><h2>Compatibility</h2>
<p> Of course, the <code class="command">SELECT</code> statement is compatible
with the SQL standard. But there are some extensions and some
missing features.
</p>
<div class="refsect2" lang="en">
<a name="id786342"></a><h3>Omitted <code class="literal">FROM</code> Clauses</h3>
<p> <span class="productname">PostgreSQL</span> allows one to omit the
<code class="literal">FROM</code> clause. It has a straightforward use to
compute the results of simple expressions:
</p>
<pre class="programlisting">SELECT 2+2;
?column?
----------
4</pre>
<p>
Some other <acronym class="acronym">SQL</acronym> databases cannot do this except
by introducing a dummy one-row table from which to do the
<code class="command">SELECT</code>.
</p>
<p> Note that if a <code class="literal">FROM</code> clause is not specified,
the query cannot reference any database tables. For example, the
following query is invalid:
</p>
<pre class="programlisting">SELECT distributors.* WHERE distributors.name = 'Westward';</pre>
<p>
<span class="productname">PostgreSQL</span> releases prior to
8.1 would accept queries of this form, and add an implicit entry
to the query's <code class="literal">FROM</code> clause for each table
referenced by the query. This is no longer the default behavior,
because it does not comply with the SQL standard, and is
considered by many to be error-prone. For compatibility with
applications that rely on this behavior the <a href="runtime-config-compatible.html#guc-add-missing-from">add_missing_from</a> configuration variable can be
enabled.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id786437"></a><h3>The <code class="literal">AS</code> Key Word</h3>
<p> In the SQL standard, the optional key word <code class="literal">AS</code> is just
noise and can be omitted without affecting the meaning. The
<span class="productname">PostgreSQL</span> parser requires this key
word when renaming output columns because the type extensibility
features lead to parsing ambiguities without it.
<code class="literal">AS</code> is optional in <code class="literal">FROM</code>
items, however.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id786480"></a><h3>Namespace Available to <code class="literal">GROUP BY</code> and <code class="literal">ORDER BY</code>
</h3>
<p> In the SQL-92 standard, an <code class="literal">ORDER BY</code> clause may
only use result column names or numbers, while a <code class="literal">GROUP
BY</code> clause may only use expressions based on input column
names. <span class="productname">PostgreSQL</span> extends each of
these clauses to allow the other choice as well (but it uses the
standard's interpretation if there is ambiguity).
<span class="productname">PostgreSQL</span> also allows both clauses to
specify arbitrary expressions. Note that names appearing in an
expression will always be taken as input-column names, not as
result-column names.
</p>
<p> SQL:1999 and later use a slightly different definition which is not
entirely upward compatible with SQL-92.
In most cases, however, <span class="productname">PostgreSQL</span>
will interpret an <code class="literal">ORDER BY</code> or <code class="literal">GROUP
BY</code> expression the same way SQL:1999 does.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id786559"></a><h3>Nonstandard Clauses</h3>
<p> The clauses <code class="literal">DISTINCT ON</code>,
<code class="literal">LIMIT</code>, and <code class="literal">OFFSET</code> are not
defined in the SQL standard.
</p>
</div>
</div>
</div></body>
</html>
|
