Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes14kdownloads
queries-table-expressions.html1034 linesDownload Raw Back to html
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 16.3 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.  The4   table expression contains a <code class="literal">FROM</code> clause that is5   optionally followed by <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and6   <code class="literal">HAVING</code> clauses.  Trivial table expressions simply refer7   to a table on disk, a so-called base table, but more complex8   expressions can be used to modify or combine base tables in various9   ways.10  </p><p>11   The optional <code class="literal">WHERE</code>, <code class="literal">GROUP BY</code>, and12   <code class="literal">HAVING</code> clauses in the table expression specify a13   pipeline of successive transformations performed on the table14   derived in the <code class="literal">FROM</code> clause.  All these transformations15   produce a virtual table that provides the rows that are passed to16   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 a19    table from one or more other tables given in a comma-separated20    table reference list.21</p><pre class="synopsis">22FROM <em class="replaceable"><code>table_reference</code></em> [<span class="optional">, <em class="replaceable"><code>table_reference</code></em> [<span class="optional">, ...</span>]</span>]23</pre><p>24 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, or27    complex combinations of these.  If more than one table reference is28    listed in the <code class="literal">FROM</code> clause, the tables are cross-joined29    (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 virtual31    table that can then be subject to32    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 the34    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 a37    table inheritance hierarchy, the table reference produces rows of38    not only that table but all of its descendant tables, unless the39    key word <code class="literal">ONLY</code> precedes the table name.  However, the40    reference produces only the columns that appear in the named table41    — any columns added in subtables are ignored.42   </p><p>43    Instead of writing <code class="literal">ONLY</code> before the table name, you can write44    <code class="literal">*</code> after the table name to explicitly specify that descendant45    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 or50     derived) tables according to the rules of the particular join51     type.  Inner, outer, and cross-joins are available.52     The general syntax of a joined table is53</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>]55</pre><p>56     Joins of all types can be chained together, or nested: either or57     both <em class="replaceable"><code>T1</code></em> and58     <em class="replaceable"><code>T2</code></em> can be joined tables.  Parentheses59     can be used around <code class="literal">JOIN</code> clauses to control the join60     order.  In the absence of parentheses, <code class="literal">JOIN</code> clauses61     nest left-to-right.62    </p><div class="variablelist"><p class="title"><strong>Join Types</strong></p><dl class="variablelist"><dt><span class="term">Cross join63      <a id="id-1.5.6.6.5.6.4.2.1.1" class="indexterm"></a>64 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>68</pre><p>69        For every possible combination of rows from70        <em class="replaceable"><code>T1</code></em> and71        <em class="replaceable"><code>T2</code></em> (i.e., a Cartesian product),72        the joined table will contain a73        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>.  If75        the tables have N and M rows respectively, the joined76        table will have N * M rows.77       </p><p>78        <code class="literal">FROM <em class="replaceable"><code>T1</code></em> CROSS JOIN79        <em class="replaceable"><code>T2</code></em></code> is equivalent to80        <code class="literal">FROM <em class="replaceable"><code>T1</code></em> INNER JOIN81        <em class="replaceable"><code>T2</code></em> ON TRUE</code> (see below).82        It is also equivalent to83        <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 two87         tables appear, because <code class="literal">JOIN</code> binds more tightly than88         comma.  For example89         <code class="literal">FROM <em class="replaceable"><code>T1</code></em> CROSS JOIN90         <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>92         is not the same as93         <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> can97         reference <em class="replaceable"><code>T1</code></em> in the first case but not98         the second.99        </p></div><p>100       </p></dd><dt><span class="term">Qualified joins101      <a id="id-1.5.6.6.5.6.4.3.1.1" class="indexterm"></a>102 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>108</pre><p>109        The words <code class="literal">INNER</code> and110        <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>, and113        <code class="literal">FULL</code> imply an outer join.114       </p><p>115        The <em class="firstterm">join condition</em> is specified in the116        <code class="literal">ON</code> or <code class="literal">USING</code> clause, or implicitly by117        the word <code class="literal">NATURAL</code>.  The join condition determines118        which rows from the two source tables are considered to119        <span class="quote">“<span class="quote">match</span>”</span>, as explained in detail below.120       </p><p>121        The possible types of qualified join are:122 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 each125           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>128 129         <a id="id-1.5.6.6.5.6.4.3.2.4.1.2.1.3" class="indexterm"></a>130         </span></dt><dd><p>131           First, an inner join is performed.  Then, for each row in132           T1 that does not satisfy the join condition with any row in133           T2, a joined row is added with null values in columns of134           T2.  Thus, the joined table always has at least135           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>138 139         <a id="id-1.5.6.6.5.6.4.3.2.4.1.3.1.3" class="indexterm"></a>140         </span></dt><dd><p>141           First, an inner join is performed.  Then, for each row in142           T2 that does not satisfy the join condition with any row in143           T1, a joined row is added with null values in columns of144           T1.  This is the converse of a left join: the result table145           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 in148           T1 that does not satisfy the join condition with any row in149           T2, a joined row is added with null values in columns of150           T2.  Also, for each row of T2 that does not satisfy the151           join condition with any row in T1, a joined row with null152           values in the columns of T1 is added.153          </p></dd></dl></div><p>154       </p><p>155        The <code class="literal">ON</code> clause is the most general kind of join156        condition: it takes a Boolean value expression of the same157        kind as is used in a <code class="literal">WHERE</code> clause.  A pair of rows158        from <em class="replaceable"><code>T1</code></em> and <em class="replaceable"><code>T2</code></em> match if the159        <code class="literal">ON</code> expression evaluates to true.160       </p><p>161        The <code class="literal">USING</code> clause is a shorthand that allows you to take162        advantage of the specific situation where both sides of the join use163        the same name for the joining column(s).  It takes a164        comma-separated list of the shared column names165        and forms a join condition that includes an equality comparison166        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> produces168        the join condition <code class="literal">ON <em class="replaceable"><code>T1</code></em>.a169        = <em class="replaceable"><code>T2</code></em>.a AND <em class="replaceable"><code>T1</code></em>.b170        = <em class="replaceable"><code>T2</code></em>.b</code>.171       </p><p>172        Furthermore, the output of <code class="literal">JOIN USING</code> suppresses173        redundant columns: there is no need to print both of the matched174        columns, since they must have equal values.  While <code class="literal">JOIN175        ON</code> produces all columns from <em class="replaceable"><code>T1</code></em> followed by all176        columns from <em class="replaceable"><code>T2</code></em>, <code class="literal">JOIN USING</code> produces one177        output column for each of the listed column pairs (in the listed178        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>.180       </p><p>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 of184        <code class="literal">USING</code>: it forms a <code class="literal">USING</code> list185        consisting of all column names that appear in both186        input tables.  As with <code class="literal">USING</code>, these columns appear187        only once in the output table.  If there are no common188        column names, <code class="literal">NATURAL JOIN</code> behaves like189        <code class="literal">JOIN ... ON TRUE</code>, producing a cross-product join.190       </p><div class="note"><h3 class="title">Note</h3><p>191         <code class="literal">USING</code> is reasonably safe from column changes192         in the joined relations since only the listed columns193         are combined.  <code class="literal">NATURAL</code> is considerably more risky since194         any schema changes to either relation that cause a new matching195         column name to be present will cause the join to combine that new196         column as well.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">200 num | name201-----+------202   1 | a203   2 | b204   3 | c205</pre><p>206     and <code class="literal">t2</code>:207</p><pre class="programlisting">208 num | value209-----+-------210   1 | xxx211   3 | yyy212   5 | zzz213</pre><p>214     then we get the following results for the various joins:215</p><pre class="screen">216<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 CROSS JOIN t2;</code></strong>217 num | name | num | value218-----+------+-----+-------219   1 | a    |   1 | xxx220   1 | a    |   3 | yyy221   1 | a    |   5 | zzz222   2 | b    |   1 | xxx223   2 | b    |   3 | yyy224   2 | b    |   5 | zzz225   3 | c    |   1 | xxx226   3 | c    |   3 | yyy227   3 | c    |   5 | zzz228(9 rows)229 230<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</code></strong>231 num | name | num | value232-----+------+-----+-------233   1 | a    |   1 | xxx234   3 | c    |   3 | yyy235(2 rows)236 237<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 INNER JOIN t2 USING (num);</code></strong>238 num | name | value239-----+------+-------240   1 | a    | xxx241   3 | c    | yyy242(2 rows)243 244<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 NATURAL INNER JOIN t2;</code></strong>245 num | name | value246-----+------+-------247   1 | a    | xxx248   3 | c    | yyy249(2 rows)250 251<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</code></strong>252 num | name | num | value253-----+------+-----+-------254   1 | a    |   1 | xxx255   2 | b    |     |256   3 | c    |   3 | yyy257(3 rows)258 259<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 LEFT JOIN t2 USING (num);</code></strong>260 num | name | value261-----+------+-------262   1 | a    | xxx263   2 | b    |264   3 | c    | yyy265(3 rows)266 267<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</code></strong>268 num | name | num | value269-----+------+-----+-------270   1 | a    |   1 | xxx271   3 | c    |   3 | yyy272     |      |   5 | zzz273(3 rows)274 275<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</code></strong>276 num | name | num | value277-----+------+-----+-------278   1 | a    |   1 | xxx279   2 | b    |     |280   3 | c    |   3 | yyy281     |      |   5 | zzz282(4 rows)283</pre><p>284    </p><p>285     The join condition specified with <code class="literal">ON</code> can also contain286     conditions that do not relate directly to the join.  This can287     prove useful for some queries but needs to be thought out288     carefully.  For example:289</p><pre class="screen">290<code class="prompt">=&gt;</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 | value292-----+------+-----+-------293   1 | a    |   1 | xxx294   2 | b    |     |295   3 | c    |     |296(3 rows)297</pre><p>298     Notice that placing the restriction in the <code class="literal">WHERE</code> clause299     produces a different result:300</p><pre class="screen">301<code class="prompt">=&gt;</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 | value303-----+------+-----+-------304   1 | a    |   1 | xxx305(1 row)306</pre><p>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, while309     a restriction placed in the <code class="literal">WHERE</code> clause is processed310     <span class="emphasis"><em>after</em></span> the join.311     That does not matter with inner joins, but it matters a lot with outer312     joins.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 table315     references to be used for references to the derived table in316     the rest of the query.  This is called a <em class="firstterm">table317     alias</em>.318    </p><p>319     To create a table alias, write320</p><pre class="synopsis">321FROM <em class="replaceable"><code>table_reference</code></em> AS <em class="replaceable"><code>alias</code></em>322</pre><p>323     or324</p><pre class="synopsis">325FROM <em class="replaceable"><code>table_reference</code></em> <em class="replaceable"><code>alias</code></em>326</pre><p>327     The <code class="literal">AS</code> key word is optional noise.328     <em class="replaceable"><code>alias</code></em> can be any identifier.329    </p><p>330     A typical application of table aliases is to assign short331     identifiers to long table names to keep the join clauses332     readable.  For example:333</p><pre class="programlisting">334SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;335</pre><p>336    </p><p>337     The alias becomes the new name of the table reference so far as the338     current query is concerned — it is not allowed to refer to the339     table by the original name elsewhere in the query.  Thus, this is not340     valid:341</p><pre class="programlisting">342SELECT * FROM my_table AS m WHERE my_table.a &gt; 5;    -- wrong343</pre><p>344    </p><p>345     Table aliases are mainly for notational convenience, but it is346     necessary to use them when joining a table to itself, e.g.:347</p><pre class="programlisting">348SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;349</pre><p>350    </p><p>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 second353     instance of <code class="literal">my_table</code>, but the second statement assigns the354     alias to the result of the join:355</p><pre class="programlisting">356SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...357SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...358</pre><p>359    </p><p>360     Another form of table aliasing gives temporary names to the columns of361     the table, as well as the table itself:362</p><pre class="synopsis">363FROM <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>] )364</pre><p>365     If fewer column aliases are specified than the actual table has366     columns, the remaining columns are not renamed.  This syntax is367     especially useful for self-joins or subqueries.368    </p><p>369     When an alias is applied to the output of a <code class="literal">JOIN</code>370     clause, the alias hides the original371     name(s) within the <code class="literal">JOIN</code>.  For example:372</p><pre class="programlisting">373SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...374</pre><p>375     is valid SQL, but:376</p><pre class="programlisting">377SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c378</pre><p>379     is not valid; the table alias <code class="literal">a</code> is not visible380     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 in383     parentheses.  They may be assigned a table alias name, and optionally384     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>).385     For example:386</p><pre class="programlisting">387FROM (SELECT * FROM table1) AS alias_name388</pre><p>389    </p><p>390     This example is equivalent to <code class="literal">FROM table1 AS391     alias_name</code>.  More interesting cases, which cannot be392     reduced to a plain join, arise when the subquery involves393     grouping or aggregation.394    </p><p>395     A subquery can also be a <code class="command">VALUES</code> list:396</p><pre class="programlisting">397FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))398     AS names(first, last)399</pre><p>400     Again, a table alias is optional.  Assigning alias names to the columns401     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>.403    </p><p>404     According to the SQL standard, a table alias name must be supplied405     for a subquery.  <span class="productname">PostgreSQL</span>406     allows <code class="literal">AS</code> and the alias to be omitted, but407     writing one is good practice in SQL code that might be ported to408     another system.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 up411     of either base data types (scalar types) or composite data types412     (table rows).  They are used like a table, view, or subquery in413     the <code class="literal">FROM</code> clause of a query. Columns returned by table414     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 manner416     as columns of a table, view, or subquery.417    </p><p>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 of420     result rows in this case is that of the largest function result, with421     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>]424ROWS 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>]425</pre><p>426     If the <code class="literal">WITH ORDINALITY</code> clause is specified, an427     additional column of type <code class="type">bigint</code> will be added to the428     function result columns.  This column numbers the rows of the function429     result set, starting from 1. (This is a generalization of the430     SQL-standard syntax for <code class="literal">UNNEST ... WITH ORDINALITY</code>.)431     By default, the ordinal column is called <code class="literal">ordinality</code>, but432     a different column name can be assigned to it using433     an <code class="literal">AS</code> clause.434    </p><p>435     The special table function <code class="literal">UNNEST</code> may be called with436     any number of array parameters, and it returns a corresponding number of437     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 parameter439     separately and combined using the <code class="literal">ROWS FROM</code> construct.440    </p><pre class="synopsis">441UNNEST( <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>]442</pre><p>443     If no <em class="replaceable"><code>table_alias</code></em> is specified, the function444     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.446    </p><p>447     If column aliases are not supplied, then for a function returning a base448     data type, the column name is also the same as the function name.  For a449     function returning a composite type, the result columns get the names450     of the individual attributes of the type.451    </p><p>452     Some examples:453</p><pre class="programlisting">454CREATE TABLE foo (fooid int, foosubid int, fooname text);455 456CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$457    SELECT * FROM foo WHERE fooid = $1;458$$ LANGUAGE SQL;459 460SELECT * FROM getfoo(1) AS t1;461 462SELECT * FROM foo463    WHERE foosubid IN (464                        SELECT foosubid465                        FROM getfoo(foo.fooid) z466                        WHERE z.fooid = foo.fooid467                      );468 469CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);470 471SELECT * FROM vw_getfoo;472</pre><p>473    </p><p>474     In some cases it is useful to define table functions that can475     return different column sets depending on how they are invoked.476     To support this, the table function can be declared as returning477     the pseudo-type <code class="type">record</code> with no <code class="literal">OUT</code>478     parameters.  When such a function is used in479     a query, the expected row structure must be specified in the480     query itself, so that the system can know how to parse and plan481     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>])485ROWS 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>] )486</pre><p>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 column489     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 to493     each member function separately; or if there is only one member function494     and no <code class="literal">WITH ORDINALITY</code> clause,495     a <em class="replaceable"><code>column_definition</code></em> list can be written in496     place of a column alias list following <code class="literal">ROWS FROM()</code>.497    </p><p>498     Consider this example:499</p><pre class="programlisting">500SELECT *501    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')502      AS t1(proname name, prosrc text)503    WHERE proname LIKE 'bytea%';504</pre><p>505     The <a class="xref" href="contrib-dblink-function.html" title="dblink"><span class="refentrytitle">dblink</span></a> function506     (part of the <a class="xref" href="dblink.html" title="F.12. dblink — connect to other PostgreSQL databases">dblink</a> module) executes507     a remote query.  It is declared to return508     <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 so510     that the parser knows, for example, what <code class="literal">*</code> should511     expand to.512    </p><p>513     This example uses <code class="literal">ROWS FROM</code>:514</p><pre class="programlisting">515SELECT *516FROM ROWS FROM517    (518        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')519            AS (a INTEGER, b TEXT),520        generate_series(1, 3)521    ) AS x (p, q, s)522ORDER BY p;523 524  p  |  q  | s525-----+-----+---526  40 | foo | 1527 100 | bar | 2528     |     | 3529</pre><p>530     It joins two functions into a single <code class="literal">FROM</code>531     target.  <code class="function">json_to_recordset()</code> is instructed532     to return two columns, the first <code class="type">integer</code>533     and the second <code class="type">text</code>.  The result of534     <code class="function">generate_series()</code> is used directly.535     The <code class="literal">ORDER BY</code> clause sorts the column values536     as integers.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 be539     preceded by the key word <code class="literal">LATERAL</code>.  This allows them to540     reference columns provided by preceding <code class="literal">FROM</code> items.541     (Without <code class="literal">LATERAL</code>, each subquery is542     evaluated independently and so cannot cross-reference any other543     <code class="literal">FROM</code> item.)544    </p><p>545     Table functions appearing in <code class="literal">FROM</code> can also be546     preceded by the key word <code class="literal">LATERAL</code>, but for functions the547     key word is optional; the function's arguments can contain references548     to columns provided by preceding <code class="literal">FROM</code> items in any case.549    </p><p>550     A <code class="literal">LATERAL</code> item can appear at the top level in the551     <code class="literal">FROM</code> list, or within a <code class="literal">JOIN</code> tree.  In the latter552     case it can also refer to any items that are on the left-hand side of a553     <code class="literal">JOIN</code> that it is on the right-hand side of.554    </p><p>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 the557     <code class="literal">FROM</code> item providing the cross-referenced column(s), or558     set of rows of multiple <code class="literal">FROM</code> items providing the559     columns, the <code class="literal">LATERAL</code> item is evaluated using that560     row or row set's values of the columns.  The resulting row(s) are561     joined as usual with the rows they were computed from.  This is562     repeated for each row or set of rows from the column source table(s).563    </p><p>564     A trivial example of <code class="literal">LATERAL</code> is565</p><pre class="programlisting">566SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;567</pre><p>568     This is not especially useful since it has exactly the same result as569     the more conventional570</p><pre class="programlisting">571SELECT * FROM foo, bar WHERE bar.id = foo.bar_id;572</pre><p>573     <code class="literal">LATERAL</code> is primarily useful when the cross-referenced574     column is necessary for computing the row(s) to be joined.  A common575     application is providing an argument value for a set-returning function.576     For example, supposing that <code class="function">vertices(polygon)</code> returns the577     set of vertices of a polygon, we could identify close-together vertices578     of polygons stored in a table with:579</p><pre class="programlisting">580SELECT p1.id, p2.id, v1, v2581FROM polygons p1, polygons p2,582     LATERAL vertices(p1.poly) v1,583     LATERAL vertices(p2.poly) v2584WHERE (v1 &lt;-&gt; v2) &lt; 10 AND p1.id != p2.id;585</pre><p>586     This query could also be written587</p><pre class="programlisting">588SELECT p1.id, p2.id, v1, v2589FROM polygons p1 CROSS JOIN LATERAL vertices(p1.poly) v1,590     polygons p2 CROSS JOIN LATERAL vertices(p2.poly) v2591WHERE (v1 &lt;-&gt; v2) &lt; 10 AND p1.id != p2.id;592</pre><p>593     or in several other equivalent formulations.  (As already mentioned,594     the <code class="literal">LATERAL</code> key word is unnecessary in this example, but595     we use it for clarity.)596    </p><p>597     It is often particularly handy to <code class="literal">LEFT JOIN</code> to a598     <code class="literal">LATERAL</code> subquery, so that source rows will appear in599     the result even if the <code class="literal">LATERAL</code> subquery produces no600     rows for them.  For example, if <code class="function">get_product_names()</code> returns601     the names of products made by a manufacturer, but some manufacturers in602     our table currently produce no products, we could find out which ones603     those are like this:604</p><pre class="programlisting">605SELECT m.name606FROM manufacturers m LEFT JOIN LATERAL get_product_names(m.id) pname ON true607WHERE pname IS NULL;608</pre><p>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>611    clause is612</p><pre class="synopsis">613WHERE <em class="replaceable"><code>search_condition</code></em>614</pre><p>615    where <em class="replaceable"><code>search_condition</code></em> is any value616    expression (see <a class="xref" href="sql-expressions.html" title="4.2. Value Expressions">Section 4.2</a>) that617    returns a value of type <code class="type">boolean</code>.618   </p><p>619    After the processing of the <code class="literal">FROM</code> clause is done, each620    row of the derived virtual table is checked against the search621    condition.  If the result of the condition is true, the row is622    kept in the output table, otherwise (i.e., if the result is623    false or null) it is discarded.  The search condition typically624    references at least one column of the table generated in the625    <code class="literal">FROM</code> clause; this is not required, but otherwise the626    <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 in629     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">632FROM a, b WHERE a.id = b.id AND b.val &gt; 5633</pre><p>634     and:635</p><pre class="programlisting">636FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val &gt; 5637</pre><p>638     or perhaps even:639</p><pre class="programlisting">640FROM a NATURAL JOIN b WHERE b.val &gt; 5641</pre><p>642     Which one of these you use is mainly a matter of style.  The643     <code class="literal">JOIN</code> syntax in the <code class="literal">FROM</code> clause is644     probably not as portable to other SQL database management systems,645     even though it is in the SQL standard.  For646     outer joins there is no choice:  they must be done in647     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 a649     <code class="literal">WHERE</code> condition, because it results in the addition650     of rows (for unmatched input rows) as well as the removal of rows651     in the final result.652    </p></div><p>653    Here are some examples of <code class="literal">WHERE</code> clauses:654</p><pre class="programlisting">655SELECT ... FROM fdt WHERE c1 &gt; 5656 657SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)658 659SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)660 661SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)662 663SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100664 665SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 &gt; fdt.c1)666</pre><p>667    <code class="literal">fdt</code> is the table derived in the668    <code class="literal">FROM</code> clause. Rows that do not meet the search669    condition of the <code class="literal">WHERE</code> clause are eliminated from670    <code class="literal">fdt</code>. Notice the use of scalar subqueries as671    value expressions.  Just like any other query, the subqueries can672    employ complex table expressions.  Notice also how673    <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 necessary675    if <code class="literal">c1</code> is also the name of a column in the derived676    input table of the subquery.  But qualifying the column name adds677    clarity even when it is not needed.  This example shows how the column678    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 input681    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>683    clause.684   </p><pre class="synopsis">685SELECT <em class="replaceable"><code>select_list</code></em>686    FROM ...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>]...689</pre><p>690    The <a class="link" href="sql-select.html#SQL-GROUPBY" title="GROUP BY Clause"><code class="literal">GROUP BY</code></a> clause is691    used to group together those rows in a table that have the same692    values in all the columns listed. The order in which the columns693    are listed does not matter.  The effect is to combine each set694    of rows having common values into one group row that695    represents all rows in the group.  This is done to696    eliminate redundancy in the output and/or compute aggregates that697    apply to these groups.  For instance:698</p><pre class="screen">699<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM test1;</code></strong>700 x | y701---+---702 a | 3703 c | 2704 b | 5705 a | 1706(4 rows)707 708<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT x FROM test1 GROUP BY x;</code></strong>709 x710---711 a712 b713 c714(3 rows)715</pre><p>716   </p><p>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 value719    for the column <code class="literal">y</code> that could be associated with each720    group.  The grouped-by columns can be referenced in the select list since721    they have a single value in each group.722   </p><p>723    In general, if a table is grouped, columns that are not724    listed in <code class="literal">GROUP BY</code> cannot be referenced except in aggregate725    expressions.  An example with aggregate expressions is:726</p><pre class="screen">727<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x;</code></strong>728 x | sum729---+-----730 a |   4731 b |   5732 c |   2733(3 rows)734</pre><p>735    Here <code class="literal">sum</code> is an aggregate function that736    computes a single value over the entire group.  More information737    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 the740     set of distinct values in a column.  This can also be achieved741     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>).742    </p></div><p>743    Here is another example:  it calculates the total sales for each744    product (rather than the total sales of all products):745</p><pre class="programlisting">746SELECT product_id, p.name, (sum(s.units) * p.price) AS sales747    FROM products p LEFT JOIN sales s USING (product_id)748    GROUP BY product_id, p.name, p.price;749</pre><p>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 be752    in the <code class="literal">GROUP BY</code> clause since they are referenced in753    the query select list (but see below).  The column754    <code class="literal">s.units</code> does not have to be in the <code class="literal">GROUP755    BY</code> list since it is only used in an aggregate expression756    (<code class="literal">sum(...)</code>), which represents the sales757    of a product.  For each product, the query returns a summary row about758    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 be762    enough to group by <code class="literal">product_id</code> in the above example,763    since name and price would be <em class="firstterm">functionally764    dependent</em> on the product ID, and so there would be no765    ambiguity about which name and price value to return for each product766    ID group.767   </p><p>768    In strict SQL, <code class="literal">GROUP BY</code> can only group by columns of769    the source table but <span class="productname">PostgreSQL</span> extends770    this to also allow <code class="literal">GROUP BY</code> to group by columns in the771    select list.  Grouping by value expressions instead of simple772    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, the776    <code class="literal">HAVING</code> clause can be used, much like a777    <code class="literal">WHERE</code> clause, to eliminate groups from the result.778    The syntax is:779</p><pre class="synopsis">780SELECT <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>781</pre><p>782    Expressions in the <code class="literal">HAVING</code> clause can refer both to783    grouped expressions and to ungrouped expressions (which necessarily784    involve an aggregate function).785   </p><p>786    Example:787</p><pre class="screen">788<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) &gt; 3;</code></strong>789 x | sum790---+-----791 a |   4792 b |   5793(2 rows)794 795<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x &lt; 'c';</code></strong>796 x | sum797---+-----798 a |   4799 b |   5800(2 rows)801</pre><p>802   </p><p>803    Again, a more realistic example:804</p><pre class="programlisting">805SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit806    FROM products p LEFT JOIN sales s USING (product_id)807    WHERE s.date &gt; CURRENT_DATE - INTERVAL '4 weeks'808    GROUP BY product_id, p.name, p.price, p.cost809    HAVING sum(p.price * s.units) &gt; 5000;810</pre><p>811    In the example above, the <code class="literal">WHERE</code> clause is selecting812    rows by a column that is not grouped (the expression is only true for813    sales during the last four weeks), while the <code class="literal">HAVING</code>814    clause restricts the output to groups with total gross sales over815    5000.  Note that the aggregate expressions do not necessarily need816    to be the same in all parts of the query.817   </p><p>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 (or820    perhaps no rows at all, if the single row is then eliminated by821    <code class="literal">HAVING</code>).822    The same is true if it contains a <code class="literal">HAVING</code> clause, even823    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 possible826    using the concept of <em class="firstterm">grouping sets</em>.  The data selected by827    the <code class="literal">FROM</code> and <code class="literal">WHERE</code> clauses is grouped separately828    by each specified grouping set, aggregates computed for each group just as829    for simple <code class="literal">GROUP BY</code> clauses, and then the results returned.830    For example:831</p><pre class="screen">832<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT * FROM items_sold;</code></strong>833 brand | size | sales834-------+------+-------835 Foo   | L    |  10836 Foo   | M    |  20837 Bar   | M    |  15838 Bar   | L    |  5839(4 rows)840 841<code class="prompt">=&gt;</code> <strong class="userinput"><code>SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</code></strong>842 brand | size | sum843-------+------+-----844 Foo   |      |  30845 Bar   |      |  20846       | L    |  15847       | M    |  35848       |      |  50849(5 rows)850</pre><p>851   </p><p>852    Each sublist of <code class="literal">GROUPING SETS</code> may specify zero or more columns853    or expressions and is interpreted the same way as though it were directly854    in the <code class="literal">GROUP BY</code> clause.  An empty grouping set means that all855    rows are aggregated down to a single group (which is output even if no856    input rows were present), as described above for the case of aggregate857    functions with no <code class="literal">GROUP BY</code> clause.858   </p><p>859    References to the grouping columns or expressions are replaced860    by null values in result rows for grouping sets in which those861    columns do not appear.  To distinguish which grouping a particular output862    row resulted from, see <a class="xref" href="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" title="Table 9.63. Grouping Operations">Table 9.63</a>.863   </p><p>864    A shorthand notation is provided for specifying two common types of grouping set.865    A clause of the form866</p><pre class="programlisting">867ROLLUP ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, <em class="replaceable"><code>e3</code></em>, ... )868</pre><p>869    represents the given list of expressions and all prefixes of the list including870    the empty list; thus it is equivalent to871</p><pre class="programlisting">872GROUPING SETS (873    ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, <em class="replaceable"><code>e3</code></em>, ... ),874    ...875    ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em> ),876    ( <em class="replaceable"><code>e1</code></em> ),877    ( )878)879</pre><p>880    This is commonly used for analysis over hierarchical data; e.g., total881    salary by department, division, and company-wide total.882   </p><p>883    A clause of the form884</p><pre class="programlisting">885CUBE ( <em class="replaceable"><code>e1</code></em>, <em class="replaceable"><code>e2</code></em>, ... )886</pre><p>887    represents the given list and all of its possible subsets (i.e., the power888    set).  Thus889</p><pre class="programlisting">890CUBE ( a, b, c )891</pre><p>892    is equivalent to893</p><pre class="programlisting">894GROUPING SETS (895    ( a, b, c ),896    ( a, b    ),897    ( a,    c ),898    ( a       ),899    (    b, c ),900    (    b    ),901    (       c ),902    (         )903)904</pre><p>905   </p><p>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 in908    parentheses.  In the latter case, the sublists are treated as single909    units for the purposes of generating the individual grouping sets.910    For example:911</p><pre class="programlisting">912CUBE ( (a, b), (c, d) )913</pre><p>914    is equivalent to915</p><pre class="programlisting">916GROUPING SETS (917    ( a, b, c, d ),918    ( a, b       ),919    (       c, d ),920    (            )921)922</pre><p>923    and924</p><pre class="programlisting">925ROLLUP ( a, (b, c), d )926</pre><p>927    is equivalent to928</p><pre class="programlisting">929GROUPING SETS (930    ( a, b, c, d ),931    ( a, b, c    ),932    ( a          ),933    (            )934)935</pre><p>936   </p><p>937    The <code class="literal">CUBE</code> and <code class="literal">ROLLUP</code> constructs can be used either938    directly in the <code class="literal">GROUP BY</code> clause, or nested inside a939    <code class="literal">GROUPING SETS</code> clause.  If one <code class="literal">GROUPING SETS</code> clause940    is nested inside another, the effect is the same as if all the elements of941    the inner clause had been written directly in the outer clause.942   </p><p>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 cross product of the945    individual items.  For example:946</p><pre class="programlisting">947GROUP BY a, CUBE (b, c), GROUPING SETS ((d), (e))948</pre><p>949    is equivalent to950</p><pre class="programlisting">951GROUP 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),955    (a, d),       (a, e)956)957</pre><p>958   </p><p>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 grouping962    sets might contain duplicates. For example:963</p><pre class="programlisting">964GROUP BY ROLLUP (a, b), ROLLUP (a, c)965</pre><p>966    is equivalent to967</p><pre class="programlisting">968GROUP BY GROUPING SETS (969    (a, b, c),970    (a, b),971    (a, b),972    (a, c),973    (a),974    (a),975    (a, c),976    (a),977    ()978)979</pre><p>980    If these duplicates are undesirable, they can be removed using the981    <code class="literal">DISTINCT</code> clause directly on the <code class="literal">GROUP BY</code>.982    Therefore:983</p><pre class="programlisting">984GROUP BY <span class="emphasis"><strong>DISTINCT</strong></span> ROLLUP (a, b), ROLLUP (a, c)985</pre><p>986    is equivalent to987</p><pre class="programlisting">988GROUP BY GROUPING SETS (989    (a, b, c),990    (a, b),991    (a, c),992    (a),993    ()994)995</pre><p>996    This is not the same as using <code class="literal">SELECT DISTINCT</code> because the output997    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 as1001    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 top1003    levels of expressions, and <code class="literal">(a, b)</code> is parsed as a list of1004    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 (see1008    <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> and1010    <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 evaluated1011    after any grouping, aggregation, and <code class="literal">HAVING</code> filtering is1012    performed.  That is, if the query uses any aggregates, <code class="literal">GROUP1013    BY</code>, or <code class="literal">HAVING</code>, then the rows seen by the window functions1014    are the group rows instead of the original table rows from1015    <code class="literal">FROM</code>/<code class="literal">WHERE</code>.1016   </p><p>1017    When multiple window functions are used, all the window functions having1018    syntactically equivalent <code class="literal">PARTITION BY</code> and <code class="literal">ORDER BY</code>1019    clauses in their window definitions are guaranteed to be evaluated in a1020    single pass over the data. Therefore they will see the same sort ordering,1021    even if the <code class="literal">ORDER BY</code> does not uniquely determine an ordering.1022    However, no guarantees are made about the evaluation of functions having1023    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 of1025    window function evaluations, and the sort is not guaranteed to preserve1026    ordering of rows that its <code class="literal">ORDER BY</code> sees as equivalent.)1027   </p><p>1028    Currently, window functions always require presorted data, and so the1029    query output will be ordered according to one or another of the window1030    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 explicit1032    top-level <code class="literal">ORDER BY</code> clause if you want to be sure the1033    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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 7.3. Select Lists</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai