<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >Table Expressions</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 9.6.19 Documentation" HREF="index.html"><LINK REL="UP" TITLE="Queries" HREF="queries.html"><LINK REL="PREVIOUS" TITLE="Overview" HREF="queries-overview.html"><LINK REL="NEXT" TITLE="Select Lists" HREF="queries-select-lists.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2020-09-02T07:27:19"></HEAD ><BODY CLASS="SECT1" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="4" ALIGN="center" VALIGN="bottom" ><A HREF="index.html" >PostgreSQL 9.6.19 Documentation</A ></TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A TITLE="Overview" HREF="queries-overview.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="queries.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" >Chapter 7. Queries</TD ><TD WIDTH="20%" ALIGN="right" VALIGN="top" ><A TITLE="Select Lists" HREF="queries-select-lists.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><DIV CLASS="SECT1" ><H1 CLASS="SECT1" ><A NAME="QUERIES-TABLE-EXPRESSIONS" >7.2. Table Expressions</A ></H1 ><P > A <I CLASS="FIRSTTERM" >table expression</I > computes a table. The table expression contains a <TT CLASS="LITERAL" >FROM</TT > clause that is optionally followed by <TT CLASS="LITERAL" >WHERE</TT >, <TT CLASS="LITERAL" >GROUP BY</TT >, and <TT CLASS="LITERAL" >HAVING</TT > 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 <TT CLASS="LITERAL" >WHERE</TT >, <TT CLASS="LITERAL" >GROUP BY</TT >, and <TT CLASS="LITERAL" >HAVING</TT > clauses in the table expression specify a pipeline of successive transformations performed on the table derived in the <TT CLASS="LITERAL" >FROM</TT > 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" ><H2 CLASS="SECT2" ><A NAME="QUERIES-FROM" >7.2.1. The <TT CLASS="LITERAL" >FROM</TT > Clause</A ></H2 ><P > The <A HREF="sql-select.html#SQL-FROM" ><I ><I >FROM</I > Clause</I ></A > derives a table from one or more other tables given in a comma-separated table reference list. </P><PRE CLASS="SYNOPSIS" >FROM <TT CLASS="REPLACEABLE" ><I >table_reference</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >table_reference</I ></TT > [<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 <TT CLASS="LITERAL" >JOIN</TT > construct, or complex combinations of these. If more than one table reference is listed in the <TT CLASS="LITERAL" >FROM</TT > clause, the tables are cross-joined (that is, the Cartesian product of their rows is formed; see below). The result of the <TT CLASS="LITERAL" >FROM</TT > list is an intermediate virtual table that can then be subject to transformations by the <TT CLASS="LITERAL" >WHERE</TT >, <TT CLASS="LITERAL" >GROUP BY</TT >, and <TT CLASS="LITERAL" >HAVING</TT > clauses and is finally the result of the overall table expression. </P ><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 <TT CLASS="LITERAL" >ONLY</TT > 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 <TT CLASS="LITERAL" >ONLY</TT > before the table name, you can write <TT CLASS="LITERAL" >*</TT > after the table name to explicitly specify that descendant tables are included. Writing <TT CLASS="LITERAL" >*</TT > is not necessary since that behavior is the default (unless you have changed the setting of the <A HREF="runtime-config-compatible.html#GUC-SQL-INHERITANCE" >sql_inheritance</A > configuration option). However writing <TT CLASS="LITERAL" >*</TT > might be useful to emphasize that additional tables will be searched. </P ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="QUERIES-JOIN" >7.2.1.1. Joined Tables</A ></H3 ><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" ><TT CLASS="REPLACEABLE" ><I >T1</I ></TT > <TT CLASS="REPLACEABLE" ><I >join_type</I ></TT > <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > [<SPAN CLASS="OPTIONAL" > <TT CLASS="REPLACEABLE" ><I >join_condition</I ></TT > </SPAN >]</PRE ><P> Joins of all types can be chained together, or nested: either or both <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > and <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > can be joined tables. Parentheses can be used around <TT CLASS="LITERAL" >JOIN</TT > clauses to control the join order. In the absence of parentheses, <TT CLASS="LITERAL" >JOIN</TT > clauses nest left-to-right. </P ><P ></P ><DIV CLASS="VARIABLELIST" ><P ><B >Join Types</B ></P ><DL ><DT >Cross join </DT ><DD ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >T1</I ></TT > CROSS JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT ></PRE ><P > For every possible combination of rows from <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > and <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > followed by all columns in <TT CLASS="REPLACEABLE" ><I >T2</I ></TT >. If the tables have N and M rows respectively, the joined table will have N * M rows. </P ><P > <TT CLASS="LITERAL" >FROM <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > CROSS JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT ></TT > is equivalent to <TT CLASS="LITERAL" >FROM <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > INNER JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > ON TRUE</TT > (see below). It is also equivalent to <TT CLASS="LITERAL" >FROM <TT CLASS="REPLACEABLE" ><I >T1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >T2</I ></TT ></TT >. </P><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > This latter equivalence does not hold exactly when more than two tables appear, because <TT CLASS="LITERAL" >JOIN</TT > binds more tightly than comma. For example <TT CLASS="LITERAL" >FROM <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > CROSS JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > INNER JOIN <TT CLASS="REPLACEABLE" ><I >T3</I ></TT > ON <TT CLASS="REPLACEABLE" ><I >condition</I ></TT ></TT > is not the same as <TT CLASS="LITERAL" >FROM <TT CLASS="REPLACEABLE" ><I >T1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > INNER JOIN <TT CLASS="REPLACEABLE" ><I >T3</I ></TT > ON <TT CLASS="REPLACEABLE" ><I >condition</I ></TT ></TT > because the <TT CLASS="REPLACEABLE" ><I >condition</I ></TT > can reference <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > in the first case but not the second. </P ></BLOCKQUOTE ></DIV ><P> </P ></DD ><DT >Qualified joins </DT ><DD ><PRE CLASS="SYNOPSIS" ><TT CLASS="REPLACEABLE" ><I >T1</I ></TT > { [<SPAN CLASS="OPTIONAL" >INNER</SPAN >] | { LEFT | RIGHT | FULL } [<SPAN CLASS="OPTIONAL" >OUTER</SPAN >] } JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > ON <TT CLASS="REPLACEABLE" ><I >boolean_expression</I ></TT > <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > { [<SPAN CLASS="OPTIONAL" >INNER</SPAN >] | { LEFT | RIGHT | FULL } [<SPAN CLASS="OPTIONAL" >OUTER</SPAN >] } JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > USING ( <TT CLASS="REPLACEABLE" ><I >join column list</I ></TT > ) <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > NATURAL { [<SPAN CLASS="OPTIONAL" >INNER</SPAN >] | { LEFT | RIGHT | FULL } [<SPAN CLASS="OPTIONAL" >OUTER</SPAN >] } JOIN <TT CLASS="REPLACEABLE" ><I >T2</I ></TT ></PRE ><P > The words <TT CLASS="LITERAL" >INNER</TT > and <TT CLASS="LITERAL" >OUTER</TT > are optional in all forms. <TT CLASS="LITERAL" >INNER</TT > is the default; <TT CLASS="LITERAL" >LEFT</TT >, <TT CLASS="LITERAL" >RIGHT</TT >, and <TT CLASS="LITERAL" >FULL</TT > imply an outer join. </P ><P > The <I CLASS="FIRSTTERM" >join condition</I > is specified in the <TT CLASS="LITERAL" >ON</TT > or <TT CLASS="LITERAL" >USING</TT > clause, or implicitly by the word <TT CLASS="LITERAL" >NATURAL</TT >. The join condition determines which rows from the two source tables are considered to <SPAN CLASS="QUOTE" >"match"</SPAN >, as explained in detail below. </P ><P > The possible types of qualified join are: <P ></P ></P><DIV CLASS="VARIABLELIST" ><DL ><DT ><TT CLASS="LITERAL" >INNER JOIN</TT ></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 ><TT CLASS="LITERAL" >LEFT OUTER JOIN</TT > </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 ><TT CLASS="LITERAL" >RIGHT OUTER JOIN</TT > </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 ><TT CLASS="LITERAL" >FULL OUTER JOIN</TT ></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 <TT CLASS="LITERAL" >ON</TT > clause is the most general kind of join condition: it takes a Boolean value expression of the same kind as is used in a <TT CLASS="LITERAL" >WHERE</TT > clause. A pair of rows from <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > and <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > match if the <TT CLASS="LITERAL" >ON</TT > expression evaluates to true. </P ><P > The <TT CLASS="LITERAL" >USING</TT > 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 <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > and <TT CLASS="REPLACEABLE" ><I >T2</I ></TT > with <TT CLASS="LITERAL" >USING (a, b)</TT > produces the join condition <TT CLASS="LITERAL" >ON <TT CLASS="REPLACEABLE" ><I >T1</I ></TT >.a = <TT CLASS="REPLACEABLE" ><I >T2</I ></TT >.a AND <TT CLASS="REPLACEABLE" ><I >T1</I ></TT >.b = <TT CLASS="REPLACEABLE" ><I >T2</I ></TT >.b</TT >. </P ><P > Furthermore, the output of <TT CLASS="LITERAL" >JOIN USING</TT > suppresses redundant columns: there is no need to print both of the matched columns, since they must have equal values. While <TT CLASS="LITERAL" >JOIN ON</TT > produces all columns from <TT CLASS="REPLACEABLE" ><I >T1</I ></TT > followed by all columns from <TT CLASS="REPLACEABLE" ><I >T2</I ></TT >, <TT CLASS="LITERAL" >JOIN USING</TT > produces one output column for each of the listed column pairs (in the listed order), followed by any remaining columns from <TT CLASS="REPLACEABLE" ><I >T1</I ></TT >, followed by any remaining columns from <TT CLASS="REPLACEABLE" ><I >T2</I ></TT >. </P ><P > Finally, <TT CLASS="LITERAL" >NATURAL</TT > is a shorthand form of <TT CLASS="LITERAL" >USING</TT >: it forms a <TT CLASS="LITERAL" >USING</TT > list consisting of all column names that appear in both input tables. As with <TT CLASS="LITERAL" >USING</TT >, these columns appear only once in the output table. If there are no common column names, <TT CLASS="LITERAL" >NATURAL JOIN</TT > behaves like <TT CLASS="LITERAL" >JOIN ... ON TRUE</TT >, producing a cross-product join. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > <TT CLASS="LITERAL" >USING</TT > is reasonably safe from column changes in the joined relations since only the listed columns are combined. <TT CLASS="LITERAL" >NATURAL</TT > 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 ></BLOCKQUOTE ></DIV ></DD ></DL ></DIV ><P > To put this together, assume we have tables <TT CLASS="LITERAL" >t1</TT >: </P><PRE CLASS="PROGRAMLISTING" > num | name -----+------ 1 | a 2 | b 3 | c</PRE ><P> and <TT CLASS="LITERAL" >t2</TT >: </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" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 CROSS JOIN t2;</KBD > 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) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</KBD > num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy (2 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 INNER JOIN t2 USING (num);</KBD > num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 NATURAL INNER JOIN t2;</KBD > num | name | value -----+------+------- 1 | a | xxx 3 | c | yyy (2 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</KBD > num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | 3 | yyy (3 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 LEFT JOIN t2 USING (num);</KBD > num | name | value -----+------+------- 1 | a | xxx 2 | b | 3 | c | yyy (3 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</KBD > num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 3 | c | 3 | yyy | | 5 | zzz (3 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</KBD > 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 <TT CLASS="LITERAL" >ON</TT > 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" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</KBD > num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx 2 | b | | 3 | c | | (3 rows)</PRE ><P> Notice that placing the restriction in the <TT CLASS="LITERAL" >WHERE</TT > clause produces a different result: </P><PRE CLASS="SCREEN" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</KBD > num | name | num | value -----+------+-----+------- 1 | a | 1 | xxx (1 row)</PRE ><P> This is because a restriction placed in the <TT CLASS="LITERAL" >ON</TT > clause is processed <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >before</I ></SPAN > the join, while a restriction placed in the <TT CLASS="LITERAL" >WHERE</TT > clause is processed <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >after</I ></SPAN > the join. That does not matter with inner joins, but it matters a lot with outer joins. </P ></DIV ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="QUERIES-TABLE-ALIASES" >7.2.1.2. Table and Column Aliases</A ></H3 ><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 <I CLASS="FIRSTTERM" >table alias</I >. </P ><P > To create a table alias, write </P><PRE CLASS="SYNOPSIS" >FROM <TT CLASS="REPLACEABLE" ><I >table_reference</I ></TT > AS <TT CLASS="REPLACEABLE" ><I >alias</I ></TT ></PRE ><P> or </P><PRE CLASS="SYNOPSIS" >FROM <TT CLASS="REPLACEABLE" ><I >table_reference</I ></TT > <TT CLASS="REPLACEABLE" ><I >alias</I ></TT ></PRE ><P> The <TT CLASS="LITERAL" >AS</TT > key word is optional noise. <TT CLASS="REPLACEABLE" ><I >alias</I ></TT > 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> Additionally, an alias is required if the table reference is a subquery (see <A HREF="queries-table-expressions.html#QUERIES-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 <TT CLASS="LITERAL" >b</TT > to the second instance of <TT CLASS="LITERAL" >my_table</TT >, 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 <TT CLASS="REPLACEABLE" ><I >table_reference</I ></TT > [<SPAN CLASS="OPTIONAL" >AS</SPAN >] <TT CLASS="REPLACEABLE" ><I >alias</I ></TT > ( <TT CLASS="REPLACEABLE" ><I >column1</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >column2</I ></TT > [<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 <TT CLASS="LITERAL" >JOIN</TT > clause, the alias hides the original name(s) within the <TT CLASS="LITERAL" >JOIN</TT >. 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 <TT CLASS="LITERAL" >a</TT > is not visible outside the alias <TT CLASS="LITERAL" >c</TT >. </P ></DIV ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="QUERIES-SUBQUERIES" >7.2.1.3. Subqueries</A ></H3 ><P > Subqueries specifying a derived table must be enclosed in parentheses and <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >must</I ></SPAN > be assigned a table alias name (as in <A HREF="queries-table-expressions.html#QUERIES-TABLE-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 <TT CLASS="LITERAL" >FROM table1 AS alias_name</TT >. 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 <TT CLASS="COMMAND" >VALUES</TT > 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 <TT CLASS="COMMAND" >VALUES</TT > list is optional, but is good practice. For more information see <A HREF="queries-values.html" >Section 7.7</A >. </P ></DIV ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="QUERIES-TABLEFUNCTIONS" >7.2.1.4. Table Functions</A ></H3 ><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 <TT CLASS="LITERAL" >FROM</TT > clause of a query. Columns returned by table functions can be included in <TT CLASS="LITERAL" >SELECT</TT >, <TT CLASS="LITERAL" >JOIN</TT >, or <TT CLASS="LITERAL" >WHERE</TT > clauses in the same manner as columns of a table, view, or subquery. </P ><P > Table functions may also be combined using the <TT CLASS="LITERAL" >ROWS FROM</TT > 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" ><TT CLASS="REPLACEABLE" ><I >function_call</I ></TT > [<SPAN CLASS="OPTIONAL" >WITH ORDINALITY</SPAN >] [<SPAN CLASS="OPTIONAL" >[<SPAN CLASS="OPTIONAL" >AS</SPAN >] <TT CLASS="REPLACEABLE" ><I >table_alias</I ></TT > [<SPAN CLASS="OPTIONAL" >(<TT CLASS="REPLACEABLE" ><I >column_alias</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >])</SPAN >]</SPAN >] ROWS FROM( <TT CLASS="REPLACEABLE" ><I >function_call</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >] ) [<SPAN CLASS="OPTIONAL" >WITH ORDINALITY</SPAN >] [<SPAN CLASS="OPTIONAL" >[<SPAN CLASS="OPTIONAL" >AS</SPAN >] <TT CLASS="REPLACEABLE" ><I >table_alias</I ></TT > [<SPAN CLASS="OPTIONAL" >(<TT CLASS="REPLACEABLE" ><I >column_alias</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >])</SPAN >]</SPAN >]</PRE ><P > If the <TT CLASS="LITERAL" >WITH ORDINALITY</TT > clause is specified, an additional column of type <TT CLASS="TYPE" >bigint</TT > 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 <TT CLASS="LITERAL" >UNNEST ... WITH ORDINALITY</TT >.) By default, the ordinal column is called <TT CLASS="LITERAL" >ordinality</TT >, but a different column name can be assigned to it using an <TT CLASS="LITERAL" >AS</TT > clause. </P ><P > The special table function <TT CLASS="LITERAL" >UNNEST</TT > may be called with any number of array parameters, and it returns a corresponding number of columns, as if <TT CLASS="LITERAL" >UNNEST</TT > (<A HREF="functions-array.html" >Section 9.18</A >) had been called on each parameter separately and combined using the <TT CLASS="LITERAL" >ROWS FROM</TT > construct. </P ><PRE CLASS="SYNOPSIS" >UNNEST( <TT CLASS="REPLACEABLE" ><I >array_expression</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >] ) [<SPAN CLASS="OPTIONAL" >WITH ORDINALITY</SPAN >] [<SPAN CLASS="OPTIONAL" >[<SPAN CLASS="OPTIONAL" >AS</SPAN >] <TT CLASS="REPLACEABLE" ><I >table_alias</I ></TT > [<SPAN CLASS="OPTIONAL" >(<TT CLASS="REPLACEABLE" ><I >column_alias</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >])</SPAN >]</SPAN >]</PRE ><P > If no <TT CLASS="REPLACEABLE" ><I >table_alias</I ></TT > is specified, the function name is used as the table name; in the case of a <TT CLASS="LITERAL" >ROWS FROM()</TT > 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 pseudotype <TT CLASS="TYPE" >record</TT >. 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" ><TT CLASS="REPLACEABLE" ><I >function_call</I ></TT > [<SPAN CLASS="OPTIONAL" >AS</SPAN >] <TT CLASS="REPLACEABLE" ><I >alias</I ></TT > (<TT CLASS="REPLACEABLE" ><I >column_definition</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >]) <TT CLASS="REPLACEABLE" ><I >function_call</I ></TT > AS [<SPAN CLASS="OPTIONAL" ><TT CLASS="REPLACEABLE" ><I >alias</I ></TT ></SPAN >] (<TT CLASS="REPLACEABLE" ><I >column_definition</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >]) ROWS FROM( ... <TT CLASS="REPLACEABLE" ><I >function_call</I ></TT > AS (<TT CLASS="REPLACEABLE" ><I >column_definition</I ></TT > [<SPAN CLASS="OPTIONAL" >, ... </SPAN >]) [<SPAN CLASS="OPTIONAL" >, ... </SPAN >] )</PRE ><P > When not using the <TT CLASS="LITERAL" >ROWS FROM()</TT > syntax, the <TT CLASS="REPLACEABLE" ><I >column_definition</I ></TT > list replaces the column alias list that could otherwise be attached to the <TT CLASS="LITERAL" >FROM</TT > item; the names in the column definitions serve as column aliases. When using the <TT CLASS="LITERAL" >ROWS FROM()</TT > syntax, a <TT CLASS="REPLACEABLE" ><I >column_definition</I ></TT > list can be attached to each member function separately; or if there is only one member function and no <TT CLASS="LITERAL" >WITH ORDINALITY</TT > clause, a <TT CLASS="REPLACEABLE" ><I >column_definition</I ></TT > list can be written in place of a column alias list following <TT CLASS="LITERAL" >ROWS FROM()</TT >. </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 HREF="contrib-dblink-function.html" >dblink</A > function (part of the <A HREF="dblink.html" >dblink</A > module) executes a remote query. It is declared to return <TT CLASS="TYPE" >record</TT > 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 <TT CLASS="LITERAL" >*</TT > should expand to. </P ></DIV ><DIV CLASS="SECT3" ><H3 CLASS="SECT3" ><A NAME="QUERIES-LATERAL" >7.2.1.5. <TT CLASS="LITERAL" >LATERAL</TT > Subqueries</A ></H3 ><P > Subqueries appearing in <TT CLASS="LITERAL" >FROM</TT > can be preceded by the key word <TT CLASS="LITERAL" >LATERAL</TT >. This allows them to reference columns provided by preceding <TT CLASS="LITERAL" >FROM</TT > items. (Without <TT CLASS="LITERAL" >LATERAL</TT >, each subquery is evaluated independently and so cannot cross-reference any other <TT CLASS="LITERAL" >FROM</TT > item.) </P ><P > Table functions appearing in <TT CLASS="LITERAL" >FROM</TT > can also be preceded by the key word <TT CLASS="LITERAL" >LATERAL</TT >, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding <TT CLASS="LITERAL" >FROM</TT > items in any case. </P ><P > A <TT CLASS="LITERAL" >LATERAL</TT > item can appear at top level in the <TT CLASS="LITERAL" >FROM</TT > list, or within a <TT CLASS="LITERAL" >JOIN</TT > tree. In the latter case it can also refer to any items that are on the left-hand side of a <TT CLASS="LITERAL" >JOIN</TT > that it is on the right-hand side of. </P ><P > When a <TT CLASS="LITERAL" >FROM</TT > item contains <TT CLASS="LITERAL" >LATERAL</TT > cross-references, evaluation proceeds as follows: for each row of the <TT CLASS="LITERAL" >FROM</TT > item providing the cross-referenced column(s), or set of rows of multiple <TT CLASS="LITERAL" >FROM</TT > items providing the columns, the <TT CLASS="LITERAL" >LATERAL</TT > 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 <TT CLASS="LITERAL" >LATERAL</TT > 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> <TT CLASS="LITERAL" >LATERAL</TT > 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 <TT CLASS="LITERAL" >LATERAL</TT > key word is unnecessary in this example, but we use it for clarity.) </P ><P > It is often particularly handy to <TT CLASS="LITERAL" >LEFT JOIN</TT > to a <TT CLASS="LITERAL" >LATERAL</TT > subquery, so that source rows will appear in the result even if the <TT CLASS="LITERAL" >LATERAL</TT > 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" ><H2 CLASS="SECT2" ><A NAME="QUERIES-WHERE" >7.2.2. The <TT CLASS="LITERAL" >WHERE</TT > Clause</A ></H2 ><P > The syntax of the <A HREF="sql-select.html#SQL-WHERE" ><I ><I >WHERE</I > Clause</I ></A > is </P><PRE CLASS="SYNOPSIS" >WHERE <TT CLASS="REPLACEABLE" ><I >search_condition</I ></TT ></PRE ><P> where <TT CLASS="REPLACEABLE" ><I >search_condition</I ></TT > is any value expression (see <A HREF="sql-expressions.html" >Section 4.2</A >) that returns a value of type <TT CLASS="TYPE" >boolean</TT >. </P ><P > After the processing of the <TT CLASS="LITERAL" >FROM</TT > 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 <TT CLASS="LITERAL" >FROM</TT > clause; this is not required, but otherwise the <TT CLASS="LITERAL" >WHERE</TT > clause will be fairly useless. </P ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > The join condition of an inner join can be written either in the <TT CLASS="LITERAL" >WHERE</TT > clause or in the <TT CLASS="LITERAL" >JOIN</TT > 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 <TT CLASS="LITERAL" >JOIN</TT > syntax in the <TT CLASS="LITERAL" >FROM</TT > 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 <TT CLASS="LITERAL" >FROM</TT > clause. The <TT CLASS="LITERAL" >ON</TT > or <TT CLASS="LITERAL" >USING</TT > clause of an outer join is <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >not</I ></SPAN > equivalent to a <TT CLASS="LITERAL" >WHERE</TT > 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 ></BLOCKQUOTE ></DIV ><P > Here are some examples of <TT CLASS="LITERAL" >WHERE</TT > 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> <TT CLASS="LITERAL" >fdt</TT > is the table derived in the <TT CLASS="LITERAL" >FROM</TT > clause. Rows that do not meet the search condition of the <TT CLASS="LITERAL" >WHERE</TT > clause are eliminated from <TT CLASS="LITERAL" >fdt</TT >. Notice the use of scalar subqueries as value expressions. Just like any other query, the subqueries can employ complex table expressions. Notice also how <TT CLASS="LITERAL" >fdt</TT > is referenced in the subqueries. Qualifying <TT CLASS="LITERAL" >c1</TT > as <TT CLASS="LITERAL" >fdt.c1</TT > is only necessary if <TT CLASS="LITERAL" >c1</TT > 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" ><H2 CLASS="SECT2" ><A NAME="QUERIES-GROUP" >7.2.3. The <TT CLASS="LITERAL" >GROUP BY</TT > and <TT CLASS="LITERAL" >HAVING</TT > Clauses</A ></H2 ><P > After passing the <TT CLASS="LITERAL" >WHERE</TT > filter, the derived input table might be subject to grouping, using the <TT CLASS="LITERAL" >GROUP BY</TT > clause, and elimination of group rows using the <TT CLASS="LITERAL" >HAVING</TT > clause. </P ><PRE CLASS="SYNOPSIS" >SELECT <TT CLASS="REPLACEABLE" ><I >select_list</I ></TT > FROM ... [<SPAN CLASS="OPTIONAL" >WHERE ...</SPAN >] GROUP BY <TT CLASS="REPLACEABLE" ><I >grouping_column_reference</I ></TT > [<SPAN CLASS="OPTIONAL" >, <TT CLASS="REPLACEABLE" ><I >grouping_column_reference</I ></TT ></SPAN >]...</PRE ><P > The <A HREF="sql-select.html#SQL-GROUPBY" ><I ><I >GROUP BY</I > Clause</I ></A > 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" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM test1;</KBD > x | y ---+--- a | 3 c | 2 b | 5 a | 1 (4 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT x FROM test1 GROUP BY x;</KBD > x --- a b c (3 rows)</PRE ><P> </P ><P > In the second query, we could not have written <TT CLASS="LITERAL" >SELECT * FROM test1 GROUP BY x</TT >, because there is no single value for the column <TT CLASS="LITERAL" >y</TT > 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 <TT CLASS="LITERAL" >GROUP BY</TT > cannot be referenced except in aggregate expressions. An example with aggregate expressions is: </P><PRE CLASS="SCREEN" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT x, sum(y) FROM test1 GROUP BY x;</KBD > x | sum ---+----- a | 4 b | 5 c | 2 (3 rows)</PRE ><P> Here <TT CLASS="LITERAL" >sum</TT > 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 HREF="functions-aggregate.html" >Section 9.20</A >. </P ><DIV CLASS="TIP" ><BLOCKQUOTE CLASS="TIP" ><P ><B >Tip: </B > Grouping without aggregate expressions effectively calculates the set of distinct values in a column. This can also be achieved using the <TT CLASS="LITERAL" >DISTINCT</TT > clause (see <A HREF="queries-select-lists.html#QUERIES-DISTINCT" >Section 7.3.3</A >). </P ></BLOCKQUOTE ></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 <TT CLASS="LITERAL" >product_id</TT >, <TT CLASS="LITERAL" >p.name</TT >, and <TT CLASS="LITERAL" >p.price</TT > must be in the <TT CLASS="LITERAL" >GROUP BY</TT > clause since they are referenced in the query select list (but see below). The column <TT CLASS="LITERAL" >s.units</TT > does not have to be in the <TT CLASS="LITERAL" >GROUP BY</TT > list since it is only used in an aggregate expression (<TT CLASS="LITERAL" >sum(...)</TT >), which represents the sales of a product. For each product, the query returns a summary row about all sales of the product. </P ><P > If the products table is set up so that, say, <TT CLASS="LITERAL" >product_id</TT > is the primary key, then it would be enough to group by <TT CLASS="LITERAL" >product_id</TT > in the above example, since name and price would be <I CLASS="FIRSTTERM" >functionally dependent</I > 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, <TT CLASS="LITERAL" >GROUP BY</TT > can only group by columns of the source table but <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > extends this to also allow <TT CLASS="LITERAL" >GROUP BY</TT > to group by columns in the select list. Grouping by value expressions instead of simple column names is also allowed. </P ><P > If a table has been grouped using <TT CLASS="LITERAL" >GROUP BY</TT >, but only certain groups are of interest, the <TT CLASS="LITERAL" >HAVING</TT > clause can be used, much like a <TT CLASS="LITERAL" >WHERE</TT > clause, to eliminate groups from the result. The syntax is: </P><PRE CLASS="SYNOPSIS" >SELECT <TT CLASS="REPLACEABLE" ><I >select_list</I ></TT > FROM ... [<SPAN CLASS="OPTIONAL" >WHERE ...</SPAN >] GROUP BY ... HAVING <TT CLASS="REPLACEABLE" ><I >boolean_expression</I ></TT ></PRE ><P> Expressions in the <TT CLASS="LITERAL" >HAVING</TT > clause can refer both to grouped expressions and to ungrouped expressions (which necessarily involve an aggregate function). </P ><P > Example: </P><PRE CLASS="SCREEN" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) > 3;</KBD > x | sum ---+----- a | 4 b | 5 (2 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT x, sum(y) FROM test1 GROUP BY x HAVING x < 'c';</KBD > 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 <TT CLASS="LITERAL" >WHERE</TT > 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 <TT CLASS="LITERAL" >HAVING</TT > 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 <TT CLASS="LITERAL" >GROUP BY</TT > 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 <TT CLASS="LITERAL" >HAVING</TT >). The same is true if it contains a <TT CLASS="LITERAL" >HAVING</TT > clause, even without any aggregate function calls or <TT CLASS="LITERAL" >GROUP BY</TT > clause. </P ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="QUERIES-GROUPING-SETS" >7.2.4. <TT CLASS="LITERAL" >GROUPING SETS</TT >, <TT CLASS="LITERAL" >CUBE</TT >, and <TT CLASS="LITERAL" >ROLLUP</TT ></A ></H2 ><P > More complex grouping operations than those described above are possible using the concept of <I CLASS="FIRSTTERM" >grouping sets</I >. The data selected by the <TT CLASS="LITERAL" >FROM</TT > and <TT CLASS="LITERAL" >WHERE</TT > clauses is grouped separately by each specified grouping set, aggregates computed for each group just as for simple <TT CLASS="LITERAL" >GROUP BY</TT > clauses, and then the results returned. For example: </P><PRE CLASS="SCREEN" ><SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT * FROM items_sold;</KBD > brand | size | sales -------+------+------- Foo | L | 10 Foo | M | 20 Bar | M | 15 Bar | L | 5 (4 rows) <SAMP CLASS="PROMPT" >=></SAMP > <KBD CLASS="USERINPUT" >SELECT brand, size, sum(sales) FROM items_sold GROUP BY GROUPING SETS ((brand), (size), ());</KBD > brand | size | sum -------+------+----- Foo | | 30 Bar | | 20 | L | 15 | M | 35 | | 50 (5 rows)</PRE ><P> </P ><P > Each sublist of <TT CLASS="LITERAL" >GROUPING SETS</TT > may specify zero or more columns or expressions and is interpreted the same way as though it were directly in the <TT CLASS="LITERAL" >GROUP BY</TT > 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 <TT CLASS="LITERAL" >GROUP BY</TT > 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 HREF="functions-aggregate.html#FUNCTIONS-GROUPING-TABLE" >Table 9-55</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 ( <TT CLASS="REPLACEABLE" ><I >e1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >e2</I ></TT >, <TT CLASS="REPLACEABLE" ><I >e3</I ></TT >, ... )</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 ( ( <TT CLASS="REPLACEABLE" ><I >e1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >e2</I ></TT >, <TT CLASS="REPLACEABLE" ><I >e3</I ></TT >, ... ), ... ( <TT CLASS="REPLACEABLE" ><I >e1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >e2</I ></TT > ), ( <TT CLASS="REPLACEABLE" ><I >e1</I ></TT > ), ( ) )</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 ( <TT CLASS="REPLACEABLE" ><I >e1</I ></TT >, <TT CLASS="REPLACEABLE" ><I >e2</I ></TT >, ... )</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 <TT CLASS="LITERAL" >CUBE</TT > or <TT CLASS="LITERAL" >ROLLUP</TT > 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 <TT CLASS="LITERAL" >CUBE</TT > and <TT CLASS="LITERAL" >ROLLUP</TT > constructs can be used either directly in the <TT CLASS="LITERAL" >GROUP BY</TT > clause, or nested inside a <TT CLASS="LITERAL" >GROUPING SETS</TT > clause. If one <TT CLASS="LITERAL" >GROUPING SETS</TT > 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 <TT CLASS="LITERAL" >GROUP BY</TT > 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 ><DIV CLASS="NOTE" ><BLOCKQUOTE CLASS="NOTE" ><P ><B >Note: </B > The construct <TT CLASS="LITERAL" >(a, b)</TT > is normally recognized in expressions as a <A HREF="sql-expressions.html#SQL-SYNTAX-ROW-CONSTRUCTORS" >row constructor</A >. Within the <TT CLASS="LITERAL" >GROUP BY</TT > clause, this does not apply at the top levels of expressions, and <TT CLASS="LITERAL" >(a, b)</TT > is parsed as a list of expressions as described above. If for some reason you <SPAN CLASS="emphasis" ><I CLASS="EMPHASIS" >need</I ></SPAN > a row constructor in a grouping expression, use <TT CLASS="LITERAL" >ROW(a, b)</TT >. </P ></BLOCKQUOTE ></DIV ></DIV ><DIV CLASS="SECT2" ><H2 CLASS="SECT2" ><A NAME="QUERIES-WINDOW" >7.2.5. Window Function Processing</A ></H2 ><P > If the query contains any window functions (see <A HREF="tutorial-window.html" >Section 3.5</A >, <A HREF="functions-window.html" >Section 9.21</A > and <A HREF="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS" >Section 4.2.8</A >), these functions are evaluated after any grouping, aggregation, and <TT CLASS="LITERAL" >HAVING</TT > filtering is performed. That is, if the query uses any aggregates, <TT CLASS="LITERAL" >GROUP BY</TT >, or <TT CLASS="LITERAL" >HAVING</TT >, then the rows seen by the window functions are the group rows instead of the original table rows from <TT CLASS="LITERAL" >FROM</TT >/<TT CLASS="LITERAL" >WHERE</TT >. </P ><P > When multiple window functions are used, all the window functions having syntactically equivalent <TT CLASS="LITERAL" >PARTITION BY</TT > and <TT CLASS="LITERAL" >ORDER BY</TT > 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 <TT CLASS="LITERAL" >ORDER BY</TT > does not uniquely determine an ordering. However, no guarantees are made about the evaluation of functions having different <TT CLASS="LITERAL" >PARTITION BY</TT > or <TT CLASS="LITERAL" >ORDER BY</TT > 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 <TT CLASS="LITERAL" >ORDER BY</TT > 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' <TT CLASS="LITERAL" >PARTITION BY</TT >/<TT CLASS="LITERAL" >ORDER BY</TT > clauses. It is not recommended to rely on this, however. Use an explicit top-level <TT CLASS="LITERAL" >ORDER BY</TT > clause if you want to be sure the results are sorted in a particular way. </P ></DIV ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="queries-overview.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="queries-select-lists.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >Overview</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="queries.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >Select Lists</TD ></TR ></TABLE ></DIV ></BODY ></HTML >