1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
2 <!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 18.0 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 <a href="#QUERIES-TABLE-EXPRESSIONS" class="id_link">#</a></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>
3 A <em class="firstterm">table expression</em> computes a table. The
4 table expression contains a <code class="literal">FROM</code> clause that is
5 optionally followed by <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and
6 <code class="literal">HAVING</code> clauses. Trivial table expressions simply refer
7 to a table on disk, a so-called base table, but more complex
8 expressions can be used to modify or combine base tables in various
11 The optional <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and
12 <code class="literal">HAVING</code> clauses in the table expression specify a
13 pipeline of successive transformations performed on the table
14 derived in the <code class="literal">FROM</code> clause. All these transformations
15 produce a virtual table that provides the rows that are passed to
16 the select list to compute the output rows of the query.
17 </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 <a href="#QUERIES-FROM" class="id_link">#</a></h3></div></div></div><p>
18 The <a class="link" href="sql-select.html#SQL-FROM" title="FROM Clause"><code class="literal">FROM</code></a> clause derives a
19 table from one or more other tables given in a comma-separated
21 </p><pre class="synopsis">
22 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>]
25 A table reference can be a table name (possibly schema-qualified),
26 or a derived table such as a subquery, a <code class="literal">JOIN</code> construct, or
27 complex combinations of these. If more than one table reference is
28 listed in the <code class="literal">FROM</code> clause, the tables are cross-joined
29 (that is, the Cartesian product of their rows is formed; see below).
30 The result of the <code class="literal">FROM</code> list is an intermediate virtual
31 table that can then be subject to
32 transformations by the <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>,
33 and <code class="literal">HAVING</code> clauses and is finally the result of the
34 overall table expression.
35 </p><a id="id-1.5.6.6.5.3" class="indexterm"></a><p>
36 When a table reference names a table that is the parent of a
37 table inheritance hierarchy, the table reference produces rows of
38 not only that table but all of its descendant tables, unless the
39 key word <code class="literal">ONLY</code> precedes the table name. However, the
40 reference produces only the columns that appear in the named table
41 — any columns added in subtables are ignored.
43 Instead of writing <code class="literal">ONLY</code> before the table name, you can write
44 <code class="literal">*</code> after the table name to explicitly specify that descendant
45 tables are included. There is no real reason to use this syntax any more,
46 because searching descendant tables is now always the default behavior.
47 However, it is supported for compatibility with older releases.
48 </p><div class="sect3" id="QUERIES-JOIN"><div class="titlepage"><div><div><h4 class="title">7.2.1.1. Joined Tables <a href="#QUERIES-JOIN" class="id_link">#</a></h4></div></div></div><a id="id-1.5.6.6.5.6.2" class="indexterm"></a><p>
49 A joined table is a table derived from two other (real or
50 derived) tables according to the rules of the particular join
51 type. Inner, outer, and cross-joins are available.
52 The general syntax of a joined table is
53 </p><pre class="synopsis">
54 <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>]
56 Joins of all types can be chained together, or nested: either or
57 both <em class="replaceable"><code>T1</code></em> and
58 <em class="replaceable"><code>T2</code></em> can be joined tables. Parentheses
59 can be used around <code class="literal">JOIN</code> clauses to control the join
60 order. In the absence of parentheses, <code class="literal">JOIN</code> clauses
62 </p><div class="variablelist"><p class="title"><strong>Join Types</strong></p><dl class="variablelist"><dt><span class="term">Cross join
63 <a id="id-1.5.6.6.5.6.4.2.1.1" class="indexterm"></a>
65 <a id="id-1.5.6.6.5.6.4.2.1.2" class="indexterm"></a>
66 </span></dt><dd><pre class="synopsis">
67 <em class="replaceable"><code>T1</code></em> CROSS JOIN <em class="replaceable"><code>T2</code></em>
69 For every possible combination of rows from
70 <em class="replaceable"><code>T1</code></em> and
71 <em class="replaceable"><code>T2</code></em> (i.e., a Cartesian product),
72 the joined table will contain a
73 row consisting of all columns in <em class="replaceable"><code>T1</code></em>
74 followed by all columns in <em class="replaceable"><code>T2</code></em>. If
75 the tables have N and M rows respectively, the joined
76 table will have N * M rows.
78 <code class="literal">FROM <em class="replaceable"><code>T1</code></em> CROSS JOIN
79 <em class="replaceable"><code>T2</code></em></code> is equivalent to
80 <code class="literal">FROM <em class="replaceable"><code>T1</code></em> INNER JOIN
81 <em class="replaceable"><code>T2</code></em> ON TRUE</code> (see below).
82 It is also equivalent to
83 <code class="literal">FROM <em class="replaceable"><code>T1</code></em>,
84 <em class="replaceable"><code>T2</code></em></code>.
85 </p><div class="note"><h3 class="title">Note</h3><p>
86 This latter equivalence does not hold exactly when more than two
87 tables appear, because <code class="literal">JOIN</code> binds more tightly than
89 <code class="literal">FROM <em class="replaceable"><code>T1</code></em> CROSS JOIN
90 <em class="replaceable"><code>T2</code></em> INNER JOIN <em class="replaceable"><code>T3</code></em>
91 ON <em class="replaceable"><code>condition</code></em></code>
93 <code class="literal">FROM <em class="replaceable"><code>T1</code></em>,
94 <em class="replaceable"><code>T2</code></em> INNER JOIN <em class="replaceable"><code>T3</code></em>
95 ON <em class="replaceable"><code>condition</code></em></code>
96 because the <em class="replaceable"><code>condition</code></em> can
97 reference <em class="replaceable"><code>T1</code></em> in the first case but not
100 </p></dd><dt><span class="term">Qualified joins
101 <a id="id-1.5.6.6.5.6.4.3.1.1" class="indexterm"></a>
103 <a id="id-1.5.6.6.5.6.4.3.1.2" class="indexterm"></a>
104 </span></dt><dd><pre class="synopsis">
105 <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>
106 <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> )
107 <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>
109 The words <code class="literal">INNER</code> and
110 <code class="literal">OUTER</code> are optional in all forms.
111 <code class="literal">INNER</code> is the default;
112 <code class="literal">LEFT</code>, <code class="literal">RIGHT</code>, and
113 <code class="literal">FULL</code> imply an outer join.
115 The <em class="firstterm">join condition</em> is specified in the
116 <code class="literal">ON</code> or <code class="literal">USING</code> clause, or implicitly by
117 the word <code class="literal">NATURAL</code>. The join condition determines
118 which rows from the two source tables are considered to
119 <span class="quote">“<span class="quote">match</span>”</span>, as explained in detail below.
121 The possible types of qualified join are:
123 </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">INNER JOIN</code></span></dt><dd><p>
124 For each row R1 of T1, the joined table has a row for each
125 row in T2 that satisfies the join condition with R1.
126 </p></dd><dt><span class="term"><code class="literal">LEFT OUTER JOIN</code>
127 <a id="id-1.5.6.6.5.6.4.3.2.4.1.2.1.2" class="indexterm"></a>
129 <a id="id-1.5.6.6.5.6.4.3.2.4.1.2.1.3" class="indexterm"></a>
131 First, an inner join is performed. Then, for each row in
132 T1 that does not satisfy the join condition with any row in
133 T2, a joined row is added with null values in columns of
134 T2. Thus, the joined table always has at least
135 one row for each row in T1.
136 </p></dd><dt><span class="term"><code class="literal">RIGHT OUTER JOIN</code>
137 <a id="id-1.5.6.6.5.6.4.3.2.4.1.3.1.2" class="indexterm"></a>
139 <a id="id-1.5.6.6.5.6.4.3.2.4.1.3.1.3" class="indexterm"></a>
141 First, an inner join is performed. Then, for each row in
142 T2 that does not satisfy the join condition with any row in
143 T1, a joined row is added with null values in columns of
144 T1. This is the converse of a left join: the result table
145 will always have a row for each row in T2.
146 </p></dd><dt><span class="term"><code class="literal">FULL OUTER JOIN</code></span></dt><dd><p>
147 First, an inner join is performed. Then, for each row in
148 T1 that does not satisfy the join condition with any row in
149 T2, a joined row is added with null values in columns of
150 T2. Also, for each row of T2 that does not satisfy the
151 join condition with any row in T1, a joined row with null
152 values in the columns of T1 is added.
153 </p></dd></dl></div><p>
155 The <code class="literal">ON</code> clause is the most general kind of join
156 condition: it takes a Boolean value expression of the same
157 kind as is used in a <code class="literal">WHERE</code> clause. A pair of rows
158 from <em class="replaceable"><code>T1</code></em> and <em class="replaceable"><code>T2</code></em> match if the
159 <code class="literal">ON</code> expression evaluates to true.
161 The <code class="literal">USING</code> clause is a shorthand that allows you to take
162 advantage of the specific situation where both sides of the join use
163 the same name for the joining column(s). It takes a
164 comma-separated list of the shared column names
165 and forms a join condition that includes an equality comparison
166 for each one. For example, joining <em class="replaceable"><code>T1</code></em>
167 and <em class="replaceable"><code>T2</code></em> with <code class="literal">USING (a, b)</code> produces
168 the join condition <code class="literal">ON <em class="replaceable"><code>T1</code></em>.a
169 = <em class="replaceable"><code>T2</code></em>.a AND <em class="replaceable"><code>T1</code></em>.b
170 = <em class="replaceable"><code>T2</code></em>.b</code>.
172 Furthermore, the output of <code class="literal">JOIN USING</code> suppresses
173 redundant columns: there is no need to print both of the matched
174 columns, since they must have equal values. While <code class="literal">JOIN
175 ON</code> produces all columns from <em class="replaceable"><code>T1</code></em> followed by all
176 columns from <em class="replaceable"><code>T2</code></em>, <code class="literal">JOIN USING</code> produces one
177 output column for each of the listed column pairs (in the listed
178 order), followed by any remaining columns from <em class="replaceable"><code>T1</code></em>,
179 followed by any remaining columns from <em class="replaceable"><code>T2</code></em>.
181 <a id="id-1.5.6.6.5.6.4.3.2.8.1" class="indexterm"></a>
182 <a id="id-1.5.6.6.5.6.4.3.2.8.2" class="indexterm"></a>
183 Finally, <code class="literal">NATURAL</code> is a shorthand form of
184 <code class="literal">USING</code>: it forms a <code class="literal">USING</code> list
185 consisting of all column names that appear in both
186 input tables. As with <code class="literal">USING</code>, these columns appear
187 only once in the output table. If there are no common
188 column names, <code class="literal">NATURAL JOIN</code> behaves like
189 <code class="literal">CROSS JOIN</code>.
190 </p><div class="note"><h3 class="title">Note</h3><p>
191 <code class="literal">USING</code> is reasonably safe from column changes
192 in the joined relations since only the listed columns
193 are combined. <code class="literal">NATURAL</code> is considerably more risky since
194 any schema changes to either relation that cause a new matching
195 column name to be present will cause the join to combine that new
197 </p></div></dd></dl></div><p>
198 To put this together, assume we have tables <code class="literal">t1</code>:
199 </p><pre class="programlisting">
206 and <code class="literal">t2</code>:
207 </p><pre class="programlisting">
214 then we get the following results for the various joins:
215 </p><pre class="screen">
216 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 CROSS JOIN t2;</code></strong>
217 num | name | num | value
218 -----+------+-----+-------
230 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</code></strong>
231 num | name | num | value
232 -----+------+-----+-------
237 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 INNER JOIN t2 USING (num);</code></strong>
244 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 NATURAL INNER JOIN t2;</code></strong>
251 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</code></strong>
252 num | name | num | value
253 -----+------+-----+-------
259 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 USING (num);</code></strong>
267 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</code></strong>
268 num | name | num | value
269 -----+------+-----+-------
275 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</code></strong>
276 num | name | num | value
277 -----+------+-----+-------
285 The join condition specified with <code class="literal">ON</code> can also contain
286 conditions that do not relate directly to the join. This can
287 prove useful for some queries but needs to be thought out
288 carefully. For example:
289 </p><pre class="screen">
290 <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>
291 num | name | num | value
292 -----+------+-----+-------
298 Notice that placing the restriction in the <code class="literal">WHERE</code> clause
299 produces a different result:
300 </p><pre class="screen">
301 <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>
302 num | name | num | value
303 -----+------+-----+-------
307 This is because a restriction placed in the <code class="literal">ON</code>
308 clause is processed <span class="emphasis"><em>before</em></span> the join, while
309 a restriction placed in the <code class="literal">WHERE</code> clause is processed
310 <span class="emphasis"><em>after</em></span> the join.
311 That does not matter with inner joins, but it matters a lot with outer
313 </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 <a href="#QUERIES-TABLE-ALIASES" class="id_link">#</a></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>
314 A temporary name can be given to tables and complex table
315 references to be used for references to the derived table in
316 the rest of the query. This is called a <em class="firstterm">table
319 To create a table alias, write
320 </p><pre class="synopsis">
321 FROM <em class="replaceable"><code>table_reference</code></em> AS <em class="replaceable"><code>alias</code></em>
324 </p><pre class="synopsis">
325 FROM <em class="replaceable"><code>table_reference</code></em> <em class="replaceable"><code>alias</code></em>
327 The <code class="literal">AS</code> key word is optional noise.
328 <em class="replaceable"><code>alias</code></em> can be any identifier.
330 A typical application of table aliases is to assign short
331 identifiers to long table names to keep the join clauses
332 readable. For example:
333 </p><pre class="programlisting">
334 SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
337 The alias becomes the new name of the table reference so far as the
338 current query is concerned — it is not allowed to refer to the
339 table by the original name elsewhere in the query. Thus, this is not
341 </p><pre class="programlisting">
342 SELECT * FROM my_table AS m WHERE my_table.a > 5; -- wrong
345 Table aliases are mainly for notational convenience, but it is
346 necessary to use them when joining a table to itself, e.g.:
347 </p><pre class="programlisting">
348 SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;
351 Parentheses are used to resolve ambiguities. In the following example,
352 the first statement assigns the alias <code class="literal">b</code> to the second
353 instance of <code class="literal">my_table</code>, but the second statement assigns the
354 alias to the result of the join:
355 </p><pre class="programlisting">
356 SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
357 SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
360 Another form of table aliasing gives temporary names to the columns of
361 the table, as well as the table itself:
362 </p><pre class="synopsis">
363 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>] )
365 If fewer column aliases are specified than the actual table has
366 columns, the remaining columns are not renamed. This syntax is
367 especially useful for self-joins or subqueries.
369 When an alias is applied to the output of a <code class="literal">JOIN</code>
370 clause, the alias hides the original
371 name(s) within the <code class="literal">JOIN</code>. For example:
372 </p><pre class="programlisting">
373 SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
376 </p><pre class="programlisting">
377 SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
379 is not valid; the table alias <code class="literal">a</code> is not visible
380 outside the alias <code class="literal">c</code>.
381 </p></div><div class="sect3" id="QUERIES-SUBQUERIES"><div class="titlepage"><div><div><h4 class="title">7.2.1.3. Subqueries <a href="#QUERIES-SUBQUERIES" class="id_link">#</a></h4></div></div></div><a id="id-1.5.6.6.5.8.2" class="indexterm"></a><p>
382 Subqueries specifying a derived table must be enclosed in
383 parentheses. They may be assigned a table alias name, and optionally
384 column alias names (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>).
386 </p><pre class="programlisting">
387 FROM (SELECT * FROM table1) AS alias_name
390 This example is equivalent to <code class="literal">FROM table1 AS
391 alias_name</code>. More interesting cases, which cannot be
392 reduced to a plain join, arise when the subquery involves
393 grouping or aggregation.
395 A subquery can also be a <code class="command">VALUES</code> list:
396 </p><pre class="programlisting">
397 FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
398 AS names(first, last)
400 Again, a table alias is optional. Assigning alias names to the columns
401 of the <code class="command">VALUES</code> list is optional, but is good practice.
402 For more information see <a class="xref" href="queries-values.html" title="7.7. VALUES Lists">Section 7.7</a>.
404 According to the SQL standard, a table alias name must be supplied
405 for a subquery. <span class="productname">PostgreSQL</span>
406 allows <code class="literal">AS</code> and the alias to be omitted, but
407 writing one is good practice in SQL code that might be ported to
409 </p></div><div class="sect3" id="QUERIES-TABLEFUNCTIONS"><div class="titlepage"><div><div><h4 class="title">7.2.1.4. Table Functions <a href="#QUERIES-TABLEFUNCTIONS" class="id_link">#</a></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>
410 Table functions are functions that produce a set of rows, made up
411 of either base data types (scalar types) or composite data types
412 (table rows). They are used like a table, view, or subquery in
413 the <code class="literal">FROM</code> clause of a query. Columns returned by table
414 functions can be included in <code class="literal">SELECT</code>,
415 <code class="literal">JOIN</code>, or <code class="literal">WHERE</code> clauses in the same manner
416 as columns of a table, view, or subquery.
418 Table functions may also be combined using the <code class="literal">ROWS FROM</code>
419 syntax, with the results returned in parallel columns; the number of
420 result rows in this case is that of the largest function result, with
421 smaller results padded with null values to match.
422 </p><pre class="synopsis">
423 <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>]
424 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>]
426 If the <code class="literal">WITH ORDINALITY</code> clause is specified, an
427 additional column of type <code class="type">bigint</code> will be added to the
428 function result columns. This column numbers the rows of the function
429 result set, starting from 1. (This is a generalization of the
430 SQL-standard syntax for <code class="literal">UNNEST ... WITH ORDINALITY</code>.)
431 By default, the ordinal column is called <code class="literal">ordinality</code>, but
432 a different column name can be assigned to it using
433 an <code class="literal">AS</code> clause.
435 The special table function <code class="literal">UNNEST</code> may be called with
436 any number of array parameters, and it returns a corresponding number of
437 columns, as if <code class="literal">UNNEST</code>
438 (<a class="xref" href="functions-array.html" title="9.19. Array Functions and Operators">Section 9.19</a>) had been called on each parameter
439 separately and combined using the <code class="literal">ROWS FROM</code> construct.
440 </p><pre class="synopsis">
441 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>]
443 If no <em class="replaceable"><code>table_alias</code></em> is specified, the function
444 name is used as the table name; in the case of a <code class="literal">ROWS FROM()</code>
445 construct, the first function's name is used.
447 If column aliases are not supplied, then for a function returning a base
448 data type, the column name is also the same as the function name. For a
449 function returning a composite type, the result columns get the names
450 of the individual attributes of the type.
453 </p><pre class="programlisting">
454 CREATE TABLE foo (fooid int, foosubid int, fooname text);
456 CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
457 SELECT * FROM foo WHERE fooid = $1;
460 SELECT * FROM getfoo(1) AS t1;
465 FROM getfoo(foo.fooid) z
466 WHERE z.fooid = foo.fooid
469 CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);
471 SELECT * FROM vw_getfoo;
474 In some cases it is useful to define table functions that can
475 return different column sets depending on how they are invoked.
476 To support this, the table function can be declared as returning
477 the pseudo-type <code class="type">record</code> with no <code class="literal">OUT</code>
478 parameters. When such a function is used in
479 a query, the expected row structure must be specified in the
480 query itself, so that the system can know how to parse and plan
481 the query. This syntax looks like:
482 </p><pre class="synopsis">
483 <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>])
484 <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>])
485 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>] )
487 When not using the <code class="literal">ROWS FROM()</code> syntax,
488 the <em class="replaceable"><code>column_definition</code></em> list replaces the column
489 alias list that could otherwise be attached to the <code class="literal">FROM</code>
490 item; the names in the column definitions serve as column aliases.
491 When using the <code class="literal">ROWS FROM()</code> syntax,
492 a <em class="replaceable"><code>column_definition</code></em> list can be attached to
493 each member function separately; or if there is only one member function
494 and no <code class="literal">WITH ORDINALITY</code> clause,
495 a <em class="replaceable"><code>column_definition</code></em> list can be written in
496 place of a column alias list following <code class="literal">ROWS FROM()</code>.
498 Consider this example:
499 </p><pre class="programlisting">
501 FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
502 AS t1(proname name, prosrc text)
503 WHERE proname LIKE 'bytea%';
505 The <a class="xref" href="contrib-dblink-function.html" title="dblink"><span class="refentrytitle">dblink</span></a> function
506 (part of the <a class="xref" href="dblink.html" title="F.11. dblink — connect to other PostgreSQL databases">dblink</a> module) executes
507 a remote query. It is declared to return
508 <code class="type">record</code> since it might be used for any kind of query.
509 The actual column set must be specified in the calling query so
510 that the parser knows, for example, what <code class="literal">*</code> should
513 This example uses <code class="literal">ROWS FROM</code>:
514 </p><pre class="programlisting">
518 json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
519 AS (a INTEGER, b TEXT),
520 generate_series(1, 3)
530 It joins two functions into a single <code class="literal">FROM</code>
531 target. <code class="function">json_to_recordset()</code> is instructed
532 to return two columns, the first <code class="type">integer</code>
533 and the second <code class="type">text</code>. The result of
534 <code class="function">generate_series()</code> is used directly.
535 The <code class="literal">ORDER BY</code> clause sorts the column values
537 </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 <a href="#QUERIES-LATERAL" class="id_link">#</a></h4></div></div></div><a id="id-1.5.6.6.5.10.2" class="indexterm"></a><p>
538 Subqueries appearing in <code class="literal">FROM</code> can be
539 preceded by the key word <code class="literal">LATERAL</code>. This allows them to
540 reference columns provided by preceding <code class="literal">FROM</code> items.
541 (Without <code class="literal">LATERAL</code>, each subquery is
542 evaluated independently and so cannot cross-reference any other
543 <code class="literal">FROM</code> item.)
545 Table functions appearing in <code class="literal">FROM</code> can also be
546 preceded by the key word <code class="literal">LATERAL</code>, but for functions the
547 key word is optional; the function's arguments can contain references
548 to columns provided by preceding <code class="literal">FROM</code> items in any case.
550 A <code class="literal">LATERAL</code> item can appear at the top level in the
551 <code class="literal">FROM</code> list, or within a <code class="literal">JOIN</code> tree. In the latter
552 case it can also refer to any items that are on the left-hand side of a
553 <code class="literal">JOIN</code> that it is on the right-hand side of.
555 When a <code class="literal">FROM</code> item contains <code class="literal">LATERAL</code>
556 cross-references, evaluation proceeds as follows: for each row of the
557 <code class="literal">FROM</code> item providing the cross-referenced column(s), or
558 set of rows of multiple <code class="literal">FROM</code> items providing the
559 columns, the <code class="literal">LATERAL</code> item is evaluated using that
560 row or row set's values of the columns. The resulting row(s) are
561 joined as usual with the rows they were computed from. This is
562 repeated for each row or set of rows from the column source table(s).
564 A trivial example of <code class="literal">LATERAL</code> is
565 </p><pre class="programlisting">
566 SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;
568 This is not especially useful since it has exactly the same result as
569 the more conventional
570 </p><pre class="programlisting">
571 SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;
573 <code class="literal">LATERAL</code> is primarily useful when the cross-referenced
574 column is necessary for computing the row(s) to be joined. A common
575 application is providing an argument value for a set-returning function.
576 For example, supposing that <code class="function">vertices(polygon)</code> returns the
577 set of vertices of a polygon, we could identify close-together vertices
578 of polygons stored in a table with:
579 </p><pre class="programlisting">
580 SELECT p1.id, p2.id, v1, v2
581 FROM polygons p1, polygons p2,
582 LATERAL vertices(p1.poly) v1,
583 LATERAL vertices(p2.poly) v2
584 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
586 This query could also be written
587 </p><pre class="programlisting">
588 SELECT p1.id, p2.id, v1, v2
589 FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,
590 polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2
591 WHERE (v1 <-> v2) < 10 AND p1.id != p2.id;
593 or in several other equivalent formulations. (As already mentioned,
594 the <code class="literal">LATERAL</code> key word is unnecessary in this example, but
595 we use it for clarity.)
597 It is often particularly handy to <code class="literal">LEFT JOIN</code> to a
598 <code class="literal">LATERAL</code> subquery, so that source rows will appear in
599 the result even if the <code class="literal">LATERAL</code> subquery produces no
600 rows for them. For example, if <code class="function">get_product_names()</code> returns
601 the names of products made by a manufacturer, but some manufacturers in
602 our table currently produce no products, we could find out which ones
604 </p><pre class="programlisting">
606 FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true
609 </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 <a href="#QUERIES-WHERE" class="id_link">#</a></h3></div></div></div><a id="id-1.5.6.6.6.2" class="indexterm"></a><p>
610 The syntax of the <a class="link" href="sql-select.html#SQL-WHERE" title="WHERE Clause"><code class="literal">WHERE</code></a>
612 </p><pre class="synopsis">
613 WHERE <em class="replaceable"><code>search_condition</code></em>
615 where <em class="replaceable"><code>search_condition</code></em> is any value
616 expression (see <a class="xref" href="sql-expressions.html" title="4.2. Value Expressions">Section 4.2</a>) that
617 returns a value of type <code class="type">boolean</code>.
619 After the processing of the <code class="literal">FROM</code> clause is done, each
620 row of the derived virtual table is checked against the search
621 condition. If the result of the condition is true, the row is
622 kept in the output table, otherwise (i.e., if the result is
623 false or null) it is discarded. The search condition typically
624 references at least one column of the table generated in the
625 <code class="literal">FROM</code> clause; this is not required, but otherwise the
626 <code class="literal">WHERE</code> clause will be fairly useless.
627 </p><div class="note"><h3 class="title">Note</h3><p>
628 The join condition of an inner join can be written either in
629 the <code class="literal">WHERE</code> clause or in the <code class="literal">JOIN</code> clause.
630 For example, these table expressions are equivalent:
631 </p><pre class="programlisting">
632 FROM a, b WHERE a.id = b.id AND b.val > 5
635 </p><pre class="programlisting">
636 FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5
639 </p><pre class="programlisting">
640 FROM a NATURAL JOIN b WHERE b.val > 5
642 Which one of these you use is mainly a matter of style. The
643 <code class="literal">JOIN</code> syntax in the <code class="literal">FROM</code> clause is
644 probably not as portable to other SQL database management systems,
645 even though it is in the SQL standard. For
646 outer joins there is no choice: they must be done in
647 the <code class="literal">FROM</code> clause. The <code class="literal">ON</code> or <code class="literal">USING</code>
648 clause of an outer join is <span class="emphasis"><em>not</em></span> equivalent to a
649 <code class="literal">WHERE</code> condition, because it results in the addition
650 of rows (for unmatched input rows) as well as the removal of rows
653 Here are some examples of <code class="literal">WHERE</code> clauses:
654 </p><pre class="programlisting">
655 SELECT ... FROM fdt WHERE c1 > 5
657 SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)
659 SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)
661 SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)
663 SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100
665 SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 > fdt.c1)
667 <code class="literal">fdt</code> is the table derived in the
668 <code class="literal">FROM</code> clause. Rows that do not meet the search
669 condition of the <code class="literal">WHERE</code> clause are eliminated from
670 <code class="literal">fdt</code>. Notice the use of scalar subqueries as
671 value expressions. Just like any other query, the subqueries can
672 employ complex table expressions. Notice also how
673 <code class="literal">fdt</code> is referenced in the subqueries.
674 Qualifying <code class="literal">c1</code> as <code class="literal">fdt.c1</code> is only necessary
675 if <code class="literal">c1</code> is also the name of a column in the derived
676 input table of the subquery. But qualifying the column name adds
677 clarity even when it is not needed. This example shows how the column
678 naming scope of an outer query extends into its inner queries.
679 </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 <a href="#QUERIES-GROUP" class="id_link">#</a></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>
680 After passing the <code class="literal">WHERE</code> filter, the derived input
681 table might be subject to grouping, using the <code class="literal">GROUP BY</code>
682 clause, and elimination of group rows using the <code class="literal">HAVING</code>
684 </p><pre class="synopsis">
685 SELECT <em class="replaceable"><code>select_list</code></em>
687 [<span class="optional">WHERE ...</span>]
688 GROUP BY <em class="replaceable"><code>grouping_column_reference</code></em> [<span class="optional">, <em class="replaceable"><code>grouping_column_reference</code></em></span>]...
690 The <a class="link" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause"><code class="literal">GROUP BY</code></a> clause is
691 used to group together those rows in a table that have the same
692 values in all the columns listed. The order in which the columns
693 are listed does not matter. The effect is to combine each set
694 of rows having common values into one group row that
695 represents all rows in the group. This is done to
696 eliminate redundancy in the output and/or compute aggregates that
697 apply to these groups. For instance:
698 </p><pre class="screen">
699 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM test1;</code></strong>
708 <code class="prompt">=></code> <strong class="userinput"><code>SELECT x FROM test1 GROUP BY x;</code></strong>
717 In the second query, we could not have written <code class="literal">SELECT *
718 FROM test1 GROUP BY x</code>, because there is no single value
719 for the column <code class="literal">y</code> that could be associated with each
720 group. The grouped-by columns can be referenced in the select list since
721 they have a single value in each group.
723 In general, if a table is grouped, columns that are not
724 listed in <code class="literal">GROUP BY</code> cannot be referenced except in aggregate
725 expressions. An example with aggregate expressions is:
726 </p><pre class="screen">
727 <code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x;</code></strong>
735 Here <code class="literal">sum</code> is an aggregate function that
736 computes a single value over the entire group. More information
737 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>.
738 </p><div class="tip"><h3 class="title">Tip</h3><p>
739 Grouping without aggregate expressions effectively calculates the
740 set of distinct values in a column. This can also be achieved
741 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>).
743 Here is another example: it calculates the total sales for each
744 product (rather than the total sales of all products):
745 </p><pre class="programlisting">
746 SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
747 FROM products p LEFT JOIN sales s USING (product_id)
748 GROUP BY product_id, p.name, p.price;
750 In this example, the columns <code class="literal">product_id</code>,
751 <code class="literal">p.name</code>, and <code class="literal">p.price</code> must be
752 in the <code class="literal">GROUP BY</code> clause since they are referenced in
753 the query select list (but see below). The column
754 <code class="literal">s.units</code> does not have to be in the <code class="literal">GROUP
755 BY</code> list since it is only used in an aggregate expression
756 (<code class="literal">sum(...)</code>), which represents the sales
757 of a product. For each product, the query returns a summary row about
758 all sales of the product.
759 </p><a id="id-1.5.6.6.7.11" class="indexterm"></a><p>
760 If the products table is set up so that, say,
761 <code class="literal">product_id</code> is the primary key, then it would be
762 enough to group by <code class="literal">product_id</code> in the above example,
763 since name and price would be <em class="firstterm">functionally
764 dependent</em> on the product ID, and so there would be no
765 ambiguity about which name and price value to return for each product
768 In strict SQL, <code class="literal">GROUP BY</code> can only group by columns of
769 the source table but <span class="productname">PostgreSQL</span> extends
770 this to also allow <code class="literal">GROUP BY</code> to group by columns in the
771 select list. Grouping by value expressions instead of simple
772 column names is also allowed.
773 </p><a id="id-1.5.6.6.7.14" class="indexterm"></a><p>
774 If a table has been grouped using <code class="literal">GROUP BY</code>,
775 but only certain groups are of interest, the
776 <code class="literal">HAVING</code> clause can be used, much like a
777 <code class="literal">WHERE</code> clause, to eliminate groups from the result.
779 </p><pre class="synopsis">
780 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>
782 Expressions in the <code class="literal">HAVING</code> clause can refer both to
783 grouped expressions and to ungrouped expressions (which necessarily
784 involve an aggregate function).
787 </p><pre class="screen">
788 <code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;</code></strong>
795 <code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';</code></strong>
803 Again, a more realistic example:
804 </p><pre class="programlisting">
805 SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
806 FROM products p LEFT JOIN sales s USING (product_id)
807 WHERE s.date > CURRENT_DATE - INTERVAL '4 weeks'
808 GROUP BY product_id, p.name, p.price, p.cost
809 HAVING sum(p.price * s.units) > 5000;
811 In the example above, the <code class="literal">WHERE</code> clause is selecting
812 rows by a column that is not grouped (the expression is only true for
813 sales during the last four weeks), while the <code class="literal">HAVING</code>
814 clause restricts the output to groups with total gross sales over
815 5000. Note that the aggregate expressions do not necessarily need
816 to be the same in all parts of the query.
818 If a query contains aggregate function calls, but no <code class="literal">GROUP BY</code>
819 clause, grouping still occurs: the result is a single group row (or
820 perhaps no rows at all, if the single row is then eliminated by
821 <code class="literal">HAVING</code>).
822 The same is true if it contains a <code class="literal">HAVING</code> clause, even
823 without any aggregate function calls or <code class="literal">GROUP BY</code> clause.
824 </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> <a href="#QUERIES-GROUPING-SETS" class="id_link">#</a></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>
825 More complex grouping operations than those described above are possible
826 using the concept of <em class="firstterm">grouping sets</em>. The data selected by
827 the <code class="literal">FROM</code> and <code class="literal">WHERE</code> clauses is grouped separately
828 by each specified grouping set, aggregates computed for each group just as
829 for simple <code class="literal">GROUP BY</code> clauses, and then the results returned.
831 </p><pre class="screen">
832 <code class="prompt">=></code> <strong class="userinput"><code>SELECT * FROM items_sold;</code></strong>
834 -------+------+-------
841 <code class="prompt">=></code> <strong class="userinput"><code>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</code></strong>
852 Each sublist of <code class="literal">GROUPING SETS</code> may specify zero or more columns
853 or expressions and is interpreted the same way as though it were directly
854 in the <code class="literal">GROUP BY</code> clause. An empty grouping set means that all
855 rows are aggregated down to a single group (which is output even if no
856 input rows were present), as described above for the case of aggregate
857 functions with no <code class="literal">GROUP BY</code> clause.
859 References to the grouping columns or expressions are replaced
860 by null values in result rows for grouping sets in which those
861 columns do not appear. To distinguish which grouping a particular output
862 row resulted from, see <a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.66. Grouping Operations">Table 9.66</a>.
864 A shorthand notation is provided for specifying two common types of grouping set.
866 </p><pre class="programlisting">
867 ROLLUP ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, <em class="replaceable"><code>e3</code></em>, ... )
869 represents the given list of expressions and all prefixes of the list including
870 the empty list; thus it is equivalent to
871 </p><pre class="programlisting">
873 ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, <em class="replaceable"><code>e3</code></em>, ... ),
875 ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em> ),
876 ( <em class="replaceable"><code>e1</code></em> ),
880 This is commonly used for analysis over hierarchical data; e.g., total
881 salary by department, division, and company-wide total.
884 </p><pre class="programlisting">
885 CUBE ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, ... )
887 represents the given list and all of its possible subsets (i.e., the power
889 </p><pre class="programlisting">
893 </p><pre class="programlisting">
906 The individual elements of a <code class="literal">CUBE</code> or <code class="literal">ROLLUP</code>
907 clause may be either individual expressions, or sublists of elements in
908 parentheses. In the latter case, the sublists are treated as single
909 units for the purposes of generating the individual grouping sets.
911 </p><pre class="programlisting">
912 CUBE ( (a, b), (c, d) )
915 </p><pre class="programlisting">
924 </p><pre class="programlisting">
925 ROLLUP ( a, (b, c), d )
928 </p><pre class="programlisting">
937 The <code class="literal">CUBE</code> and <code class="literal">ROLLUP</code> constructs can be used either
938 directly in the <code class="literal">GROUP BY</code> clause, or nested inside a
939 <code class="literal">GROUPING SETS</code> clause. If one <code class="literal">GROUPING SETS</code> clause
940 is nested inside another, the effect is the same as if all the elements of
941 the inner clause had been written directly in the outer clause.
943 If multiple grouping items are specified in a single <code class="literal">GROUP BY</code>
944 clause, then the final list of grouping sets is the Cartesian product of the
945 individual items. For example:
946 </p><pre class="programlisting">
947 GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))
950 </p><pre class="programlisting">
951 GROUP BY GROUPING SETS (
952 (a, b, c, d), (a, b, c, e),
953 (a, b, d), (a, b, e),
954 (a, c, d), (a, c, e),
959 <a id="id-1.5.6.6.8.13.1" class="indexterm"></a>
960 <a id="id-1.5.6.6.8.13.2" class="indexterm"></a>
961 When specifying multiple grouping items together, the final set of grouping
962 sets might contain duplicates. For example:
963 </p><pre class="programlisting">
964 GROUP BY ROLLUP (a, b), ROLLUP (a, c)
967 </p><pre class="programlisting">
968 GROUP BY GROUPING SETS (
980 If these duplicates are undesirable, they can be removed using the
981 <code class="literal">DISTINCT</code> clause directly on the <code class="literal">GROUP BY</code>.
983 </p><pre class="programlisting">
984 GROUP BY <span class="emphasis"><strong>DISTINCT</strong></span> ROLLUP (a, b), ROLLUP (a, c)
987 </p><pre class="programlisting">
988 GROUP BY GROUPING SETS (
996 This is not the same as using <code class="literal">SELECT DISTINCT</code> because the output
997 rows may still contain duplicates. If any of the ungrouped columns contains NULL,
998 it will be indistinguishable from the NULL used when that same column is grouped.
999 </p><div class="note"><h3 class="title">Note</h3><p>
1000 The construct <code class="literal">(a, b)</code> is normally recognized in expressions as
1001 a <a class="link" href="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" title="4.2.13. Row Constructors">row constructor</a>.
1002 Within the <code class="literal">GROUP BY</code> clause, this does not apply at the top
1003 levels of expressions, and <code class="literal">(a, b)</code> is parsed as a list of
1004 expressions as described above. If for some reason you <span class="emphasis"><em>need</em></span>
1005 a row constructor in a grouping expression, use <code class="literal">ROW(a, b)</code>.
1006 </p></div></div><div class="sect2" id="QUERIES-WINDOW"><div class="titlepage"><div><div><h3 class="title">7.2.5. Window Function Processing <a href="#QUERIES-WINDOW" class="id_link">#</a></h3></div></div></div><a id="id-1.5.6.6.9.2" class="indexterm"></a><p>
1007 If the query contains any window functions (see
1008 <a class="xref" href="tutorial-window.html" title="3.5. Window Functions">Section 3.5</a>,
1009 <a class="xref" href="functions-window.html" title="9.22. Window Functions">Section 9.22</a> and
1010 <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
1011 after any grouping, aggregation, and <code class="literal">HAVING</code> filtering is
1012 performed. That is, if the query uses any aggregates, <code class="literal">GROUP
1013 BY</code>, or <code class="literal">HAVING</code>, then the rows seen by the window functions
1014 are the group rows instead of the original table rows from
1015 <code class="literal">FROM</code>/<code class="literal">WHERE</code>.
1017 When multiple window functions are used, all the window functions having
1018 equivalent <code class="literal">PARTITION BY</code> and <code class="literal">ORDER BY</code>
1019 clauses in their window definitions are guaranteed to see the same
1020 ordering of the input rows, even if the <code class="literal">ORDER BY</code> does
1021 not uniquely determine the ordering.
1022 However, no guarantees are made about the evaluation of functions having
1023 different <code class="literal">PARTITION BY</code> or <code class="literal">ORDER BY</code> specifications.
1024 (In such cases a sort step is typically required between the passes of
1025 window function evaluations, and the sort is not guaranteed to preserve
1026 ordering of rows that its <code class="literal">ORDER BY</code> sees as equivalent.)
1028 Currently, window functions always require presorted data, and so the
1029 query output will be ordered according to one or another of the window
1030 functions' <code class="literal">PARTITION BY</code>/<code class="literal">ORDER BY</code> clauses.
1031 It is not recommended to rely on this, however. Use an explicit
1032 top-level <code class="literal">ORDER BY</code> clause if you want to be sure the
1033 results are sorted in a particular way.
1034 </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 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 7.3. Select Lists</td></tr></table></div></body></html>
begriffs.com / projects / ai-pg / blob