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
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!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>7.2. Table Expressions</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="queries-overview.html" title="7.1. Overview" /><link rel="next" href="queries-select-lists.html" title="7.3. Select Lists" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">7.2. Table Expressions</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="queries-overview.html" title="7.1. Overview">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><th width="60%" align="center">Chapter 7. Queries</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="queries-select-lists.html" title="7.3. Select Lists">Next</a></td></tr></table><hr /></div><div class="sect1" id="QUERIES-TABLE-EXPRESSIONS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">7.2. Table Expressions</h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="queries-table-expressions.html#QUERIES-FROM">7.2.1. The <code class="literal">FROM</code> Clause</a></span></dt><dt><span class="sect2"><a href="queries-table-expressions.html#QUERIES-WHERE">7.2.2. The <code class="literal">WHERE</code> Clause</a></span></dt><dt><span class="sect2"><a href="queries-table-expressions.html#QUERIES-GROUP">7.2.3. The <code class="literal">GROUP BY</code> and <code class="literal">HAVING</code> Clauses</a></span></dt><dt><span class="sect2"><a href="queries-table-expressions.html#QUERIES-GROUPING-SETS">7.2.4. <code class="literal">GROUPING SETS</code>, <code class="literal">CUBE</code>, and <code class="literal">ROLLUP</code></a></span></dt><dt><span class="sect2"><a href="queries-table-expressions.html#QUERIES-WINDOW">7.2.5. Window Function Processing</a></span></dt></dl></div><a id="id-1.5.6.6.2" class="indexterm"></a><p>
A <em class="firstterm">table expression</em> computes a table. The
table expression contains a <code class="literal">FROM</code> clause that is
optionally followed by <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and
<code class="literal">HAVING</code> clauses. Trivial table expressions simply refer
to a table on disk, a so-called base table, but more complex
expressions can be used to modify or combine base tables in various
ways.
</p><p>
The optional <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and
<code class="literal">HAVING</code> clauses in the table expression specify a
pipeline of successive transformations performed on the table
derived in the <code class="literal">FROM</code> clause. All these transformations
produce a virtual table that provides the rows that are passed to
the select list to compute the output rows of the query.
</p><div class="sect2" id="QUERIES-FROM"><div class="titlepage"><div><div><h3 class="title">7.2.1. The <code class="literal">FROM</code> Clause</h3></div></div></div><p>
The <a class="link" href="sql-select.html#SQL-FROM" title="FROM Clause"><code class="literal">FROM</code></a> clause derives a
table from one or more other tables given in a comma-separated
table reference list.
</p><pre class="synopsis">
FROM <em class="replaceable"><code>table_reference</code></em> [<span class="optional">, <em class="replaceable"><code>table_reference</code></em> [<span class="optional">, ...</span>]</span>]
</pre><p>
A table reference can be a table name (possibly schema-qualified),
or a derived table such as a subquery, a <code class="literal">JOIN</code> construct, or
complex combinations of these. If more than one table reference is
listed in the <code class="literal">FROM</code> clause, the tables are cross-joined
(that is, the Cartesian product of their rows is formed; see below).
The result of the <code class="literal">FROM</code> list is an intermediate virtual
table that can then be subject to
transformations by the <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>,
and <code class="literal">HAVING</code> clauses and is finally the result of the
overall table expression.
</p><a id="id-1.5.6.6.5.3" class="indexterm"></a><p>
When a table reference names a table that is the parent of a
table inheritance hierarchy, the table reference produces rows of
not only that table but all of its descendant tables, unless the
key word <code class="literal">ONLY</code> precedes the table name. However, the
reference produces only the columns that appear in the named table
— any columns added in subtables are ignored.
</p><p>
Instead of writing <code class="literal">ONLY</code> before the table name, you can write
<code class="literal">*</code> after the table name to explicitly specify that descendant
tables are included. There is no real reason to use this syntax any more,
because searching descendant tables is now always the default behavior.
However, it is supported for compatibility with older releases.
</p><div class="sect3" id="QUERIES-JOIN"><div class="titlepage"><div><div><h4 class="title">7.2.1.1. Joined Tables</h4></div></div></div><a id="id-1.5.6.6.5.6.2" class="indexterm"></a><p>
A joined table is a table derived from two other (real or
derived) tables according to the rules of the particular join
type. Inner, outer, and cross-joins are available.
The general syntax of a joined table is
</p><pre class="synopsis">
<em class="replaceable"><code>T1</code></em> <em class="replaceable"><code>join_type</code></em> <em class="replaceable"><code>T2</code></em> [<span class="optional"> <em class="replaceable"><code>join_condition</code></em> </span>]
</pre><p>
Joins of all types can be chained together, or nested: either or
both <em class="replaceable"><code>T1</code></em> and
<em class="replaceable"><code>T2</code></em> can be joined tables. Parentheses
can be used around <code class="literal">JOIN</code> clauses to control the join
order. In the absence of parentheses, <code class="literal">JOIN</code> clauses
nest left-to-right.
</p><div class="variablelist"><p class="title"><strong>Join Types</strong></p><dl class="variablelist"><dt><span class="term">Cross join
<a id="id-1.5.6.6.5.6.4.2.1.1" class="indexterm"></a>
<a id="id-1.5.6.6.5.6.4.2.1.2" class="indexterm"></a>
</span></dt><dd><pre class="synopsis">
<em class="replaceable"><code>T1</code></em> CROSS JOIN <em class="replaceable"><code>T2</code></em>
</pre><p>
For every possible combination of rows from
<em class="replaceable"><code>T1</code></em> and
<em class="replaceable"><code>T2</code></em> (i.e., a Cartesian product),
the joined table will contain a
row consisting of all columns in <em class="replaceable"><code>T1</code></em>
followed by all columns in <em class="replaceable"><code>T2</code></em>. If
the tables have N and M rows respectively, the joined
table will have N * M rows.
</p><p>
<code class="literal">FROM <em class="replaceable"><code>T1</code></em> CROSS JOIN
<em class="replaceable"><code>T2</code></em></code> is equivalent to
<code class="literal">FROM <em class="replaceable"><code>T1</code></em> INNER JOIN
<em class="replaceable"><code>T2</code></em> ON TRUE</code> (see below).
It is also equivalent to
<code class="literal">FROM <em class="replaceable"><code>T1</code></em>,
<em class="replaceable"><code>T2</code></em></code>.
</p><div class="note"><h3 class="title">Note</h3><p>
This latter equivalence does not hold exactly when more than two
tables appear, because <code class="literal">JOIN</code> binds more tightly than
comma. For example
<code class="literal">FROM <em class="replaceable"><code>T1</code></em> CROSS JOIN
<em class="replaceable"><code>T2</code></em> INNER JOIN <em class="replaceable"><code>T3</code></em>
ON <em class="replaceable"><code>condition</code></em></code>
is not the same as
<code class="literal">FROM <em class="replaceable"><code>T1</code></em>,
<em class="replaceable"><code>T2</code></em> INNER JOIN <em class="replaceable"><code>T3</code></em>
ON <em class="replaceable"><code>condition</code></em></code>
because the <em class="replaceable"><code>condition</code></em> can
reference <em class="replaceable"><code>T1</code></em> in the first case but not
the second.
</p></div><p>
</p></dd><dt><span class="term">Qualified joins
<a id="id-1.5.6.6.5.6.4.3.1.1" class="indexterm"></a>
<a id="id-1.5.6.6.5.6.4.3.1.2" class="indexterm"></a>
</span></dt><dd><pre class="synopsis">
<em class="replaceable"><code>T1</code></em> { [<span class="optional">INNER</span>] | { LEFT | RIGHT | FULL } [<span class="optional">OUTER</span>] } JOIN <em class="replaceable"><code>T2</code></em> ON <em class="replaceable"><code>boolean_expression</code></em>
<em class="replaceable"><code>T1</code></em> { [<span class="optional">INNER</span>] | { LEFT | RIGHT | FULL } [<span class="optional">OUTER</span>] } JOIN <em class="replaceable"><code>T2</code></em> USING ( <em class="replaceable"><code>join column list</code></em> )
<em class="replaceable"><code>T1</code></em> NATURAL { [<span class="optional">INNER</span>] | { LEFT | RIGHT | FULL } [<span class="optional">OUTER</span>] } JOIN <em class="replaceable"><code>T2</code></em>
</pre><p>
The words <code class="literal">INNER</code> and
<code class="literal">OUTER</code> are optional in all forms.
<code class="literal">INNER</code> is the default;
<code class="literal">LEFT</code>, <code class="literal">RIGHT</code>, and
<code class="literal">FULL</code> imply an outer join.
</p><p>
The <em class="firstterm">join condition</em> is specified in the
<code class="literal">ON</code> or <code class="literal">USING</code> clause, or implicitly by
the word <code class="literal">NATURAL</code>. The join condition determines
which rows from the two source tables are considered to
<span class="quote">“<span class="quote">match</span>”</span>, as explained in detail below.
</p><p>
The possible types of qualified join are:
</p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">INNER JOIN</code></span></dt><dd><p>
For each row R1 of T1, the joined table has a row for each
row in T2 that satisfies the join condition with R1.
</p></dd><dt><span class="term"><code class="literal">LEFT OUTER JOIN</code>
<a id="id-1.5.6.6.5.6.4.3.2.4.1.2.1.2" class="indexterm"></a>
<a id="id-1.5.6.6.5.6.4.3.2.4.1.2.1.3" class="indexterm"></a>
</span></dt><dd><p>
First, an inner join is performed. Then, for each row in
T1 that does not satisfy the join condition with any row in
T2, a joined row is added with null values in columns of
T2. Thus, the joined table always has at least
one row for each row in T1.
</p></dd><dt><span class="term"><code class="literal">RIGHT OUTER JOIN</code>
<a id="id-1.5.6.6.5.6.4.3.2.4.1.3.1.2" class="indexterm"></a>
<a id="id-1.5.6.6.5.6.4.3.2.4.1.3.1.3" class="indexterm"></a>
</span></dt><dd><p>
First, an inner join is performed. Then, for each row in
T2 that does not satisfy the join condition with any row in
T1, a joined row is added with null values in columns of
T1. This is the converse of a left join: the result table
will always have a row for each row in T2.
</p></dd><dt><span class="term"><code class="literal">FULL OUTER JOIN</code></span></dt><dd><p>
First, an inner join is performed. Then, for each row in
T1 that does not satisfy the join condition with any row in
T2, a joined row is added with null values in columns of
T2. Also, for each row of T2 that does not satisfy the
join condition with any row in T1, a joined row with null
values in the columns of T1 is added.
</p></dd></dl></div><p>
</p><p>
The <code class="literal">ON</code> clause is the most general kind of join
condition: it takes a Boolean value expression of the same
kind as is used in a <code class="literal">WHERE</code> clause. A pair of rows
from <em class="replaceable"><code>T1</code></em> and <em class="replaceable"><code>T2</code></em> match if the
<code class="literal">ON</code> expression evaluates to true.
</p><p>
The <code class="literal">USING</code> clause is a shorthand that allows you to take
advantage of the specific situation where both sides of the join use
the same name for the joining column(s). It takes a
comma-separated list of the shared column names
and forms a join condition that includes an equality comparison
for each one. For example, joining <em class="replaceable"><code>T1</code></em>
and <em class="replaceable"><code>T2</code></em> with <code class="literal">USING (a, b)</code> produces
the join condition <code class="literal">ON <em class="replaceable"><code>T1</code></em>.a
= <em class="replaceable"><code>T2</code></em>.a AND <em class="replaceable"><code>T1</code></em>.b
= <em class="replaceable"><code>T2</code></em>.b</code>.
</p><p>
Furthermore, the output of <code class="literal">JOIN USING</code> suppresses
redundant columns: there is no need to print both of the matched
columns, since they must have equal values. While <code class="literal">JOIN
ON</code> produces all columns from <em class="replaceable"><code>T1</code></em> followed by all
columns from <em class="replaceable"><code>T2</code></em>, <code class="literal">JOIN USING</code> produces one
output column for each of the listed column pairs (in the listed
order), followed by any remaining columns from <em class="replaceable"><code>T1</code></em>,
followed by any remaining columns from <em class="replaceable"><code>T2</code></em>.
</p><p>
<a id="id-1.5.6.6.5.6.4.3.2.8.1" class="indexterm"></a>
<a id="id-1.5.6.6.5.6.4.3.2.8.2" class="indexterm"></a>
Finally, <code class="literal">NATURAL</code> is a shorthand form of
<code class="literal">USING</code>: it forms a <code class="literal">USING</code> list
consisting of all column names that appear in both
input tables. As with <code class="literal">USING</code>, these columns appear
only once in the output table. If there are no common
column names, <code class="literal">NATURAL JOIN</code> behaves like
<code class="literal">JOIN ... ON TRUE</code>, producing a cross-product join.
</p><div class="note"><h3 class="title">Note</h3><p>
<code class="literal">USING</code> is reasonably safe from column changes
in the joined relations since only the listed columns
are combined. <code class="literal">NATURAL</code> is considerably more risky since
any schema changes to either relation that cause a new matching
column name to be present will cause the join to combine that new
column as well.
</p></div></dd></dl></div><p>
To put this together, assume we have tables <code class="literal">t1</code>:
</p><pre class="programlisting">
num | name
-----+------
1 | a
2 | b
3 | c
</pre><p>
and <code class="literal">t2</code>:
</p><pre class="programlisting">
num | value
-----+-------
1 | xxx
3 | yyy
5 | zzz
</pre><p>
then we get the following results for the various joins:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 CROSS JOIN t2;</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
1 | a | 3 | yyy
1 | a | 5 | zzz
2 | b | 1 | xxx
2 | b | 3 | yyy
2 | b | 5 | zzz
3 | c | 1 | xxx
3 | c | 3 | yyy
3 | c | 5 | zzz
(9 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
(2 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 INNER JOIN t2 USING (num);</code></strong>
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 NATURAL INNER JOIN t2;</code></strong>
num | name | value
-----+------+-------
1 | a | xxx
3 | c | yyy
(2 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
(3 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 USING (num);</code></strong>
num | name | value
-----+------+-------
1 | a | xxx
2 | b |
3 | c | yyy
(3 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
3 | c | 3 | yyy
| | 5 | zzz
(3 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | 3 | yyy
| | 5 | zzz
(4 rows)
</pre><p>
</p><p>
The join condition specified with <code class="literal">ON</code> can also contain
conditions that do not relate directly to the join. This can
prove useful for some queries but needs to be thought out
carefully. For example:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
2 | b | |
3 | c | |
(3 rows)
</pre><p>
Notice that placing the restriction in the <code class="literal">WHERE</code> clause
produces a different result:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</code></strong>
num | name | num | value
-----+------+-----+-------
1 | a | 1 | xxx
(1 row)
</pre><p>
This is because a restriction placed in the <code class="literal">ON</code>
clause is processed <span class="emphasis"><em>before</em></span> the join, while
a restriction placed in the <code class="literal">WHERE</code> clause is processed
<span class="emphasis"><em>after</em></span> the join.
That does not matter with inner joins, but it matters a lot with outer
joins.
</p></div><div class="sect3" id="QUERIES-TABLE-ALIASES"><div class="titlepage"><div><div><h4 class="title">7.2.1.2. Table and Column Aliases</h4></div></div></div><a id="id-1.5.6.6.5.7.2" class="indexterm"></a><a id="id-1.5.6.6.5.7.3" class="indexterm"></a><p>
A temporary name can be given to tables and complex table
references to be used for references to the derived table in
the rest of the query. This is called a <em class="firstterm">table
alias</em>.
</p><p>
To create a table alias, write
</p><pre class="synopsis">
FROM <em class="replaceable"><code>table_reference</code></em> AS <em class="replaceable"><code>alias</code></em>
</pre><p>
or
</p><pre class="synopsis">
FROM <em class="replaceable"><code>table_reference</code></em> <em class="replaceable"><code>alias</code></em>
</pre><p>
The <code class="literal">AS</code> key word is optional noise.
<em class="replaceable"><code>alias</code></em> can be any identifier.
</p><p>
A typical application of table aliases is to assign short
identifiers to long table names to keep the join clauses
readable. For example:
</p><pre class="programlisting">
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
</pre><p>
</p><p>
The alias becomes the new name of the table reference so far as the
current query is concerned — it is not allowed to refer to the
table by the original name elsewhere in the query. Thus, this is not
valid:
</p><pre class="programlisting">
SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong
</pre><p>
</p><p>
Table aliases are mainly for notational convenience, but it is
necessary to use them when joining a table to itself, e.g.:
</p><pre class="programlisting">
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
</pre><p>
Additionally, an alias is required if the table reference is a
subquery (see <a class="xref" href="queries-table-expressions.html#QUERIES-SUBQUERIES" title="7.2.1.3. Subqueries">Section 7.2.1.3</a>).
</p><p>
Parentheses are used to resolve ambiguities. In the following example,
the first statement assigns the alias <code class="literal">b</code> to the second
instance of <code class="literal">my_table</code>, but the second statement assigns the
alias to the result of the join:
</p><pre class="programlisting">
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
</pre><p>
</p><p>
Another form of table aliasing gives temporary names to the columns of
the table, as well as the table itself:
</p><pre class="synopsis">
FROM <em class="replaceable"><code>table_reference</code></em> [<span class="optional">AS</span>] <em class="replaceable"><code>alias</code></em> ( <em class="replaceable"><code>column1</code></em> [<span class="optional">, <em class="replaceable"><code>column2</code></em> [<span class="optional">, ...</span>]</span>] )
</pre><p>
If fewer column aliases are specified than the actual table has
columns, the remaining columns are not renamed. This syntax is
especially useful for self-joins or subqueries.
</p><p>
When an alias is applied to the output of a <code class="literal">JOIN</code>
clause, the alias hides the original
name(s) within the <code class="literal">JOIN</code>. For example:
</p><pre class="programlisting">
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
</pre><p>
is valid SQL, but:
</p><pre class="programlisting">
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
</pre><p>
is not valid; the table alias <code class="literal">a</code> is not visible
outside the alias <code class="literal">c</code>.
</p></div><div class="sect3" id="QUERIES-SUBQUERIES"><div class="titlepage"><div><div><h4 class="title">7.2.1.3. Subqueries</h4></div></div></div><a id="id-1.5.6.6.5.8.2" class="indexterm"></a><p>
Subqueries specifying a derived table must be enclosed in
parentheses and <span class="emphasis"><em>must</em></span> be assigned a table
alias name (as in <a class="xref" href="queries-table-expressions.html#QUERIES-TABLE-ALIASES" title="7.2.1.2. Table and Column Aliases">Section 7.2.1.2</a>). For
example:
</p><pre class="programlisting">
FROM (SELECT * FROM table1) AS alias_name
</pre><p>
</p><p>
This example is equivalent to <code class="literal">FROM table1 AS
alias_name</code>. More interesting cases, which cannot be
reduced to a plain join, arise when the subquery involves
grouping or aggregation.
</p><p>
A subquery can also be a <code class="command">VALUES</code> list:
</p><pre class="programlisting">
FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
AS names(first, last)
</pre><p>
Again, a table alias is required. Assigning alias names to the columns
of the <code class="command">VALUES</code> list is optional, but is good practice.
For more information see <a class="xref" href="queries-values.html" title="7.7. VALUES Lists">Section 7.7</a>.
</p></div><div class="sect3" id="QUERIES-TABLEFUNCTIONS"><div class="titlepage"><div><div><h4 class="title">7.2.1.4. Table Functions</h4></div></div></div><a id="id-1.5.6.6.5.9.2" class="indexterm"></a><a id="id-1.5.6.6.5.9.3" class="indexterm"></a><p>
Table functions are functions that produce a set of rows, made up
of either base data types (scalar types) or composite data types
(table rows). They are used like a table, view, or subquery in
the <code class="literal">FROM</code> clause of a query. Columns returned by table
functions can be included in <code class="literal">SELECT</code>,
<code class="literal">JOIN</code>, or <code class="literal">WHERE</code> clauses in the same manner
as columns of a table, view, or subquery.
</p><p>
Table functions may also be combined using the <code class="literal">ROWS FROM</code>
syntax, with the results returned in parallel columns; the number of
result rows in this case is that of the largest function result, with
smaller results padded with null values to match.
</p><pre class="synopsis">
<em class="replaceable"><code>function_call</code></em> [<span class="optional">WITH ORDINALITY</span>] [<span class="optional">[<span class="optional">AS</span>] <em class="replaceable"><code>table_alias</code></em> [<span class="optional">(<em class="replaceable"><code>column_alias</code></em> [<span class="optional">, ... </span>])</span>]</span>]
ROWS FROM( <em class="replaceable"><code>function_call</code></em> [<span class="optional">, ... </span>] ) [<span class="optional">WITH ORDINALITY</span>] [<span class="optional">[<span class="optional">AS</span>] <em class="replaceable"><code>table_alias</code></em> [<span class="optional">(<em class="replaceable"><code>column_alias</code></em> [<span class="optional">, ... </span>])</span>]</span>]
</pre><p>
If the <code class="literal">WITH ORDINALITY</code> clause is specified, an
additional column of type <code class="type">bigint</code> will be added to the
function result columns. This column numbers the rows of the function
result set, starting from 1. (This is a generalization of the
SQL-standard syntax for <code class="literal">UNNEST ... WITH ORDINALITY</code>.)
By default, the ordinal column is called <code class="literal">ordinality</code>, but
a different column name can be assigned to it using
an <code class="literal">AS</code> clause.
</p><p>
The special table function <code class="literal">UNNEST</code> may be called with
any number of array parameters, and it returns a corresponding number of
columns, as if <code class="literal">UNNEST</code>
(<a class="xref" href="functions-array.html" title="9.19. Array Functions and Operators">Section 9.19</a>) had been called on each parameter
separately and combined using the <code class="literal">ROWS FROM</code> construct.
</p><pre class="synopsis">
UNNEST( <em class="replaceable"><code>array_expression</code></em> [<span class="optional">, ... </span>] ) [<span class="optional">WITH ORDINALITY</span>] [<span class="optional">[<span class="optional">AS</span>] <em class="replaceable"><code>table_alias</code></em> [<span class="optional">(<em class="replaceable"><code>column_alias</code></em> [<span class="optional">, ... </span>])</span>]</span>]
</pre><p>
If no <em class="replaceable"><code>table_alias</code></em> is specified, the function
name is used as the table name; in the case of a <code class="literal">ROWS FROM()</code>
construct, the first function's name is used.
</p><p>
If column aliases are not supplied, then for a function returning a base
data type, the column name is also the same as the function name. For a
function returning a composite type, the result columns get the names
of the individual attributes of the type.
</p><p>
Some examples:
</p><pre class="programlisting">
CREATE TABLE foo (fooid int, foosubid int, fooname text);
CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;
SELECT * FROM getfoo(1) AS t1;
SELECT * FROM foo
WHERE foosubid IN (
SELECT foosubid
FROM getfoo(foo.fooid) z
WHERE z.fooid = foo.fooid
);
CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
SELECT * FROM vw_getfoo;
</pre><p>
</p><p>
In some cases it is useful to define table functions that can
return different column sets depending on how they are invoked.
To support this, the table function can be declared as returning
the pseudo-type <code class="type">record</code> with no <code class="literal">OUT</code>
parameters. When such a function is used in
a query, the expected row structure must be specified in the
query itself, so that the system can know how to parse and plan
the query. This syntax looks like:
</p><pre class="synopsis">
<em class="replaceable"><code>function_call</code></em> [<span class="optional">AS</span>] <em class="replaceable"><code>alias</code></em> (<em class="replaceable"><code>column_definition</code></em> [<span class="optional">, ... </span>])
<em class="replaceable"><code>function_call</code></em> AS [<span class="optional"><em class="replaceable"><code>alias</code></em></span>] (<em class="replaceable"><code>column_definition</code></em> [<span class="optional">, ... </span>])
ROWS FROM( ... <em class="replaceable"><code>function_call</code></em> AS (<em class="replaceable"><code>column_definition</code></em> [<span class="optional">, ... </span>]) [<span class="optional">, ... </span>] )
</pre><p>
When not using the <code class="literal">ROWS FROM()</code> syntax,
the <em class="replaceable"><code>column_definition</code></em> list replaces the column
alias list that could otherwise be attached to the <code class="literal">FROM</code>
item; the names in the column definitions serve as column aliases.
When using the <code class="literal">ROWS FROM()</code> syntax,
a <em class="replaceable"><code>column_definition</code></em> list can be attached to
each member function separately; or if there is only one member function
and no <code class="literal">WITH ORDINALITY</code> clause,
a <em class="replaceable"><code>column_definition</code></em> list can be written in
place of a column alias list following <code class="literal">ROWS FROM()</code>.
</p><p>
Consider this example:
</p><pre class="programlisting">
SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text)
WHERE proname LIKE 'bytea%';
</pre><p>
The <a class="xref" href="contrib-dblink-function.html" title="dblink"><span class="refentrytitle">dblink</span></a> function
(part of the <a class="xref" href="dblink.html" title="F.12. dblink">dblink</a> module) executes
a remote query. It is declared to return
<code class="type">record</code> since it might be used for any kind of query.
The actual column set must be specified in the calling query so
that the parser knows, for example, what <code class="literal">*</code> should
expand to.
</p><p>
This example uses <code class="literal">ROWS FROM</code>:
</p><pre class="programlisting">
SELECT *
FROM ROWS FROM
(
json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
AS (a INTEGER, b TEXT),
generate_series(1, 3)
) AS x (p, q, s)
ORDER BY p;
p | q | s
-----+-----+---
40 | foo | 1
100 | bar | 2
| | 3
</pre><p>
It joins two functions into a single <code class="literal">FROM</code>
target. <code class="function">json_to_recordset()</code> is instructed
to return two columns, the first <code class="type">integer</code>
and the second <code class="type">text</code>. The result of
<code class="function">generate_series()</code> is used directly.
The <code class="literal">ORDER BY</code> clause sorts the column values
as integers.
</p></div><div class="sect3" id="QUERIES-LATERAL"><div class="titlepage"><div><div><h4 class="title">7.2.1.5. <code class="literal">LATERAL</code> Subqueries</h4></div></div></div><a id="id-1.5.6.6.5.10.2" class="indexterm"></a><p>
Subqueries appearing in <code class="literal">FROM</code> can be
preceded by the key word <code class="literal">LATERAL</code>. This allows them to
reference columns provided by preceding <code class="literal">FROM</code> items.
(Without <code class="literal">LATERAL</code>, each subquery is
evaluated independently and so cannot cross-reference any other
<code class="literal">FROM</code> item.)
</p><p>
Table functions appearing in <code class="literal">FROM</code> can also be
preceded by the key word <code class="literal">LATERAL</code>, but for functions the
key word is optional; the function's arguments can contain references
to columns provided by preceding <code class="literal">FROM</code> items in any case.
</p><p>
A <code class="literal">LATERAL</code> item can appear at the top level in the
<code class="literal">FROM</code> list, or within a <code class="literal">JOIN</code> tree. In the latter
case it can also refer to any items that are on the left-hand side of a
<code class="literal">JOIN</code> that it is on the right-hand side of.
</p><p>
When a <code class="literal">FROM</code> item contains <code class="literal">LATERAL</code>
cross-references, evaluation proceeds as follows: for each row of the
<code class="literal">FROM</code> item providing the cross-referenced column(s), or
set of rows of multiple <code class="literal">FROM</code> items providing the
columns, the <code class="literal">LATERAL</code> item is evaluated using that
row or row set's values of the columns. The resulting row(s) are
joined as usual with the rows they were computed from. This is
repeated for each row or set of rows from the column source table(s).
</p><p>
A trivial example of <code class="literal">LATERAL</code> is
</p><pre class="programlisting">
SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
</pre><p>
This is not especially useful since it has exactly the same result as
the more conventional
</p><pre class="programlisting">
SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
</pre><p>
<code class="literal">LATERAL</code> is primarily useful when the cross-referenced
column is necessary for computing the row(s) to be joined. A common
application is providing an argument value for a set-returning function.
For example, supposing that <code class="function">vertices(polygon)</code> returns the
set of vertices of a polygon, we could identify close-together vertices
of polygons stored in a table with:
</p><pre class="programlisting">
SELECT p1.id, p2.id, v1, v2
FROM polygons p1, polygons p2,
LATERAL vertices(p1.poly) v1,
LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
</pre><p>
This query could also be written
</p><pre class="programlisting">
SELECT p1.id, p2.id, v1, v2
FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
</pre><p>
or in several other equivalent formulations. (As already mentioned,
the <code class="literal">LATERAL</code> key word is unnecessary in this example, but
we use it for clarity.)
</p><p>
It is often particularly handy to <code class="literal">LEFT JOIN</code> to a
<code class="literal">LATERAL</code> subquery, so that source rows will appear in
the result even if the <code class="literal">LATERAL</code> subquery produces no
rows for them. For example, if <code class="function">get_product_names()</code> returns
the names of products made by a manufacturer, but some manufacturers in
our table currently produce no products, we could find out which ones
those are like this:
</p><pre class="programlisting">
SELECT m.name
FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
WHERE pname IS NULL;
</pre><p>
</p></div></div><div class="sect2" id="QUERIES-WHERE"><div class="titlepage"><div><div><h3 class="title">7.2.2. The <code class="literal">WHERE</code> Clause</h3></div></div></div><a id="id-1.5.6.6.6.2" class="indexterm"></a><p>
The syntax of the <a class="link" href="sql-select.html#SQL-WHERE" title="WHERE Clause"><code class="literal">WHERE</code></a>
clause is
</p><pre class="synopsis">
WHERE <em class="replaceable"><code>search_condition</code></em>
</pre><p>
where <em class="replaceable"><code>search_condition</code></em> is any value
expression (see <a class="xref" href="sql-expressions.html" title="4.2. Value Expressions">Section 4.2</a>) that
returns a value of type <code class="type">boolean</code>.
</p><p>
After the processing of the <code class="literal">FROM</code> clause is done, each
row of the derived virtual table is checked against the search
condition. If the result of the condition is true, the row is
kept in the output table, otherwise (i.e., if the result is
false or null) it is discarded. The search condition typically
references at least one column of the table generated in the
<code class="literal">FROM</code> clause; this is not required, but otherwise the
<code class="literal">WHERE</code> clause will be fairly useless.
</p><div class="note"><h3 class="title">Note</h3><p>
The join condition of an inner join can be written either in
the <code class="literal">WHERE</code> clause or in the <code class="literal">JOIN</code> clause.
For example, these table expressions are equivalent:
</p><pre class="programlisting">
FROM a, b WHERE a.id = b.id AND b.val > 5
</pre><p>
and:
</p><pre class="programlisting">
FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
</pre><p>
or perhaps even:
</p><pre class="programlisting">
FROM a NATURAL JOIN b WHERE b.val > 5
</pre><p>
Which one of these you use is mainly a matter of style. The
<code class="literal">JOIN</code> syntax in the <code class="literal">FROM</code> clause is
probably not as portable to other SQL database management systems,
even though it is in the SQL standard. For
outer joins there is no choice: they must be done in
the <code class="literal">FROM</code> clause. The <code class="literal">ON</code> or <code class="literal">USING</code>
clause of an outer join is <span class="emphasis"><em>not</em></span> equivalent to a
<code class="literal">WHERE</code> condition, because it results in the addition
of rows (for unmatched input rows) as well as the removal of rows
in the final result.
</p></div><p>
Here are some examples of <code class="literal">WHERE</code> clauses:
</p><pre class="programlisting">
SELECT ... FROM fdt WHERE c1 > 5
SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
</pre><p>
<code class="literal">fdt</code> is the table derived in the
<code class="literal">FROM</code> clause. Rows that do not meet the search
condition of the <code class="literal">WHERE</code> clause are eliminated from
<code class="literal">fdt</code>. Notice the use of scalar subqueries as
value expressions. Just like any other query, the subqueries can
employ complex table expressions. Notice also how
<code class="literal">fdt</code> is referenced in the subqueries.
Qualifying <code class="literal">c1</code> as <code class="literal">fdt.c1</code> is only necessary
if <code class="literal">c1</code> is also the name of a column in the derived
input table of the subquery. But qualifying the column name adds
clarity even when it is not needed. This example shows how the column
naming scope of an outer query extends into its inner queries.
</p></div><div class="sect2" id="QUERIES-GROUP"><div class="titlepage"><div><div><h3 class="title">7.2.3. The <code class="literal">GROUP BY</code> and <code class="literal">HAVING</code> Clauses</h3></div></div></div><a id="id-1.5.6.6.7.2" class="indexterm"></a><a id="id-1.5.6.6.7.3" class="indexterm"></a><p>
After passing the <code class="literal">WHERE</code> filter, the derived input
table might be subject to grouping, using the <code class="literal">GROUP BY</code>
clause, and elimination of group rows using the <code class="literal">HAVING</code>
clause.
</p><pre class="synopsis">
SELECT <em class="replaceable"><code>select_list</code></em>
FROM ...
[<span class="optional">WHERE ...</span>]
GROUP BY <em class="replaceable"><code>grouping_column_reference</code></em> [<span class="optional">, <em class="replaceable"><code>grouping_column_reference</code></em></span>]...
</pre><p>
The <a class="link" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause"><code class="literal">GROUP BY</code></a> clause is
used to group together those rows in a table that have the same
values in all the columns listed. The order in which the columns
are listed does not matter. The effect is to combine each set
of rows having common values into one group row that
represents all rows in the group. This is done to
eliminate redundancy in the output and/or compute aggregates that
apply to these groups. For instance:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM test1;</code></strong>
x | y
---+---
a | 3
c | 2
b | 5
a | 1
(4 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT x FROM test1 GROUP BY x;</code></strong>
x
---
a
b
c
(3 rows)
</pre><p>
</p><p>
In the second query, we could not have written <code class="literal">SELECT *
FROM test1 GROUP BY x</code>, because there is no single value
for the column <code class="literal">y</code> that could be associated with each
group. The grouped-by columns can be referenced in the select list since
they have a single value in each group.
</p><p>
In general, if a table is grouped, columns that are not
listed in <code class="literal">GROUP BY</code> cannot be referenced except in aggregate
expressions. An example with aggregate expressions is:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x;</code></strong>
x | sum
---+-----
a | 4
b | 5
c | 2
(3 rows)
</pre><p>
Here <code class="literal">sum</code> is an aggregate function that
computes a single value over the entire group. More information
about the available aggregate functions can be found in <a class="xref" href="functions-aggregate.html" title="9.21. Aggregate Functions">Section 9.21</a>.
</p><div class="tip"><h3 class="title">Tip</h3><p>
Grouping without aggregate expressions effectively calculates the
set of distinct values in a column. This can also be achieved
using the <code class="literal">DISTINCT</code> clause (see <a class="xref" href="queries-select-lists.html#QUERIES-DISTINCT" title="7.3.3. DISTINCT">Section 7.3.3</a>).
</p></div><p>
Here is another example: it calculates the total sales for each
product (rather than the total sales of all products):
</p><pre class="programlisting">
SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
FROM products p LEFT JOIN sales s USING (product_id)
GROUP BY product_id, p.name, p.price;
</pre><p>
In this example, the columns <code class="literal">product_id</code>,
<code class="literal">p.name</code>, and <code class="literal">p.price</code> must be
in the <code class="literal">GROUP BY</code> clause since they are referenced in
the query select list (but see below). The column
<code class="literal">s.units</code> does not have to be in the <code class="literal">GROUP
BY</code> list since it is only used in an aggregate expression
(<code class="literal">sum(...)</code>), which represents the sales
of a product. For each product, the query returns a summary row about
all sales of the product.
</p><a id="id-1.5.6.6.7.11" class="indexterm"></a><p>
If the products table is set up so that, say,
<code class="literal">product_id</code> is the primary key, then it would be
enough to group by <code class="literal">product_id</code> in the above example,
since name and price would be <em class="firstterm">functionally
dependent</em> on the product ID, and so there would be no
ambiguity about which name and price value to return for each product
ID group.
</p><p>
In strict SQL, <code class="literal">GROUP BY</code> can only group by columns of
the source table but <span class="productname">PostgreSQL</span> extends
this to also allow <code class="literal">GROUP BY</code> to group by columns in the
select list. Grouping by value expressions instead of simple
column names is also allowed.
</p><a id="id-1.5.6.6.7.14" class="indexterm"></a><p>
If a table has been grouped using <code class="literal">GROUP BY</code>,
but only certain groups are of interest, the
<code class="literal">HAVING</code> clause can be used, much like a
<code class="literal">WHERE</code> clause, to eliminate groups from the result.
The syntax is:
</p><pre class="synopsis">
SELECT <em class="replaceable"><code>select_list</code></em> FROM ... [<span class="optional">WHERE ...</span>] GROUP BY ... HAVING <em class="replaceable"><code>boolean_expression</code></em>
</pre><p>
Expressions in the <code class="literal">HAVING</code> clause can refer both to
grouped expressions and to ungrouped expressions (which necessarily
involve an aggregate function).
</p><p>
Example:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;</code></strong>
x | sum
---+-----
a | 4
b | 5
(2 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';</code></strong>
x | sum
---+-----
a | 4
b | 5
(2 rows)
</pre><p>
</p><p>
Again, a more realistic example:
</p><pre class="programlisting">
SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
FROM products p LEFT JOIN sales s USING (product_id)
WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
GROUP BY product_id, p.name, p.price, p.cost
HAVING sum(p.price * s.units) > 5000;
</pre><p>
In the example above, the <code class="literal">WHERE</code> clause is selecting
rows by a column that is not grouped (the expression is only true for
sales during the last four weeks), while the <code class="literal">HAVING</code>
clause restricts the output to groups with total gross sales over
5000. Note that the aggregate expressions do not necessarily need
to be the same in all parts of the query.
</p><p>
If a query contains aggregate function calls, but no <code class="literal">GROUP BY</code>
clause, grouping still occurs: the result is a single group row (or
perhaps no rows at all, if the single row is then eliminated by
<code class="literal">HAVING</code>).
The same is true if it contains a <code class="literal">HAVING</code> clause, even
without any aggregate function calls or <code class="literal">GROUP BY</code> clause.
</p></div><div class="sect2" id="QUERIES-GROUPING-SETS"><div class="titlepage"><div><div><h3 class="title">7.2.4. <code class="literal">GROUPING SETS</code>, <code class="literal">CUBE</code>, and <code class="literal">ROLLUP</code></h3></div></div></div><a id="id-1.5.6.6.8.2" class="indexterm"></a><a id="id-1.5.6.6.8.3" class="indexterm"></a><a id="id-1.5.6.6.8.4" class="indexterm"></a><p>
More complex grouping operations than those described above are possible
using the concept of <em class="firstterm">grouping sets</em>. The data selected by
the <code class="literal">FROM</code> and <code class="literal">WHERE</code> clauses is grouped separately
by each specified grouping set, aggregates computed for each group just as
for simple <code class="literal">GROUP BY</code> clauses, and then the results returned.
For example:
</p><pre class="screen">
<code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM items_sold;</code></strong>
brand | size | sales
-------+------+-------
Foo | L | 10
Foo | M | 20
Bar | M | 15
Bar | L | 5
(4 rows)
<code class="prompt">=></code> <strong class="userinput"><code>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</code></strong>
brand | size | sum
-------+------+-----
Foo | | 30
Bar | | 20
| L | 15
| M | 35
| | 50
(5 rows)
</pre><p>
</p><p>
Each sublist of <code class="literal">GROUPING SETS</code> may specify zero or more columns
or expressions and is interpreted the same way as though it were directly
in the <code class="literal">GROUP BY</code> clause. An empty grouping set means that all
rows are aggregated down to a single group (which is output even if no
input rows were present), as described above for the case of aggregate
functions with no <code class="literal">GROUP BY</code> clause.
</p><p>
References to the grouping columns or expressions are replaced
by null values in result rows for grouping sets in which those
columns do not appear. To distinguish which grouping a particular output
row resulted from, see <a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.62. Grouping Operations">Table 9.62</a>.
</p><p>
A shorthand notation is provided for specifying two common types of grouping set.
A clause of the form
</p><pre class="programlisting">
ROLLUP ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, <em class="replaceable"><code>e3</code></em>, ... )
</pre><p>
represents the given list of expressions and all prefixes of the list including
the empty list; thus it is equivalent to
</p><pre class="programlisting">
GROUPING SETS (
( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, <em class="replaceable"><code>e3</code></em>, ... ),
...
( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em> ),
( <em class="replaceable"><code>e1</code></em> ),
( )
)
</pre><p>
This is commonly used for analysis over hierarchical data; e.g., total
salary by department, division, and company-wide total.
</p><p>
A clause of the form
</p><pre class="programlisting">
CUBE ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, ... )
</pre><p>
represents the given list and all of its possible subsets (i.e., the power
set). Thus
</p><pre class="programlisting">
CUBE ( a, b, c )
</pre><p>
is equivalent to
</p><pre class="programlisting">
GROUPING SETS (
( a, b, c ),
( a, b ),
( a, c ),
( a ),
( b, c ),
( b ),
( c ),
( )
)
</pre><p>
</p><p>
The individual elements of a <code class="literal">CUBE</code> or <code class="literal">ROLLUP</code>
clause may be either individual expressions, or sublists of elements in
parentheses. In the latter case, the sublists are treated as single
units for the purposes of generating the individual grouping sets.
For example:
</p><pre class="programlisting">
CUBE ( (a, b), (c, d) )
</pre><p>
is equivalent to
</p><pre class="programlisting">
GROUPING SETS (
( a, b, c, d ),
( a, b ),
( c, d ),
( )
)
</pre><p>
and
</p><pre class="programlisting">
ROLLUP ( a, (b, c), d )
</pre><p>
is equivalent to
</p><pre class="programlisting">
GROUPING SETS (
( a, b, c, d ),
( a, b, c ),
( a ),
( )
)
</pre><p>
</p><p>
The <code class="literal">CUBE</code> and <code class="literal">ROLLUP</code> constructs can be used either
directly in the <code class="literal">GROUP BY</code> clause, or nested inside a
<code class="literal">GROUPING SETS</code> clause. If one <code class="literal">GROUPING SETS</code> clause
is nested inside another, the effect is the same as if all the elements of
the inner clause had been written directly in the outer clause.
</p><p>
If multiple grouping items are specified in a single <code class="literal">GROUP BY</code>
clause, then the final list of grouping sets is the cross product of the
individual items. For example:
</p><pre class="programlisting">
GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
</pre><p>
is equivalent to
</p><pre class="programlisting">
GROUP BY GROUPING SETS (
(a, b, c, d), (a, b, c, e),
(a, b, d), (a, b, e),
(a, c, d), (a, c, e),
(a, d), (a, e)
)
</pre><p>
</p><p>
<a id="id-1.5.6.6.8.13.1" class="indexterm"></a>
<a id="id-1.5.6.6.8.13.2" class="indexterm"></a>
When specifying multiple grouping items together, the final set of grouping
sets might contain duplicates. For example:
</p><pre class="programlisting">
GROUP BY ROLLUP (a, b), ROLLUP (a, c)
</pre><p>
is equivalent to
</p><pre class="programlisting">
GROUP BY GROUPING SETS (
(a, b, c),
(a, b),
(a, b),
(a, c),
(a),
(a),
(a, c),
(a),
()
)
</pre><p>
If these duplicates are undesirable, they can be removed using the
<code class="literal">DISTINCT</code> clause directly on the <code class="literal">GROUP BY</code>.
Therefore:
</p><pre class="programlisting">
GROUP BY <span class="emphasis"><strong>DISTINCT</strong></span> ROLLUP (a, b), ROLLUP (a, c)
</pre><p>
is equivalent to
</p><pre class="programlisting">
GROUP BY GROUPING SETS (
(a, b, c),
(a, b),
(a, c),
(a),
()
)
</pre><p>
This is not the same as using <code class="literal">SELECT DISTINCT</code> because the output
rows may still contain duplicates. If any of the ungrouped columns contains NULL,
it will be indistinguishable from the NULL used when that same column is grouped.
</p><div class="note"><h3 class="title">Note</h3><p>
The construct <code class="literal">(a, b)</code> is normally recognized in expressions as
a <a class="link" href="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" title="4.2.13. Row Constructors">row constructor</a>.
Within the <code class="literal">GROUP BY</code> clause, this does not apply at the top
levels of expressions, and <code class="literal">(a, b)</code> is parsed as a list of
expressions as described above. If for some reason you <span class="emphasis"><em>need</em></span>
a row constructor in a grouping expression, use <code class="literal">ROW(a, b)</code>.
</p></div></div><div class="sect2" id="QUERIES-WINDOW"><div class="titlepage"><div><div><h3 class="title">7.2.5. Window Function Processing</h3></div></div></div><a id="id-1.5.6.6.9.2" class="indexterm"></a><p>
If the query contains any window functions (see
<a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a>,
<a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a> and
<a class="xref" href="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" title="4.2.8. Window Function Calls">Section 4.2.8</a>), these functions are evaluated
after any grouping, aggregation, and <code class="literal">HAVING</code> filtering is
performed. That is, if the query uses any aggregates, <code class="literal">GROUP
BY</code>, or <code class="literal">HAVING</code>, then the rows seen by the window functions
are the group rows instead of the original table rows from
<code class="literal">FROM</code>/<code class="literal">WHERE</code>.
</p><p>
When multiple window functions are used, all the window functions having
syntactically equivalent <code class="literal">PARTITION BY</code> and <code class="literal">ORDER BY</code>
clauses in their window definitions are guaranteed to be evaluated in a
single pass over the data. Therefore they will see the same sort ordering,
even if the <code class="literal">ORDER BY</code> does not uniquely determine an ordering.
However, no guarantees are made about the evaluation of functions having
different <code class="literal">PARTITION BY</code> or <code class="literal">ORDER BY</code> specifications.
(In such cases a sort step is typically required between the passes of
window function evaluations, and the sort is not guaranteed to preserve
ordering of rows that its <code class="literal">ORDER BY</code> sees as equivalent.)
</p><p>
Currently, window functions always require presorted data, and so the
query output will be ordered according to one or another of the window
functions' <code class="literal">PARTITION BY</code>/<code class="literal">ORDER BY</code> clauses.
It is not recommended to rely on this, however. Use an explicit
top-level <code class="literal">ORDER BY</code> clause if you want to be sure the
results are sorted in a particular way.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="queries-overview.html" title="7.1. Overview">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="queries.html" title="Chapter 7. Queries">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="queries-select-lists.html" title="7.3. Select Lists">Next</a></td></tr><tr><td width="40%" align="left" valign="top">7.1. Overview </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 7.3. Select Lists</td></tr></table></div></body></html>
|
