sql.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

       relation constructed by a query and to assign a computed relation to a
       relation name.
      </para>
     </listitem>

     <listitem>
      <para>
       Aggregate Functions: Operations such as
       <firstterm>average</firstterm>, <firstterm>sum</firstterm>,
       <firstterm>max</firstterm>, etc. can be applied to columns of a 
       relation to
       obtain a single quantity.
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <sect2 id="select">
    <title id="select-title">Select</title>

    <para>
     The most often used command in <acronym>SQL</acronym> is the
     <command>SELECT</command> statement,
     used to retrieve data. The syntax is:

     <synopsis>
SELECT [ ALL | DISTINCT [ ON ( <replaceable class="PARAMETER">expression</replaceable> [, ...] ) ] ]
    * | <replaceable class="PARAMETER">expression</replaceable> [ AS <replaceable class="PARAMETER">output_name</replaceable> ] [, ...]
    [ INTO [ TEMPORARY | TEMP ] [ TABLE ] <replaceable class="PARAMETER">new_table</replaceable> ]
    [ FROM <replaceable class="PARAMETER">from_item</replaceable> [, ...] ]
    [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
    [ GROUP BY <replaceable class="PARAMETER">expression</replaceable> [, ...] ]
    [ HAVING <replaceable class="PARAMETER">condition</replaceable> [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL ] <replaceable class="PARAMETER">select</replaceable> ]
    [ ORDER BY <replaceable class="PARAMETER">expression</replaceable> [ ASC | DESC | USING <replaceable class="PARAMETER">operator</replaceable> ] [, ...] ]
    [ LIMIT { <replaceable class="PARAMETER">count</replaceable> | ALL } ]
    [ OFFSET <replaceable class="PARAMETER">start</replaceable> ]
    [ FOR { UPDATE | SHARE } [ OF <replaceable class="parameter">table_name</replaceable> [, ...] ] [ NOWAIT ] ]
     </synopsis>
    </para>

    <para>
     Now we will illustrate the complex syntax of the
     <command>SELECT</command> statement with various examples. The
     tables used for the examples are defined in <xref
     linkend="supplier-fig" endterm="supplier-fig">.
    </para>

    <sect3>
     <title>Simple Selects</title>

     <para>
      Here are some simple examples using a <command>SELECT</command> statement:

      <example>
       <title id="simple-query">Simple Query with Qualification</title>
       <para>
	To retrieve all tuples from table PART where the attribute PRICE is
	greater than 10 we formulate the following query:

	<programlisting>
SELECT * FROM PART
    WHERE PRICE &gt; 10;
	</programlisting>

	and get the table:

	<programlisting>
 PNO |  PNAME  |  PRICE
-----+---------+--------
  3  |  Bolt   |   15
  4  |  Cam    |   25
	</programlisting>
       </para>

       <para>
	Using <quote>*</quote> in the <command>SELECT</command> statement
	will deliver all attributes from the table. If we want to retrieve
	only the attributes PNAME and PRICE from table PART we use the
	statement:

	<programlisting>
SELECT PNAME, PRICE 
    FROM PART
    WHERE PRICE &gt; 10;
	</programlisting>

	In this case the result is:

	<programlisting>
                      PNAME  |  PRICE
                     --------+--------
                      Bolt   |   15
                      Cam    |   25
	</programlisting>

	Note that the <acronym>SQL</acronym> <command>SELECT</command>
	corresponds to the <quote>projection</quote> in relational algebra
	not to the <quote>selection</quote> (see <xref linkend="rel-alg"
	endterm="rel-alg"> for more details).
       </para>

       <para>
	The qualifications in the WHERE clause can also be logically connected
	using the keywords OR, AND, and NOT:

	<programlisting>
SELECT PNAME, PRICE 
    FROM PART
    WHERE PNAME = 'Bolt' AND
         (PRICE = 0 OR PRICE &lt;= 15);
	</programlisting>

	will lead to the result:

	<programlisting>
 PNAME  |  PRICE
--------+--------
 Bolt   |   15
	</programlisting>
       </para>

       <para>
	Arithmetic operations may be used in the target list and in the WHERE
	clause. For example if we want to know how much it would cost if we
	take two pieces of a part we could use the following query:

	<programlisting>
SELECT PNAME, PRICE * 2 AS DOUBLE
    FROM PART
    WHERE PRICE * 2 &lt; 50;
	</programlisting>

	and we get:

	<programlisting>
 PNAME  |  DOUBLE
--------+---------
 Screw  |    20
 Nut    |    16
 Bolt   |    30
	</programlisting>

	Note that the word DOUBLE after the keyword AS is the new title of the
	second column. This technique can be used for every element of the
	target list to assign a new title to the resulting
	column. This new title
	is often referred to as alias. The alias cannot be used throughout the
	rest of the query.
       </para>
      </example>
     </para>
    </sect3>

    <sect3>
     <title>Joins</title>

     <para id="simple-join">
      The following example shows how <firstterm>joins</firstterm> are
      realized in <acronym>SQL</acronym>.
     </para>

     <para>
      To join the three tables SUPPLIER, PART and SELLS over their common
      attributes we formulate the following statement:

      <programlisting>
SELECT S.SNAME, P.PNAME
    FROM SUPPLIER S, PART P, SELLS SE
    WHERE S.SNO = SE.SNO AND
          P.PNO = SE.PNO;
      </programlisting>

      and get the following table as a result:

      <programlisting>
 SNAME | PNAME
-------+-------
 Smith | Screw
 Smith | Nut
 Jones | Cam
 Adams | Screw
 Adams | Bolt
 Blake | Nut
 Blake | Bolt
 Blake | Cam
      </programlisting>
     </para>

     <para>
      In the FROM clause we introduced an alias name for every relation
      because there are common named attributes (SNO and PNO) among the
      relations. Now we can distinguish between the common named attributes
      by simply prefixing the attribute name with the alias name followed by
      a dot. The join is calculated in the same way as shown in 
      <xref linkend="join-example" endterm="join-example">.
      First the Cartesian product

      SUPPLIER &times; PART &times; SELLS

      is derived. Now only those tuples satisfying the
      conditions given in the WHERE clause are selected (i.e. the common
      named attributes have to be equal). Finally we project out all
      columns but S.SNAME and P.PNAME. 
     </para>

	 <para>
	 Another way to perform joins is to use the SQL JOIN syntax as follows:
	 <programlisting>
select sname, pname from supplier
	JOIN sells USING (sno)
	JOIN part USING (pno);
	</programlisting>
	giving again:
	<programlisting>
 sname | pname
-------+-------
 Smith | Screw
 Adams | Screw
 Smith | Nut
 Blake | Nut
 Adams | Bolt
 Blake | Bolt
 Jones | Cam
 Blake | Cam
(8 rows) 
	 </programlisting>
	 </para>

	 <para>
	 A joined table, created using JOIN syntax, is a table reference list
	 item that occurs in a FROM clause and before any WHERE, GROUP BY,
	 or HAVING clause.  Other table references, including table names or
	 other JOIN clauses, may be included in the FROM clause if separated
	 by commas.  JOINed tables are logically like any other
	 table listed in the FROM clause.
	 </para>

	 <para>
	  SQL JOINs come in two main types, CROSS JOINs (unqualified joins)
	  and <firstterm>qualified JOINs</>.  Qualified joins can be further
	  subdivided based on the way in which the <firstterm>join condition</>
	  is specified (ON, USING, or NATURAL) and the way in which it is
	  applied (INNER or OUTER join).
	 </para>

    <variablelist>
        <title>Join Types</title>
        <varlistentry>
            <term>CROSS JOIN</term>
	        <listitem>
			<cmdsynopsis>
			    <arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
			    <command> CROSS 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 all
			N*M possible 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
			equivalent to an INNER JOIN ON TRUE.
			</para>
			</listitem>
		</varlistentry>

		<varlistentry>
		    <term>Qualified JOINs</term>
		    <listitem>

			<cmdsynopsis>
			<arg choice="req"> <replaceable class="parameter">T1</replaceable> </arg>
			<arg choice="opt"> 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>
			<group choice="req">
				<arg> ON <replaceable>search condition</replaceable></arg>
				<arg> USING ( <replaceable>join column list</replaceable> ) </arg>
			</group>
			</cmdsynopsis>

			<para>
			A qualified JOIN must specify its join condition
			by providing one (and only one) of NATURAL, ON, or
			USING.  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 equality of those columns.  NATURAL is
			shorthand for a USING clause that lists all the common
			column names of the two tables.  A side-effect of both
			USING and NATURAL is that only one copy of each joined
			column is emitted into the result table (compare the
			relational-algebra definition of JOIN, shown earlier).
			</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 condition with R1. 
				</para>
				<tip>
				<para>
					The words INNER and OUTER are optional for all JOINs.
					INNER is the default.  LEFT, RIGHT, and FULL imply an
					OUTER JOIN.
					</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, for each row in T1 that does not satisfy the join
				condition with any row in T2, an additional joined row is
				returned with null fields in the 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>
                First, an INNER JOIN is performed.
                Then, for each row in T2 that does not satisfy the join
				condition with any row in T1, an additional joined row is
				returned with null fields in the 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, an INNER JOIN is performed.
                Then, for each row in T1 that does not satisfy the join
				condition with any row in T2, an additional joined row is
				returned with null fields in the columns from T2.
                Also, for each row in T2 that does not satisfy the join
				condition with any row in T1, an additional joined row is
				returned with null fields in the columns from T1.
				</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>
	 </variablelist>

	 <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.
	 Parenthesis can be used around JOIN clauses to control the order