From: Robert B. Easter <reaster@comptechnews.com>
To: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: pgsql-patches@postgresql.org
Subject: Re: docs: syntax.sgml patch
Date: Thu, 18 Jan 2001 20:38:26 -0500
Message-ID: <0101182038260L.02219@comptechnews> (raw)
In-Reply-To: <Pine.LNX.4.30.0101190048330.3124-100000@peter.localdomain>
References: <Pine.LNX.4.30.0101190048330.3124-100000@peter.localdomain>
I worked on the ORDER BY paragraphs some more. See what you think of it now.
The patch is attached. :)
On Thursday 18 January 2001 18:48, Peter Eisentraut wrote:
> Bruce Momjian writes:
> > I know this SQL Guide has been discussed, but what should be done with
> > this patch?
>
> I believe he's going to revise it to address Tom Lane's suggestions.
--
-------- Robert B. Easter reaster@comptechnews.com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------
Attachments:syntax.sgml.patch.3 (text/english, 23.2K) download
*** syntax.sgml.orig Sat Jan 13 23:18:57 2001
--- syntax.sgml.modi.3 Thu Jan 18 20:35:03 2001
***************
*** 697,702 ****
--- 697,703 ----
<member>parameter</member>
<member>functional expression</member>
<member>aggregate expression</member>
+ <member>table expression</member>
</simplelist>
</para>
***************
*** 838,897 ****
</para>
</sect2>
- <sect2>
- <title>From List</title>
-
- <para>
- The <firstterm>from list</firstterm>
- is a comma-separated list of <firstterm>from-expressions</firstterm>.
- The simplest possibility for a from-expression is:
-
- <synopsis>
- <replaceable>table_reference</replaceable> [ [ AS ] <replaceable class="PARAMETER">alias</replaceable> ]
- </synopsis>
-
- where <replaceable>table_reference</replaceable> is of the form
-
- <synopsis>
- [ ONLY ] <replaceable class="PARAMETER">table_name</replaceable> [ * ]
- </synopsis>
-
- The from-expression defines an instance variable that ranges over the
- rows of the specified table. The instance variable's name is either
- the table name, or the <replaceable>alias</replaceable> if one is given.
- Ordinarily, if the table has child tables then the instance variable
- will range over all rows in the inheritance hierarchy starting with
- the specified table. If <literal>ONLY</literal> is specified then
- child tables are not included. A trailing asterisk <literal>*</literal>
- can be written to specifically indicate that child tables are included
- (<literal>ONLY</literal> and <literal>*</literal> are mutually
- exclusive).
- </para>
! <para>
! A from-expression can also be a sub-query:
!
! <synopsis>
! ( <replaceable class="PARAMETER">select-statement</replaceable> ) [ AS ] <replaceable class="PARAMETER">alias</replaceable>
! </synopsis>
! Here, the effect is as though the SELECT were executed and its results
! stored in a temporary table, which then becomes available as an instance
! variable under the given <replaceable>alias</replaceable>.
! </para>
! <para>
! Finally, a from-expression can be built up from simpler from-expressions
! using JOIN clauses:
! <synopsis>
! <replaceable class="PARAMETER">from_expression</replaceable> [ NATURAL ] <replaceable class="PARAMETER">join_type</replaceable> <replaceable class="PARAMETER">from_expression</replaceable>
! [ ON <replaceable class="PARAMETER">join_condition</replaceable> | USING ( <replaceable class="PARAMETER">join_column_list</replaceable> ) ]
! </synopsis>
! This syntax allows specification of <firstterm>outer joins</firstterm>.
! For details see the reference page for SELECT.
! </para>
</sect2>
--- 839,1360 ----
</para>
</sect2>
! <sect2>
! <title>Table Expressions</title>
! <para>
! A <replaceable>table expression</replaceable> specifies a table
! or grouped table.
! </para>
!
! <para>
! The table expression contains a FROM clause that is optionally followed
! by WHERE, GROUP BY, and HAVING clauses. The table resulting from a table expression,
! called a derived table, is the input table of a query. The
! input table is in scope inside the query and any subqueries.
! </para>
!
! <para>
! The WHERE, GROUP BY, and HAVING clauses in the table expression specify
! a pipeline of successive transformations performed on the initial input table
! derived in the FROM clause. The final transformed input table that is
! derived provides the input rows used to derive output rows as specified by the select
! list of derived column value expressions.
! </para>
!
! <para>
! <cmdsynopsis>
! <command>SELECT</command>
! <arg choice="req">select list</arg>
! <arg choice="req">table expression</arg>
! </cmdsynopsis>
! </para>
!
! <para>
! The query <replaceable>select list</replaceable> is a comma separated list
! of <replaceable>value expressions</replaceable> that specify the derived columns
! of the query output table, which is a derived table itself it if is a subquery.
! Column names in the derived input table may be referenced in the
! derived column <replaceable>value expression</replaceable>s of the output
! table.
! </para>
!
! <para>
! In general, anywhere a table reference or value expression can appear in a
! query, a subquery or scalar subquery, respectively, may be used. A scalar
! subquery is a form of <replaceable>value expression</replaceable>.
! For example, a scalar subquery can be used in a derived column
! <replaceable>value expression</replaceable>. A scalar subquery returns
! a derived table with one column and one row (a value).
! Input table columns of an outer query that are referenceable in the
! select list of the outer query, are referenceable (in scope) in an inner
! query (subquery). A scalar query deriving a value from globally defined
! functions and variables and columns in scope from an outer query, need not
! have a FROM clause but may still use other table expression clauses.
! </para>
!
! <sect3>
! <title>FROM clause</title>
!
! <para>
! The FROM clause derives a table from one or more other tables given in a
! comma-separated table reference list. The tables listed may be table
! names or derived tables such as query names, named subqueries, joined
! tables, and complex combinations of these. The tables listed in the
! FROM clause are CROSS JOINed to produce the derived input table of a
! query which may then be subject to transformations by the WHERE,
! GROUP BY, and HAVING clauses.
! </para>
!
! <para>
! Table names are simple names of real tables. A query name is the name
! of a VIEW. A named subquery in the FROM clause is a parenthesized SELECT
! statement followed by an AS clause. Joined tables are tables joined using
! SQL JOIN syntax. If a table reference is a simple table name and it is
! the supertable in a table inheritance hierarchy, rows of the table
! include rows from all of its subtable successors unless the keyword ONLY
! precedes the table name.
! </para>
!
!
! <programlisting>
! FROM T1 AS DT1
! FROM T1 DT1
! FROM T1 DT1 (DT1C1)
! </programlisting>
!
! <para>
! Table T1 is taken as the derived input table and renamed
! as DT1. The keyword AS is optional noise when naming a table.
! Finally, T1 is renamed DT1 again and then the first column of
! DT1 is renamed as DT1C1. If DT1 has additional columns, they can be
! named by adding more comma-separated column names following DT1C1.
! Note that T1 could be any type of derived table, not just a simple
! table name.
! </para>
!
! <para>
! Subqueries, like queries in general, return a derived output table.
! Subqueries specifying a derived table must be enclosed in
! parenthesis and must be named using an AS clause.
! </para>
!
! <programlisting>
! FROM (SELECT * FROM T1) AS DT1
! FROM (SELECT * FROM T1)DT1
! FROM (SELECT * FROM T1)DT1 (DT1C1, DT1C2), T2
! </programlisting>
!
! <para>
! This example above is similar to selecting simple table names.
! The last example results in a CROSS JOIN of derived
! table DT1 with table T2. The first two columns in
! DT1 are renamed as DT1C1 and DT1C2.
! </para>
!
! <para>
! A joined table is a table derived from two or more other derived
! or real tables using JOIN syntax. INNER, OUTER, NATURAL, and CROSS
! JOIN are supported. Like a subquery, a joined table can be
! enclosed in parenthesis and given a derived table name using
! an AS clause.
! </para>
!
! <para>
! JOINs of all types can be chained together or nested where either or both of
! <replaceable class="parameter">T1</replaceable> and
! <replaceable class="parameter">T2</replaceable> may be JOINed tables.
! A Qualified JOIN may be JOINed to another table (or JOINed table)
! following its join specification, which consists of either an
! ON <replaceable>search condition</replaceable> or
! USING ( <replaceable>join column list</replaceable> ) clause.
! Parenthesis can be used around JOIN clauses to control the order
! of JOINs which are otherwise processed left to right.
! </para>
!
! <variablelist>
! <title>Join Types</title>
! <varlistentry>
! <term>CROSS JOIN</term>
! <listitem>
! <cmdsynopsis>
! <arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
! <arg choice="plain">CROSS</arg>
! <command> JOIN </command>
! <arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
! </cmdsynopsis>
!
! <para>
! A cross join takes two tables T1 and T2 having N and M rows
! respectively, and returns a joined table containing a cross
! product, NxM, of joined rows. For each row R1 of T1, each row
! R2 of T2 is joined with R1 to yield a joined table row JR
! consisting of all fields in R1 and R2. A CROSS JOIN is
! essentially an INNER JOIN ON TRUE.
! </para>
!
! <tip>
! <para>
! FROM <replaceable class="parameter">T1</replaceable>
! CROSS JOIN
! <replaceable class="parameter">T2</replaceable>,
! is equivalent to,
! FROM
! <replaceable class="parameter">T1</replaceable>,
! <replaceable class="parameter">T2</replaceable>.
! The tables listed in FROM are CROSS JOINed to form a
! derived table.
! </para>
! </tip>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term>Qualified JOINs</term>
! <listitem>
! <cmdsynopsis>
! <arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
! <group choice="opt">
! <arg choice="opt"> INNER </arg>
! <arg>
! <group choice="req">
! <arg choice="plain"> LEFT </arg>
! <arg choice="plain"> RIGHT </arg>
! <arg choice="plain"> FULL </arg>
! </group>
! <arg choice="opt"> OUTER </arg>
! </arg>
! </group>
! <command> JOIN </command>
! <arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
! <group choice="req">
! <arg> ON <replaceable>search condition</replaceable></arg>
! <arg> USING ( <replaceable>join column list</replaceable> ) </arg>
! </group>
! <arg choice="plain"> ... </arg>
! </cmdsynopsis>
!
! <para>
! Only the qualified JOIN types can use the ON or USING clause. The ON clause
! takes a <replaceable>search condition</replaceable>, which is the same
! as in a WHERE clause. The USING clause takes a comma-separated list of
! column names, which the joined tables must have in common, and joins
! the tables on the equality of those columns as a set, resulting in a
! joined table having one column for each common column listed and all
! of the other columns from both tables.
! </para>
!
! <!-- begin join semantics -->
! <variablelist>
! <varlistentry>
! <term>
! <cmdsynopsis>
! <arg> INNER </arg>
! <command> JOIN </command>
! </cmdsynopsis>
! </term>
! <listitem>
! <para>
! For each row R1 of T1, the joined table has a row for each row
! in T2 that satisfies the join specification with R1.
! </para>
! <tip>
! <para>
! The words INNER and OUTER are optional for all JOINs.
! INNER is the default. LEFT, RIGHT, and FULL are for
! OUTER JOINs only.
! </para>
! </tip>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term>
! <cmdsynopsis>
! <arg choice="plain"> LEFT </arg>
! <arg> OUTER </arg>
! <command> JOIN </command>
! </cmdsynopsis>
! </term>
! <listitem>
! <para>
! First, an INNER JOIN is performed.
! Then, where a row in T1 does not satisfy the join specification
! with any row in T2, a joined row is returned with null fields in
! columns from T2.
! </para>
! <tip>
! <para>
! The joined table unconditionally has a row for each row in T1.
! </para>
! </tip>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term>
! <cmdsynopsis>
! <arg choice="plain"> RIGHT </arg>
! <arg> OUTER </arg>
! <command> JOIN </command>
! </cmdsynopsis>
! </term>
! <listitem>
! <para>
! Rule 1: For each row R2 of T2, the joined table has a row for each
! row in T1 that satisfies the join specification with R2 (transposed
! [INNER] JOIN).
! Rule 2: Where a row in T2 does not satisfy the join specification
! with any row in T1, a joined row is returned with null fields in
! columns from T1.
! </para>
! <tip>
! <para>
! The joined table unconditionally has a row for each row in T2.
! </para>
! </tip>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term>
! <cmdsynopsis>
! <arg choice="plain"> FULL </arg>
! <arg> OUTER </arg>
! <command> JOIN </command>
! </cmdsynopsis>
! </term>
! <listitem>
! <para>
! First, a LEFT [OUTER] JOIN is performed.
! Then, Rule 2 of a RIGHT [OUTER] JOIN is performed.
! </para>
! <tip>
! <para>
! The joined table unconditionally has a row for every row of T1
! and a row for every row of T2.
! </para>
! </tip>
! </listitem>
! </varlistentry>
! </variablelist>
! <!-- end join semantics -->
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term>NATURAL JOINs</term>
! <listitem>
! <cmdsynopsis>
! <arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
! <arg choice="plain"> NATURAL </arg>
! <group choice="opt">
! <arg choice="opt"> INNER </arg>
! <arg>
! <group choice="req">
! <arg choice="plain"> LEFT </arg>
! <arg choice="plain"> RIGHT </arg>
! <arg choice="plain"> FULL </arg>
! </group>
! <arg choice="opt"> OUTER </arg>
! </arg>
! </group>
! <command> JOIN </command>
! <arg choice="req"> <replaceable class="parameter">T2</replaceable> </arg>
! </cmdsynopsis>
! <para>
! A natural join creates a joined table where every pair of matching
! column names between the two tables are merged into one column. The
! join specification is effectively a USING clause containing all the
! common column names and is otherwise like a Qualified JOIN.
! </para>
! </listitem>
! </varlistentry>
! </variablelist>
!
! <programlisting>
! FROM T1 INNER JOIN T2 USING (C)
! FROM T1 LEFT OUTER JOIN T2 USING (C)
! FROM (T1 RIGHT OUTER JOIN T2 ON (T1C1=T2C1)) AS DT1
! FROM (T1 FULL OUTER JOIN T2 USING (C)) AS DT1 (DT1C1, DT1C2)
!
! FROM T1 NATURAL INNER JOIN T2
! FROM T1 NATURAL LEFT OUTER JOIN T2
! FROM T1 NATURAL RIGHT OUTER JOIN T2
! FROM T1 NATURAL FULL OUTER JOIN T2
!
! FROM (SELECT * FROM T1)DT1 CROSS JOIN T2, T3
! FROM (SELECT * FROM T1)DT1, T2, T3
! </programlisting>
!
! <para>
! Above are some examples of joined tables and complex
! derived tables. Notice how the AS clause renames or
! names a derived table and how the optional
! comma-separated list of column names that follows
! gives names or renames the columns. The last two
! FROM clauses produce the same derived table from
! T1, T2, and T3. The AS keyword was omitted in naming
! the subquery as DT1. The keywords OUTER and INNER
! are noise that can be omitted also.
! </para>
! </sect3>
!
! <sect3>
! <title>WHERE clause</title>
!
! <para>
! The WHERE clause specifies a search condition
! that determines which rows are kept in the derived
! input table. The search condition is a boolean
! expression that may consist of simple comparison
! operators between columns or value expressions
! and various other boolean predicates, including
! BETWEEN, LIKE, EXISTS, IN and others.
! </para>
! <programlisting>
! FROM FDT WHERE
! C1 > 5
! FROM FDT WHERE
! C1 IN (1, 2, 3)
! FROM FDT WHERE
! C1 IN (SELECT C1 FROM T2)
! FROM FDT WHERE
! C1 IN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10)
!
! FROM FDT WHERE
! C1 BETWEEN
! (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10)
! AND 100
! FROM FDT WHERE
! EXISTS (SELECT C1 FROM T2 WHERE C2 > FDT.C1)
! </programlisting>
! <para>
! In the examples above, FDT is the table derived in the
! FROM clause. Rows that do not meet the search condition
! of the where clause are eliminated from FDT. Notice the use
! of scalar subqueries as value expressions (C2 assumed UNIQUE).
! Just like any other query, the subqueries can employ complex
! table expressions. Notice how FDT is referenced in the
! subqueries. Qualifying C1 as FDT.C1 is only
! necessary if C1 is the name of a column in the
! derived input table of the subquery. Qualifying the
! column name adds clarity even when it is not needed.
! The column naming scope of an outer query extends into its
! inner queries.
! </para>
!
! <para>
! After passing the WHERE filter, the derived
! input table may be subject to grouping, using
! the GROUP BY clause, and elimination of group
! rows using the HAVING clause. The HAVING clause
! can only be used after the GROUP BY clause.
! </para>
! </sect3>
!
! <sect3>
! <title>GROUP BY and HAVING clauses</title>
!
! <para>
! In standard SQL, the GROUP BY clause takes a list of
! column names, that specify a subrow, from the derived
! input table produced by the previous WHERE or FROM clause
! and partitions the table into groups with duplicate subrows
! such that within a column of the subrow, no column value is
! distinct from other column values. The resulting derived
! input table is a special type of table, called a grouped
! table, which still contains all columns but only references
! to columns of the grouped subrow, and group aggregates,
! derived from any of the columns, may appear in derived
! column value expressions in the query select list.
! When deriving an output table from a query using a grouped
! input table, each output row is derived from a corresponding
! group/partition of the grouped table. Aggregates computed
! in a derived output column are aggregates on the current
! partition/group of the grouped input table being processed.
! Only one output table row results per group/partition of
! the grouped input table.
! </para>
!
! <para>
! Postgres has extended the GROUP BY clause to allow some
! non-standard, but useful behavior. Derived output columns,
! given names using an AS clause in the query select list,
! may appear in the GROUP BY clause in combination with, or
! instead of, the input table column names. Tables may also
! be grouped by arbitrary expressions. If output table column
! names appear in the GROUP BY list, then the
! input table is augmented with additional columns of the
! output table columns listed in the GROUP BY clause. The
! value for each row in the additional columns is computed
! from the value expression that defines the output column
! in the query select list. The augmented input table is grouped
! by the column names listed in the GROUP BY clause. The
! resulting grouped augmented input table is then treated
! according standard SQL GROUP BY semantics. Only the columns of the
! unaugmented input table in the grouped subrow (if any), and group
! aggregates, derived from any of the columns of the unaugmented
! input table, may be referenced in the value expressions of the
! derived output columns of the query. Output columns derived with an
! aggregate expression cannot be named in the GROUP BY clause.
! </para>
!
! <para>
! A HAVING clause may optionally follow a GROUP BY clause.
! The HAVING clause selects or eliminates, depending on which
! perspective is taken, groups from the grouped input table
! derived from the required GROUP BY clause that precedes it.
! The search condition is the same type of expression allowed
! in a WHERE clause and may reference any of the input table
! column names in the grouped subrow, but may not reference any
! others or any named output columns. When the search condition
! results in TRUE the group is retained, otherwise the group is
! eliminated and will not be used to derive an output table row.
! </para>
!
! </sect3>
!
! <sect3>
! <title>ORDER BY and LIMIT clauses</title>
!
! <para>
! ORDER BY and LIMIT clauses are not clauses of a table expression.
! They are optional clauses that may follow a query expression and
! are discussed here because they are commonly used with the clauses above.
! </para>
!
! <para>
! The ORDER BY clause conforms to the SQL92 standard but is extended
! in Postgres. Postgres allows ORDER BY to reference both output
! table columns, as named in the select list using the AS clause, and
! input table columns, as given by the table derived in the FROM clause and
! other previous clauses. Postgres also extends ORDER BY to allow ordering by
! arbitrary expressions. If used in a query with a GROUP BY clause, the ORDER BY
! clause can only reference output table column names and grouped input table
! columns. ORDER BY takes a comma-separated list of columns and performs a
! cascaded ordering of the table by the columns listed, in the order listed.
! The keyword DESC or ASC may follow any column name or expression in the list
! to specify descending or ascending ordering, respectively. Ascending order
! is the default. Note that in SQL99, ORDER BY is only allowed in
! cursor declarations, while in SQL92, it is allowed as part of a "direct
! select statement" that returns multiple rows.
! </para>
!
! <para>
! LIMIT is not a standard SQL clause. LIMIT is a Postgres extension
! that limits the number of rows that will be returned from a query.
! The rows returned by a query using the LIMIT clause are random if not
! following an ORDER BY clause. A LIMIT clause may optionally be
! followed by an OFFSET clause which specifies a number of rows to be
! skipped in the output table before returning the number of rows
! specified in the LIMIT clause.
! </para>
! </sect3>
</sect2>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: reaster@comptechnews.com, peter_e@gmx.net, pgman@candle.pha.pa.us, pgsql-patches@postgresql.org
Subject: Re: docs: syntax.sgml patch
In-Reply-To: <0101182038260L.02219@comptechnews>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox