pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feeddocs: syntax.sgml patch
13+ messages / 4 participants
[nested] [flat]
* docs: syntax.sgml patch
@ 2001-01-14 04:52 Robert B. Easter <reaster@comptechnews.com>
0 siblings, 2 replies; 13+ messages in thread
From: Robert B. Easter @ 2001-01-14 04:52 UTC (permalink / raw)
To: pgsql-patches@postgresql.org
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">
* Re: docs: syntax.sgml patch
@ 2001-01-14 20:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Robert B. Easter <reaster@comptechnews.com>
1 sibling, 1 reply; 13+ messages in thread
From: Tom Lane @ 2001-01-14 20:45 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: pgsql-patches@postgresql.org
"Robert B. Easter" <reaster@comptechnews.com> writes:
> 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.
A good start. Here are some suggestions for improving it.
1. I think this material should be moved out of the "Expressions"
section and made into its own level-1 section. In the first place,
it's now larger than the rest of the section put together, and in the
second place table expressions are not the same kind of expression that
the rest of the section is talking about. (Perhaps the "Expressions"
section should be retitled "Arithmetic Expressions" or some such.)
The mention of scalar subqueries does belong under arithmetic
expressions, but ISTM the rest of this doesn't.
Another possibility is to make a new level-1 section that talks about
the syntax of the SELECT statement in general, with subsections along
the lines of
Target list
[take this part out of Expressions]
Source table (FROM/WHERE/GROUP BY/HAVING)
[most of what you wrote]
Display constraints (ORDER BY/LIMIT)
[last part of what you wrote]
UNION/INTERSECT/EXCEPT
[to be written]
2. I don't much like the way you use the term "input table"; that seems
to me to connote a real table that is being used as one of the initial
inputs to a SELECT. Perhaps use something like "final derived table" to
refer to the output of the table expression, with "output table" being
the result of the SELECT itself.
3. The references to scope seem to just be confusing here. I'd leave
them out of this discussion and plan to develop a separate section
discussing name scope and outer references.
4. Your practice of showing several examples in a group and then
explaining them seems confusing, because it's not always clear which
example any particular sentence is referring to. I'd say keep to a
strict one-example, one-commentary alternation.
5. In some places you have {curly braces} around syntactic variables.
Don't do that, use the appropriate markup.
6. Both SQL92 and Postgres allow a HAVING clause without GROUP BY.
It's kinda pointless, perhaps, but it's allowed.
7. It's not correct to say that standard SQL only allows ORDER BY in
cursors; there's also
<direct select statement: multiple rows> ::=
<query expression> [ <order by clause> ]
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: docs: syntax.sgml patch
@ 2001-01-15 00:13 Robert B. Easter <reaster@comptechnews.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Robert B. Easter @ 2001-01-15 00:13 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-patches@postgresql.org
On Sunday 14 January 2001 15:45, Tom Lane wrote:
> "Robert B. Easter" <reaster@comptechnews.com> writes:
> > 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.
>
>
> 1. I think this material should be moved out of the "Expressions"
> section and made into its own level-1 section. In the first place,
> it's now larger than the rest of the section put together, and in the
> second place table expressions are not the same kind of expression that
> the rest of the section is talking about. (Perhaps the "Expressions"
> section should be retitled "Arithmetic Expressions" or some such.)
> The mention of scalar subqueries does belong under arithmetic
> expressions, but ISTM the rest of this doesn't.
Thanks for feedback!
I see what you are saying and I expected my stuff to not fit so well. The
FROM list section, which I replaced, doesn't really belong there either. The
whole outline of the SQL Syntax chapter could maybe use a redesign with a
structure that follows the breakdown of SQL as expressed in the SQL standards
documents and then Postgres extensions/compatibility chapters or material
mixed in.
The basic outline of the standard I read (SQL1999) is like:
1. Scope
2. Normative References
3. Definitions, notations, conventions.
4. Concepts
5. Lexical Elements
6. Scalar Expressions
Value Expression
Scalar Subquery
Case Expression
Element Reference
7. Query Expressions
Table Expression
FROM clause
WHERE clause
etc ...
8. Predicates
LIKE
BETWEEN
EXISTS
9. Data Assignment Rules and Routine Determination
10. Additional Common Elements
11. Schema Definition and Manipulation
12. Access Control
13. SQL-client modules
14. Data Manipulation
15. Control Statements
16. Transaction Management
17. Connection Management
18. Session Management
19. Diagnostics Management
20. Information Schema
21. Definition Schema
22. Status Codes
Anyhow, this outline could be a basis from which to build an "SQL Guide" or
something. Maybe calling it SQL Syntax is not the best. More than just pure
syntax is laid out. The semantics is explained too. The standard document
has a good structure but is very formal. Taking this structure and writing
something that is normal, readable english with real examples from PostgreSQL
would be something good but is a large task - basically, derive a document
based on the standard doc (no verbatim plagiarism though). Maybe not all of
the chapters above make sense for PostgreSQL now if it's not all implemented,
but that outline could be reserved, in a way, for future development.
The current "Expressions" section, as it is, might better be called "Scalar
Expressions" and then move the "Table Expressions" that I wrote under "Query
Expressions." etc ...
The reference section, from the man-pages etc, would still be good as like
quick reference cards.
Comments, anyone?
>
> Another possibility is to make a new level-1 section that talks about
> the syntax of the SELECT statement in general, with subsections along
> the lines of
> Target list
> [take this part out of Expressions]
> Source table (FROM/WHERE/GROUP BY/HAVING)
> [most of what you wrote]
> Display constraints (ORDER BY/LIMIT)
> [last part of what you wrote]
> UNION/INTERSECT/EXCEPT
> [to be written]
>
> 2. I don't much like the way you use the term "input table"; that seems
> to me to connote a real table that is being used as one of the initial
> inputs to a SELECT. Perhaps use something like "final derived table" to
> refer to the output of the table expression, with "output table" being
> the result of the SELECT itself.
I agree. I want to use standard terminology and take the time to introduce
and define terms clearly before using them. I was afraid to use "derived
table" all over the place because I thought it was too vague sounding. The
result of the whole query is also a derived table so things are confusing
maybe. Also, I saw the "input table" term used in some other PostgreSQL
docs, so I thought it might be consistent. It can be worked on more and the
terms improved.
>
> 3. The references to scope seem to just be confusing here. I'd leave
> them out of this discussion and plan to develop a separate section
> discussing name scope and outer references.
Ok. I'm not sure where that fits right now either.
>
> 4. Your practice of showing several examples in a group and then
> explaining them seems confusing, because it's not always clear which
> example any particular sentence is referring to. I'd say keep to a
> strict one-example, one-commentary alternation.
>
I know, its confusing. :) Examples need work.
> 5. In some places you have {curly braces} around syntactic variables.
> Don't do that, use the appropriate markup.
It would be more like:
SELECT <select list> <table expression>
?? DocBook puts those curly braces on automatically when I use choice="req"
in a <arg> tag. Guess I can't use those DocBook tags all the time unless it
can do < and > with some other tag or option.
>
> 6. Both SQL92 and Postgres allow a HAVING clause without GROUP BY.
> It's kinda pointless, perhaps, but it's allowed.
>
You're right, I've done it and it works like a WHERE clause. In SQL99, the
default in a HAVING clause, if group by is not specified, is GROUP BY (). A
GROUP BY () is called a <grand total>. I'll admit, the reading of the <group
by clause> is one of the more confusing ones. Whatever GROUP BY () is
supposed to do, PostgreSQL won't take it.
> 7. It's not correct to say that standard SQL only allows ORDER BY in
> cursors; there's also
>
> <direct select statement: multiple rows> ::=
> <query expression> [ <order by clause> ]
>
> regards, tom lane
Again, I'm reading SQL1999 so things are different. I managed to see a copy
of SQL92 and found what you quote. However, direct select statement is not
present in the SQL1999 file I've been reading. In the file I read, <order by
clause> is only used in a <declare cursor> definition. I guess they intend
that if you really want ordered results, you have to create a cursor for it
now. But it doesn't matter too much since PostgreSQL is working on SQL92
compatibility, right.
The documentation seems to need a lot of work. I'm not sure how radically it
can be changed since I don't want to disturb others work any. I've just been
trying to squeeze in my stuff where I can fit it! :)
--
-------- 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/ ------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PATCHES] docs: syntax.sgml patch
@ 2001-01-15 21:03 Peter Eisentraut <peter_e@gmx.net>
parent: Robert B. Easter <reaster@comptechnews.com>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Eisentraut @ 2001-01-15 21:03 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
[ redirected to -docs ]
Robert B. Easter writes:
> I see what you are saying and I expected my stuff to not fit so well. The
> >FROM list section, which I replaced, doesn't really belong there either. The
> whole outline of the SQL Syntax chapter could maybe use a redesign with a
> structure that follows the breakdown of SQL as expressed in the SQL standards
> documents and then Postgres extensions/compatibility chapters or material
> mixed in.
Not a bad idea. I think we already have it similar to that.
> The basic outline of the standard I read (SQL1999) is like:
>
> 1. Scope
> 2. Normative References
> 3. Definitions, notations, conventions.
Skip that.
> 4. Concepts
Maybe tutorial?
> 5. Lexical Elements
We have that.
> 6. Scalar Expressions
> Value Expression
> Scalar Subquery
> Case Expression
> Element Reference
We have some of that. You're writing the rest. (?)
> 7. Query Expressions
> Table Expression
> FROM clause
> WHERE clause
> etc ...
You're writing that.
> 8. Predicates
> LIKE
> BETWEEN
> EXISTS
This should be in the functions/operators chapter.
> 9. Data Assignment Rules and Routine Determination
This is sort of the function/operator resolution and type casting rules.
These are documented somewhere.
> 10. Additional Common Elements
> 11. Schema Definition and Manipulation
> 12. Access Control
> 13. SQL-client modules
> 14. Data Manipulation
> 15. Control Statements
These all have varyingly little documentation between the tutorial,
reference manual, smoke and mirrors.
> 16. Transaction Management
Documented
> 17. Connection Management
> 18. Session Management
> 19. Diagnostics Management
> 20. Information Schema
> 21. Definition Schema
> 22. Status Codes
We don't have these implemented.
> Anyhow, this outline could be a basis from which to build an "SQL Guide" or
> something.
The User's Guide is effectively the SQL Guide, it just isn't called that.
In the future we might want to ponder renaming it.
> Maybe calling it SQL Syntax is not the best. More than just pure
> syntax is laid out. The semantics is explained too. The standard document
> has a good structure but is very formal. Taking this structure and writing
> something that is normal, readable english with real examples from PostgreSQL
> would be something good but is a large task - basically, derive a document
> based on the standard doc (no verbatim plagiarism though).
I think you have the right idea. The schedule is probably too short to
accomplish all of this for 7.1, though.
> > 5. In some places you have {curly braces} around syntactic variables.
> > Don't do that, use the appropriate markup.
>
> It would be more like:
>
> SELECT <select list> <table expression>
>
> ?? DocBook puts those curly braces on automatically when I use choice="req"
> in a <arg> tag. Guess I can't use those DocBook tags all the time unless it
> can do < and > with some other tag or option.
You can use choice="plain". I don't feel partial either way but I wonder
how it looks in print.
> The documentation seems to need a lot of work. I'm not sure how radically it
> can be changed since I don't want to disturb others work any. I've just been
> trying to squeeze in my stuff where I can fit it! :)
I don't think you will disturb too many people...
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [PATCHES] docs: syntax.sgml patch
@ 2001-01-17 20:02 Robert B. Easter <reaster@comptechnews.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 0 replies; 13+ messages in thread
From: Robert B. Easter @ 2001-01-17 20:02 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-docs
We seem to be in agreement about the SQL documentation. That's cool. :)
I'd really like to see the "SQL Guide" when we can do it. As for the User's
Guide, you say it might be nice to rename it SQL Guide. That could be done
or have both. Maybe having both an SQL guide and a User's guide is best.
The SQL Guide would document all of the SQL language in PostgreSQL based on
the outline of the SQL standards docs and using as many standard terms as
possible without it being too cryptic/formal for normal readers.
The User's guide could focus on non-SQL things that users want to know, like
about psql, pgaccess, and other user apps, explain the basic architecture and
files involved in the system, and commands that are PostgreSQL-specific but
not SQL and not DBA-level commands.
The Admin guide covers those commands and programs (initdb/postmaster) that
affect the whole database management system, its tuning, backup and other
things. All the other guides could reference the SQL Guide when needing to
explain the full details of an sql command.
Again, everything is already similar to (or exactly) what I'm saying here! :)
Well, if you think the creation of the SQL Guide is really good, we can work
on the outline of this guide. This would be a toplevel guide like User's,
Admin, Programmer etc. We can look through the outline of the standards docs
more and have a good plan. Once we have a good outline, then its easy for
anyone to write sections and chapters to fill it in with material. I really
think we want to establish the outline before everyone jumps in and it
becomes a disorganized patchwork of documentation.
Let me know what you think. When I have the time, I will look at both the
basic outline in SQL92 Foundation and SQL99 Foundation. PostgreSQL wants
SQL92 compatibility, so we have to see how they differ and what chapters and
sections make sense in a PostgreSQL SQL Guide. At the same time, PostgreSQL
should be looking at SQL99 too for the future, at least for implementing some
intesting new features. I'm thinking to create detailed outlines from both
SQL92 and SQL99, then compare them and compare them both with PostreSQL.
Then, take from both outlines what is right for PostgreSQL and make that the
basic outline of the SQL Guide. Finally, after the outline as been poked
around by enough people on this list, post the skeleton (outline) of the SQL
Guide somewhere (in cvs maybe), and then anyone can write the sections and
chapters. Hopefully the chapters and sections in the outline will be good
enough and people won't try to write different chapter or section names. We
want to keep it so that every chapter and section has a clear mapping to the
offical SQL Foundation docs. The SQL Guide can make footnotes referring the
reader to see the corresponding SQL Foundation section for detailed
information. The SQL Guide would be like a novice to intermediate-level
guide to SQL while the SQL Foundation doc is like the advanced version.
I think it would be a good docs project and a nice opportunity to get into
those specs in detail and learn a lot. By making a new SQL Guide, I won't
have to worry about upsetting the existing docs too. :)
On Monday 15 January 2001 16:03, Peter Eisentraut wrote:
> [ redirected to -docs ]
>
> Robert B. Easter writes:
> > I see what you are saying and I expected my stuff to not fit so well.
> > The
> >
> > >FROM list section, which I replaced, doesn't really belong there either.
> > > The
> >
> > whole outline of the SQL Syntax chapter could maybe use a redesign with a
> > structure that follows the breakdown of SQL as expressed in the SQL
> > standards documents and then Postgres extensions/compatibility chapters
> > or material mixed in.
>
> Not a bad idea. I think we already have it similar to that.
>
> > The basic outline of the standard I read (SQL1999) is like:
> >
> > 1. Scope
> > 2. Normative References
> > 3. Definitions, notations, conventions.
>
> Skip that.
>
> > 4. Concepts
>
> Maybe tutorial?
>
> > 5. Lexical Elements
>
> We have that.
>
> > 6. Scalar Expressions
> > Value Expression
> > Scalar Subquery
> > Case Expression
> > Element Reference
>
> We have some of that. You're writing the rest. (?)
>
> > 7. Query Expressions
> > Table Expression
> > FROM clause
> > WHERE clause
> > etc ...
>
> You're writing that.
>
> > 8. Predicates
> > LIKE
> > BETWEEN
> > EXISTS
>
> This should be in the functions/operators chapter.
>
> > 9. Data Assignment Rules and Routine Determination
>
> This is sort of the function/operator resolution and type casting rules.
> These are documented somewhere.
>
> > 10. Additional Common Elements
> > 11. Schema Definition and Manipulation
> > 12. Access Control
> > 13. SQL-client modules
> > 14. Data Manipulation
> > 15. Control Statements
>
> These all have varyingly little documentation between the tutorial,
> reference manual, smoke and mirrors.
>
> > 16. Transaction Management
>
> Documented
>
> > 17. Connection Management
> > 18. Session Management
> > 19. Diagnostics Management
> > 20. Information Schema
> > 21. Definition Schema
> > 22. Status Codes
>
> We don't have these implemented.
>
> > Anyhow, this outline could be a basis from which to build an "SQL Guide"
> > or something.
>
> The User's Guide is effectively the SQL Guide, it just isn't called that.
> In the future we might want to ponder renaming it.
>
> > Maybe calling it SQL Syntax is not the best. More than just pure
> > syntax is laid out. The semantics is explained too. The standard
> > document has a good structure but is very formal. Taking this structure
> > and writing something that is normal, readable english with real examples
> > from PostgreSQL would be something good but is a large task - basically,
> > derive a document based on the standard doc (no verbatim plagiarism
> > though).
>
> I think you have the right idea. The schedule is probably too short to
> accomplish all of this for 7.1, though.
>
> > > 5. In some places you have {curly braces} around syntactic variables.
> > > Don't do that, use the appropriate markup.
> >
> > It would be more like:
> >
> > SELECT <select list> <table expression>
> >
> > ?? DocBook puts those curly braces on automatically when I use
> > choice="req" in a <arg> tag. Guess I can't use those DocBook tags all
> > the time unless it can do < and > with some other tag or option.
>
> You can use choice="plain". I don't feel partial either way but I wonder
> how it looks in print.
>
> > The documentation seems to need a lot of work. I'm not sure how
> > radically it can be changed since I don't want to disturb others work
> > any. I've just been trying to squeeze in my stuff where I can fit it! :)
>
> I don't think you will disturb too many people...
--
-------- 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/ ------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: docs: syntax.sgml patch
@ 2001-01-17 22:15 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Robert B. Easter <reaster@comptechnews.com>
1 sibling, 1 reply; 13+ messages in thread
From: Bruce Momjian @ 2001-01-17 22:15 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: pgsql-patches@postgresql.org
I know this SQL Guide has been discussed, but what should be done with
this patch?
[ Charset ISO-8859-1 unsupported, converting... ]
> 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/ ------------
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: docs: syntax.sgml patch
@ 2001-01-18 23:48 Peter Eisentraut <peter_e@gmx.net>
parent: Bruce Momjian <pgman@candle.pha.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Peter Eisentraut @ 2001-01-18 23:48 UTC (permalink / raw)
To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Robert B. Easter <reaster@comptechnews.com>; pgsql-patches@postgresql.org
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.
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: docs: syntax.sgml patch
@ 2001-01-19 01:38 Robert B. Easter <reaster@comptechnews.com>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 1 reply; 13+ messages in thread
From: Robert B. Easter @ 2001-01-19 01:38 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: pgsql-patches@postgresql.org
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>
* Re: docs: syntax.sgml patch
@ 2001-01-21 22:14 Peter Eisentraut <peter_e@gmx.net>
parent: Robert B. Easter <reaster@comptechnews.com>
0 siblings, 3 replies; 13+ messages in thread
From: Peter Eisentraut @ 2001-01-21 22:14 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
I've checked this in, or at least I used it as the starting point for what
I did. The GROUP BY and ORDER BY explanations seemed really complicated,
so I disabled them for now. They need to be made simpler, and an example
would be nice. Essentially, this section is now the description of the
SELECT command; maybe it should be named like that, too.
Robert B. Easter writes:
> 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.
>
>
--
Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: docs: syntax.sgml patch
@ 2001-01-22 02:00 Robert B. Easter <reaster@comptechnews.com>
parent: Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 13+ messages in thread
From: Robert B. Easter @ 2001-01-22 02:00 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
Thanks for getting some of the table expressions into the docs. I think it
will be helpful to people. I'll see what I can do with the GROUP BY and
HAVING clauses, unless you beat me to it! Thanks again.
On Sunday 21 January 2001 17:14, Peter Eisentraut wrote:
> I've checked this in, or at least I used it as the starting point for what
> I did. The GROUP BY and ORDER BY explanations seemed really complicated,
> so I disabled them for now. They need to be made simpler, and an example
> would be nice. Essentially, this section is now the description of the
> SELECT command; maybe it should be named like that, too.
>
> Robert B. Easter writes:
> > 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/ ------------
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: docs: syntax.sgml patch
@ 2001-01-22 06:10 Robert B. Easter <reaster@comptechnews.com>
parent: Peter Eisentraut <peter_e@gmx.net>
2 siblings, 0 replies; 13+ messages in thread
From: Robert B. Easter @ 2001-01-22 06:10 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
I saw the commented sections about GROUP BY etc. I've taken another try at
GROUP BY in the patch (attached). I moved the commenting to cover ORDER BY
and LIMIT now.
On Sunday 21 January 2001 17:14, Peter Eisentraut wrote:
> I've checked this in, or at least I used it as the starting point for what
> I did. The GROUP BY and ORDER BY explanations seemed really complicated,
> so I disabled them for now. They need to be made simpler, and an example
> would be nice. Essentially, this section is now the description of the
> SELECT command; maybe it should be named like that, too.
>
> Robert B. Easter writes:
> > 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.4 (text/english, 7.4K) download
*** syntax.sgml Mon Jan 22 01:04:00 2001
--- syntax.sgml.modi.4 Mon Jan 22 01:03:34 2001
***************
*** 1467,1475 ****
</para>
</sect2>
- <!-- This is confusing as heck. Make it simpler. -->
-
- <![IGNORE[
<sect2>
<title>GROUP BY and HAVING clauses</title>
--- 1467,1472 ----
***************
*** 1482,1540 ****
clause.)
</para>
<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 table derived in the
! 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.
</para>
</sect2>
<sect2>
<title>ORDER BY and LIMIT clauses</title>
--- 1479,1590 ----
clause.)
</para>
+ <synopsis>
+ GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>...
+ </synopsis>
+
+ <para>
+ The GROUP BY clause is used to group together rows in a table
+ that share the same values in all the columns listed. The order
+ in which the columns are listed doesn't matter as it does in
+ an ORDER BY clause. The purpose is to reduce each group of rows
+ sharing common values into one group row that is representative
+ of all rows in the group. This is done to eliminate redundancy
+ in the output and/or obtain aggregates that apply to these groups.
+ </para>
+
+ <para>
+ Once a table
+ is grouped, columns that are not included in
+ the grouping can't be referenced, except in aggregate, since a specific value
+ in those columns is ambiguous - which row in the group should it come from?
+ The grouped-by columns can be referenced in select list column expressions
+ since they have a known constant-value
+ per group. Aggregate functions on the ungrouped columns
+ provide aggregates that span the rows of a group, not
+ of the whole table. For instance, a sum(sales) on a grouped table
+ by product code gives the total sales for each product, not the
+ total sales on all products. The aggregates of the ungrouped
+ columns are representative of the group, whereas their individual
+ values may not be.
+ </para>
+
+ <para>
+ In strict SQL, GROUP BY can only group by columns of the source
+ table but Postgres extends this to also allow GROUP BY to group
+ by target columns in the query select list. Grouping by
+ value expressions instead of simple column names is also allowed.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+ <para>
+ <programlisting>
+ SELECT pid, p.name, (sum(s.units) * p.price) AS sales
+ FROM products p LEFT JOIN sales s USING ( pid )
+ GROUP BY pid, p.name, p.price;
+ </programlisting>
+ </para>
+
+ <para>
+ In this example, the columns pid, p.name, and p.price
+ must be in the GROUP BY clause since they are
+ referenced in the query select list. The column
+ s.units doesn't have to be in the GROUP BY list since
+ it is only used in an aggregate, which represents
+ the group of sales of a product. For each
+ product, a summary row is returned about all sales
+ of the product.
+ </para>
+
+ <para>
+ <synopsis>
+ HAVING <replaceable>search_condition</replaceable>
+ </synopsis>
+ </para>
+
<para>
! If a table has been grouped using a GROUP BY clause, but then
! only certain groups are of interest, then the HAVING clause
! can be used, much like a WHERE clause, to select out the
! groups of interest from a grouped table. For some queries,
! Postgres allows a HAVING clause to be used without a GROUP BY
! and then it acts just like another WHERE clause, but the point
! in using HAVING that way is not clear. Since HAVING operates
! on groups, only grouped columns can be listed in the HAVING
! clause. If selection based on some ungrouped column is
! desired, it should be expressed in the WHERE clause.
</para>
<para>
! Example:
</para>
<para>
! <programlisting>
! SELECT pid AS "Products",
! p.name AS "Over 5000",
! (sum(s.units) * (p.price - p.cost))
! AS "Past Month Profit"
! FROM products p LEFT JOIN sales s USING ( pid )
! WHERE p.date > CURRENT_DATE - INTERVAL '4 week'
! GROUP BY pid, p.name, p.price
! HAVING p.price > 5000;
! </programlisting>
! </para>
!
! <para>
! In the example above, the WHERE clause is selecting rows by a
! column that is not grouped, while the HAVING clause
! is selecting groups with a price greater than 5000.
</para>
</sect2>
+
+ <!-- This is confusing as heck. Make it simpler. -->
+
+ <![IGNORE[
<sect2>
<title>ORDER BY and LIMIT clauses</title>
* Re: docs: syntax.sgml patch
@ 2001-01-22 06:26 Robert B. Easter <reaster@comptechnews.com>
parent: Peter Eisentraut <peter_e@gmx.net>
2 siblings, 1 reply; 13+ messages in thread
From: Robert B. Easter @ 2001-01-22 06:26 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: Bruce Momjian <pgman@candle.pha.pa.us>; pgsql-patches@postgresql.org
Woops. syntax.sgml.patch.5 is attached. Don't use syntax.sgml.patch.4, it
has a mistake in it. Sorry. (Hopefully no other errors!)
On Sunday 21 January 2001 17:14, Peter Eisentraut wrote:
> I've checked this in, or at least I used it as the starting point for what
> I did. The GROUP BY and ORDER BY explanations seemed really complicated,
> so I disabled them for now. They need to be made simpler, and an example
> would be nice. Essentially, this section is now the description of the
> SELECT command; maybe it should be named like that, too.
>
> Robert B. Easter writes:
> > 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.5 (text/english, 7.4K) download
*** syntax.sgml.orig Sun Jan 21 22:17:17 2001
--- syntax.sgml.modi.5 Mon Jan 22 01:24:10 2001
***************
*** 1467,1475 ****
</para>
</sect2>
- <!-- This is confusing as heck. Make it simpler. -->
-
- <![IGNORE[
<sect2>
<title>GROUP BY and HAVING clauses</title>
--- 1467,1472 ----
***************
*** 1482,1540 ****
clause.)
</para>
<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 table derived in the
! 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.
</para>
</sect2>
<sect2>
<title>ORDER BY and LIMIT clauses</title>
--- 1479,1590 ----
clause.)
</para>
+ <synopsis>
+ GROUP BY <replaceable>grouping_column_reference</replaceable> <optional>, <replaceable>grouping_column_reference</replaceable></optional>...
+ </synopsis>
+
+ <para>
+ The GROUP BY clause is used to group together rows in a table
+ that share the same values in all the columns listed. The order
+ in which the columns are listed doesn't matter as it does in
+ an ORDER BY clause. The purpose is to reduce each group of rows
+ sharing common values into one group row that is representative
+ of all rows in the group. This is done to eliminate redundancy
+ in the output and/or obtain aggregates that apply to these groups.
+ </para>
+
+ <para>
+ Once a table
+ is grouped, columns that are not included in
+ the grouping can't be referenced, except in aggregate, since a specific value
+ in those columns is ambiguous - which row in the group should it come from?
+ The grouped-by columns can be referenced in select list column expressions
+ since they have a known constant-value
+ per group. Aggregate functions on the ungrouped columns
+ provide aggregates that span the rows of a group, not
+ of the whole table. For instance, a sum(sales) on a grouped table
+ by product code gives the total sales for each product, not the
+ total sales on all products. The aggregates of the ungrouped
+ columns are representative of the group, whereas their individual
+ values may not be.
+ </para>
+
+ <para>
+ In strict SQL, GROUP BY can only group by columns of the source
+ table but Postgres extends this to also allow GROUP BY to group
+ by target columns in the query select list. Grouping by
+ value expressions instead of simple column names is also allowed.
+ </para>
+
+ <para>
+ Example:
+ </para>
+
+ <para>
+ <programlisting>
+ SELECT pid, p.name, (sum(s.units) * p.price) AS sales
+ FROM products p LEFT JOIN sales s USING ( pid )
+ GROUP BY pid, p.name, p.price;
+ </programlisting>
+ </para>
+
+ <para>
+ In this example, the columns pid, p.name, and p.price
+ must be in the GROUP BY clause since they are
+ referenced in the query select list. The column
+ s.units doesn't have to be in the GROUP BY list since
+ it is only used in an aggregate, which represents
+ the group of sales of a product. For each
+ product, a summary row is returned about all sales
+ of the product.
+ </para>
+
+ <para>
+ <synopsis>
+ HAVING <replaceable>search_condition</replaceable>
+ </synopsis>
+ </para>
+
<para>
! If a table has been grouped using a GROUP BY clause, but then
! only certain groups are of interest, then the HAVING clause
! can be used, much like a WHERE clause, to select out the
! groups of interest from a grouped table. For some queries,
! Postgres allows a HAVING clause to be used without a GROUP BY
! and then it acts just like another WHERE clause, but the point
! in using HAVING that way is not clear. Since HAVING operates
! on groups, only grouped columns can be listed in the HAVING
! clause. If selection based on some ungrouped column is
! desired, it should be expressed in the WHERE clause.
</para>
<para>
! Example:
</para>
<para>
! <programlisting>
! SELECT pid AS "Products",
! p.name AS "Over 5000",
! (sum(s.units) * (p.price - p.cost))
! AS "Past Month Profit"
! FROM products p LEFT JOIN sales s USING ( pid )
! WHERE p.date > CURRENT_DATE - INTERVAL '4 week'
! GROUP BY pid, p.name, p.price, p.cost
! HAVING p.price > 5000;
! </programlisting>
! </para>
!
! <para>
! In the example above, the WHERE clause is selecting rows by a
! column that is not grouped, while the HAVING clause
! is selecting groups with a price greater than 5000.
</para>
</sect2>
+
+ <!-- This is confusing as heck. Make it simpler. -->
+
+ <![IGNORE[
<sect2>
<title>ORDER BY and LIMIT clauses</title>
* Re: docs: syntax.sgml patch
@ 2001-01-24 03:29 Bruce Momjian <pgman@candle.pha.pa.us>
parent: Robert B. Easter <reaster@comptechnews.com>
0 siblings, 0 replies; 13+ messages in thread
From: Bruce Momjian @ 2001-01-24 03:29 UTC (permalink / raw)
To: Robert B. Easter <reaster@comptechnews.com>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-patches@postgresql.org
Robert, this in all in CVS, right?
> Woops. syntax.sgml.patch.5 is attached. Don't use syntax.sgml.patch.4, it
> has a mistake in it. Sorry. (Hopefully no other errors!)
>
>
>
> On Sunday 21 January 2001 17:14, Peter Eisentraut wrote:
> > I've checked this in, or at least I used it as the starting point for what
> > I did. The GROUP BY and ORDER BY explanations seemed really complicated,
> > so I disabled them for now. They need to be made simpler, and an example
> > would be nice. Essentially, this section is now the description of the
> > SELECT command; maybe it should be named like that, too.
> >
> > Robert B. Easter writes:
> > > 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/ ------------
[ Attachment, skipping... ]
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2001-01-24 03:29 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-01-14 04:52 docs: syntax.sgml patch Robert B. Easter <reaster@comptechnews.com>
2001-01-14 20:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-01-15 00:13 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-15 21:03 ` Peter Eisentraut <peter_e@gmx.net>
2001-01-17 20:02 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-17 22:15 ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-01-18 23:48 ` Peter Eisentraut <peter_e@gmx.net>
2001-01-19 01:38 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-21 22:14 ` Peter Eisentraut <peter_e@gmx.net>
2001-01-22 02:00 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-22 06:10 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-22 06:26 ` Robert B. Easter <reaster@comptechnews.com>
2001-01-24 03:29 ` Bruce Momjian <pgman@candle.pha.pa.us>
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