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