codekingpro/portable-devtools
114k
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">=></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">=></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">=></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">=></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">=></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">=></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">=></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">=></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">=></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">=></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 > 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 <-> v2) < 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 <-> v2) < 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 > 5633</pre><p>634 and:635</p><pre class="programlisting">636FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val > 5637</pre><p>638 or perhaps even:639</p><pre class="programlisting">640FROM a NATURAL JOIN b WHERE b.val > 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 > 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 > 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">=></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">=></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">=></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">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;</code></strong>789 x | sum790---+-----791 a | 4792 b | 5793(2 rows)794 795<code class="prompt">=></code> <strong class="userinput"><code>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < '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 > CURRENT_DATE - INTERVAL '4 weeks'808 GROUP BY product_id, p.name, p.price, p.cost809 HAVING sum(p.price * s.units) > 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">=></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">=></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>