From List

*** 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 ---- parameter functional expression aggregate expression + table expression *************** *** 838,898 **** - - From List - - - The from list - is a comma-separated list of from-expressions. - The simplest possibility for a from-expression is: - - - table_reference [ [ AS ] alias ] - ! where table_reference is of the form ! ! ! [ ONLY ] table_name [ * ] ! ! ! 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 alias 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 ONLY is specified then ! child tables are not included. A trailing asterisk * ! can be written to specifically indicate that child tables are included ! (ONLY and * are mutually ! exclusive). ! ! ! A from-expression can also be a sub-query: ! ! ( select-statement ) [ AS ] alias ! ! 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 alias. ! ! ! Finally, a from-expression can be built up from simpler from-expressions ! using JOIN clauses: - - from_expression [ NATURAL ] join_type from_expression - [ ON join_condition | USING ( join_column_list ) ] - - This syntax allows specification of outer joins. - For details see the reference page for SELECT. - - --- 839,1357 ---- ! ! Table Expressions ! ! A table expression specifies a table ! or grouped table. ! ! ! ! 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. ! ! ! ! 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. ! ! ! ! ! SELECT ! select list ! table expression ! ! ! ! ! The query select list is a comma separated list ! of value expressions 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 value expressions of the output ! table. ! ! ! ! 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 value expression. ! For example, a scalar subquery can be used in a derived column ! value expression. 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. ! ! ! ! FROM clause ! ! ! 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. ! ! ! ! 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. ! ! ! ! ! FROM T1 AS DT1 ! FROM T1 DT1 ! FROM T1 DT1 (DT1C1) ! ! ! ! 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. ! ! ! ! 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. ! ! ! ! FROM (SELECT * FROM T1) AS DT1 ! FROM (SELECT * FROM T1)DT1 ! FROM (SELECT * FROM T1)DT1 (DT1C1, DT1C2), T2 ! ! ! ! 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. ! ! ! ! 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. ! ! ! ! JOINs of all types can be chained together or nested where either or both of ! T1 and ! T2 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 search condition or ! USING ( join column list ) clause. ! Parenthesis can be used around JOIN clauses to control the order ! of JOINs which are otherwise processed left to right. ! ! ! ! Join Types ! ! CROSS JOIN ! ! ! T1 ! CROSS ! JOIN ! T2 ! ! ! ! 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. ! ! ! ! ! FROM T1 ! CROSS JOIN ! T2, ! is equivalent to, ! FROM ! T1, ! T2. ! The tables listed in FROM are CROSS JOINed to form a ! derived table. ! ! ! ! ! ! ! Qualified JOINs ! ! ! T1 ! ! INNER ! ! ! LEFT ! RIGHT ! FULL ! ! OUTER ! ! ! JOIN ! T2 ! ! ON search condition ! USING ( join column list ) ! ! ... ! ! ! ! Only the qualified JOIN types can use the ON or USING clause. The ON clause ! takes a search condition, 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. ! ! ! ! ! ! ! ! INNER ! JOIN ! ! ! ! ! For each row R1 of T1, the joined table has a row for each row ! in T2 that satisfies the join specification with R1. ! ! ! ! The words INNER and OUTER are optional for all JOINs. ! INNER is the default. LEFT, RIGHT, and FULL are for ! OUTER JOINs only. ! ! ! ! ! ! ! ! ! LEFT ! OUTER ! JOIN ! ! ! ! ! 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. ! ! ! ! The joined table unconditionally has a row for each row in T1. ! ! ! ! ! ! ! ! ! RIGHT ! OUTER ! JOIN ! ! ! ! ! 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. ! ! ! ! The joined table unconditionally has a row for each row in T2. ! ! ! ! ! ! ! ! ! FULL ! OUTER ! JOIN ! ! ! ! ! First, a LEFT [OUTER] JOIN is performed. ! Then, Rule 2 of a RIGHT [OUTER] JOIN is performed. ! ! ! ! The joined table unconditionally has a row for every row of T1 ! and a row for every row of T2. ! ! ! ! ! ! ! ! ! ! ! NATURAL JOINs ! ! ! T1 ! NATURAL ! ! INNER ! ! ! LEFT ! RIGHT ! FULL ! ! OUTER ! ! ! JOIN ! T2 ! ! ! 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. ! ! ! ! ! ! ! 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 ! ! ! ! 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. ! ! ! ! ! WHERE clause ! ! ! 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. ! ! ! 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) ! ! ! 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. ! ! ! ! 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. ! ! ! ! ! GROUP BY and HAVING clauses ! ! ! 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. ! ! ! ! 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. ! ! ! ! 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. ! ! ! ! ! ORDER BY and LIMIT clauses ! ! ! 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. ! ! ! ! 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. ! ! ! ! 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. ! ! !