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