From: Robert B. Easter <reaster@comptechnews.com>
To: pgsql-patches@postgresql.org
Subject: docs: syntax.sgml patch
Date: Sat, 13 Jan 2001 23:52:37 -0500
Message-ID: <0101132352370A.15894@comptechnews> (raw)
I've made an attempt to better describe the FROM clause and table expressions
in general for the SQL Syntax: Expressions page in the User's Guide.
My patch replaces the "FROM clause" section that was in there with a section
called "Table Expressions". It includes a description of the FROM clause and
also the WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT clauses. The JOIN
syntax is covered. Subqueries are also explained some.
There could be some mistakes or statements that imply a limitation that is
not true (maybe). I encourage people to review it and correct any mistakes
or clean up the language etc.
If it's not right for being applied, let me know. I'm hoping this material
will fit in and be developed further with more examples later.
--
-------- 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 (text/english, 22.6K) download
*** syntax.sgml.orig Sat Jan 13 23:18:57 2001
--- syntax.sgml.modi.2 Sat Jan 13 23:41:19 2001
***************
*** 697,702 ****
--- 697,703 ----
<member>parameter</member>
<member>functional expression</member>
<member>aggregate expression</member>
+ <member>table expression</member>
</simplelist>
</para>
***************
*** 838,898 ****
</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>
<sect2 id="sql-precedence">
--- 839,1357 ----
</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
! 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 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 part of an SQL table
! expression. They, like a table expression, appear as part
! of a query expression.
! </para>
!
! <para>
! In standard SQL, the ORDER BY clause is defined only in cursor
! declarations to sort the output rows accessed with fetch and
! may only reference output table columns by name or number.
! Postgres extends ORDER BY and allows its use in all queries and
! may reference both output table columns and input table columns.
! Postgres also extends ORDER BY to order 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
! columns.
! </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>
<sect2 id="sql-precedence">
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, pgsql-patches@postgresql.org
Subject: Re: docs: syntax.sgml patch
In-Reply-To: <0101132352370A.15894@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