1#@!#!123s

: / usr / share / doc / postgresql-9.2.24 / html /
Filename : queries-table-expressions.html
back
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Table Expressions</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 9.2.24 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Queries"
HREF="queries.html"><LINK
REL="PREVIOUS"
TITLE="Overview"
HREF="queries-overview.html"><LINK
REL="NEXT"
TITLE="Select Lists"
HREF="queries-select-lists.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2017-11-06T22:43:11"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
><A
HREF="index.html"
>PostgreSQL 9.2.24 Documentation</A
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
TITLE="Overview"
HREF="queries-overview.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="queries.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 7. Queries</TD
><TD
WIDTH="20%"
ALIGN="right"
VALIGN="top"
><A
TITLE="Select Lists"
HREF="queries-select-lists.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="QUERIES-TABLE-EXPRESSIONS"
>7.2. Table Expressions</A
></H1
><P
>   A <I
CLASS="FIRSTTERM"
>table expression</I
> computes a table.  The
   table expression contains a <TT
CLASS="LITERAL"
>FROM</TT
> clause that is
   optionally followed by <TT
CLASS="LITERAL"
>WHERE</TT
>, <TT
CLASS="LITERAL"
>GROUP BY</TT
>, and
   <TT
CLASS="LITERAL"
>HAVING</TT
> clauses.  Trivial table expressions simply refer
   to a table on disk, a so-called base table, but more complex
   expressions can be used to modify or combine base tables in various
   ways.
  </P
><P
>   The optional <TT
CLASS="LITERAL"
>WHERE</TT
>, <TT
CLASS="LITERAL"
>GROUP BY</TT
>, and
   <TT
CLASS="LITERAL"
>HAVING</TT
> clauses in the table expression specify a
   pipeline of successive transformations performed on the table
   derived in the <TT
CLASS="LITERAL"
>FROM</TT
> clause.  All these transformations
   produce a virtual table that provides the rows that are passed to
   the select list to compute the output rows of the query.
  </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="QUERIES-FROM"
>7.2.1. The <TT
CLASS="LITERAL"
>FROM</TT
> Clause</A
></H2
><P
>    The <A
HREF="sql-select.html#SQL-FROM"
><I
><I
>FROM</I
> Clause</I
></A
> derives a
    table from one or more other tables given in a comma-separated
    table reference list.
</P><PRE
CLASS="SYNOPSIS"
>FROM <TT
CLASS="REPLACEABLE"
><I
>table_reference</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>table_reference</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ...</SPAN
>]</SPAN
>]</PRE
><P>

    A table reference can be a table name (possibly schema-qualified),
    or a derived table such as a subquery, a <TT
CLASS="LITERAL"
>JOIN</TT
> construct, or
    complex combinations of these.  If more than one table reference is
    listed in the <TT
CLASS="LITERAL"
>FROM</TT
> clause, the tables are cross-joined
    (that is, the Cartesian product of their rows is formed; see below).
    The result of the <TT
CLASS="LITERAL"
>FROM</TT
> list is an intermediate virtual
    table that can then be subject to
    transformations by the <TT
CLASS="LITERAL"
>WHERE</TT
>, <TT
CLASS="LITERAL"
>GROUP BY</TT
>,
    and <TT
CLASS="LITERAL"
>HAVING</TT
> clauses and is finally the result of the
    overall table expression.
   </P
><P
>    When a table reference names a table that is the parent of a
    table inheritance hierarchy, the table reference produces rows of
    not only that table but all of its descendant tables, unless the
    key word <TT
CLASS="LITERAL"
>ONLY</TT
> precedes the table name.  However, the
    reference produces only the columns that appear in the named table
    &mdash; any columns added in subtables are ignored.
   </P
><P
>    Instead of writing <TT
CLASS="LITERAL"
>ONLY</TT
> before the table name, you can write
    <TT
CLASS="LITERAL"
>*</TT
> after the table name to explicitly specify that descendant
    tables are included.  Writing <TT
CLASS="LITERAL"
>*</TT
> is not necessary since that
    behavior is the default (unless you have changed the setting of the <A
HREF="runtime-config-compatible.html#GUC-SQL-INHERITANCE"
>sql_inheritance</A
> configuration option).  However writing
    <TT
CLASS="LITERAL"
>*</TT
> might be useful to emphasize that additional tables will be
    searched.
   </P
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="QUERIES-JOIN"
>7.2.1.1. Joined Tables</A
></H3
><P
>     A joined table is a table derived from two other (real or
     derived) tables according to the rules of the particular join
     type.  Inner, outer, and cross-joins are available.
     The general syntax of a joined table is
</P><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>join_type</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> [<SPAN
CLASS="OPTIONAL"
> <TT
CLASS="REPLACEABLE"
><I
>join_condition</I
></TT
> </SPAN
>]</PRE
><P>
     Joins of all types can be chained together, or nested: either or
     both <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> and
     <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> can be joined tables.  Parentheses
     can be used around <TT
CLASS="LITERAL"
>JOIN</TT
> clauses to control the join
     order.  In the absence of parentheses, <TT
CLASS="LITERAL"
>JOIN</TT
> clauses
     nest left-to-right.
    </P
><P
></P
><DIV
CLASS="VARIABLELIST"
><P
><B
>Join Types</B
></P
><DL
><DT
>Cross join</DT
><DD
><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> CROSS JOIN <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
></PRE
><P
>        For every possible combination of rows from
        <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> and
        <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> (i.e., a Cartesian product),
        the joined table will contain a
        row consisting of all columns in <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>
        followed by all columns in <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
>.  If
        the tables have N and M rows respectively, the joined
        table will have N * M rows.
       </P
><P
>        <TT
CLASS="LITERAL"
>FROM <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> CROSS JOIN
        <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
></TT
> is equivalent to
        <TT
CLASS="LITERAL"
>FROM <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> INNER JOIN
        <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> ON TRUE</TT
> (see below).
        It is also equivalent to
        <TT
CLASS="LITERAL"
>FROM <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>,
        <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
></TT
>.
        </P><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>         This latter equivalence does not hold exactly when more than two
         tables appear, because <TT
CLASS="LITERAL"
>JOIN</TT
> binds more tightly than
         comma.  For example
         <TT
CLASS="LITERAL"
>FROM <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> CROSS JOIN
         <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> INNER JOIN <TT
CLASS="REPLACEABLE"
><I
>T3</I
></TT
>
         ON <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
></TT
>
         is not the same as
         <TT
CLASS="LITERAL"
>FROM <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>,
         <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> INNER JOIN <TT
CLASS="REPLACEABLE"
><I
>T3</I
></TT
>
         ON <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
></TT
>
         because the <TT
CLASS="REPLACEABLE"
><I
>condition</I
></TT
> can
         reference <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> in the first case but not
         the second.
        </P
></BLOCKQUOTE
></DIV
><P>
       </P
></DD
><DT
>Qualified joins</DT
><DD
><PRE
CLASS="SYNOPSIS"
><TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> { [<SPAN
CLASS="OPTIONAL"
>INNER</SPAN
>] | { LEFT | RIGHT | FULL } [<SPAN
CLASS="OPTIONAL"
>OUTER</SPAN
>] } JOIN <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> ON <TT
CLASS="REPLACEABLE"
><I
>boolean_expression</I
></TT
>
<TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> { [<SPAN
CLASS="OPTIONAL"
>INNER</SPAN
>] | { LEFT | RIGHT | FULL } [<SPAN
CLASS="OPTIONAL"
>OUTER</SPAN
>] } JOIN <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> USING ( <TT
CLASS="REPLACEABLE"
><I
>join column list</I
></TT
> )
<TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> NATURAL { [<SPAN
CLASS="OPTIONAL"
>INNER</SPAN
>] | { LEFT | RIGHT | FULL } [<SPAN
CLASS="OPTIONAL"
>OUTER</SPAN
>] } JOIN <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
></PRE
><P
>        The words <TT
CLASS="LITERAL"
>INNER</TT
> and
        <TT
CLASS="LITERAL"
>OUTER</TT
> are optional in all forms.
        <TT
CLASS="LITERAL"
>INNER</TT
> is the default;
        <TT
CLASS="LITERAL"
>LEFT</TT
>, <TT
CLASS="LITERAL"
>RIGHT</TT
>, and
        <TT
CLASS="LITERAL"
>FULL</TT
> imply an outer join.
       </P
><P
>        The <I
CLASS="FIRSTTERM"
>join condition</I
> is specified in the
        <TT
CLASS="LITERAL"
>ON</TT
> or <TT
CLASS="LITERAL"
>USING</TT
> clause, or implicitly by
        the word <TT
CLASS="LITERAL"
>NATURAL</TT
>.  The join condition determines
        which rows from the two source tables are considered to
        <SPAN
CLASS="QUOTE"
>"match"</SPAN
>, as explained in detail below.
       </P
><P
>        The possible types of qualified join are:

       <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
><TT
CLASS="LITERAL"
>INNER JOIN</TT
></DT
><DD
><P
>           For each row R1 of T1, the joined table has a row for each
           row in T2 that satisfies the join condition with R1.
          </P
></DD
><DT
><TT
CLASS="LITERAL"
>LEFT OUTER JOIN</TT
></DT
><DD
><P
>           First, an inner join is performed.  Then, for each row in
           T1 that does not satisfy the join condition with any row in
           T2, a joined row is added with null values in columns of
           T2.  Thus, the joined table always has at least
           one row for each row in T1.
          </P
></DD
><DT
><TT
CLASS="LITERAL"
>RIGHT OUTER JOIN</TT
></DT
><DD
><P
>           First, an inner join is performed.  Then, for each row in
           T2 that does not satisfy the join condition with any row in
           T1, a joined row is added with null values in columns of
           T1.  This is the converse of a left join: the result table
           will always have a row for each row in T2.
          </P
></DD
><DT
><TT
CLASS="LITERAL"
>FULL OUTER JOIN</TT
></DT
><DD
><P
>           First, an inner join is performed.  Then, for each row in
           T1 that does not satisfy the join condition with any row in
           T2, a joined row is added with null values in columns of
           T2.  Also, for each row of T2 that does not satisfy the
           join condition with any row in T1, a joined row with null
           values in the columns of T1 is added.
          </P
></DD
></DL
></DIV
><P>
       </P
><P
>        The <TT
CLASS="LITERAL"
>ON</TT
> clause is the most general kind of join
        condition: it takes a Boolean value expression of the same
        kind as is used in a <TT
CLASS="LITERAL"
>WHERE</TT
> clause.  A pair of rows
        from <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> and <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> match if the
        <TT
CLASS="LITERAL"
>ON</TT
> expression evaluates to true.
       </P
><P
>        The <TT
CLASS="LITERAL"
>USING</TT
> clause is a shorthand that allows you to take
        advantage of the specific situation where both sides of the join use
        the same name for the joining column(s).  It takes a
        comma-separated list of the shared column names
        and forms a join condition that includes an equality comparison
        for each one.  For example, joining <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>
        and <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
> with <TT
CLASS="LITERAL"
>USING (a, b)</TT
> produces
        the join condition <TT
CLASS="LITERAL"
>ON <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>.a
        = <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
>.a AND <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>.b
        = <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
>.b</TT
>.
       </P
><P
>        Furthermore, the output of <TT
CLASS="LITERAL"
>JOIN USING</TT
> suppresses
        redundant columns: there is no need to print both of the matched
        columns, since they must have equal values.  While <TT
CLASS="LITERAL"
>JOIN
        ON</TT
> produces all columns from <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
> followed by all
        columns from <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
>, <TT
CLASS="LITERAL"
>JOIN USING</TT
> produces one
        output column for each of the listed column pairs (in the listed
        order), followed by any remaining columns from <TT
CLASS="REPLACEABLE"
><I
>T1</I
></TT
>,
        followed by any remaining columns from <TT
CLASS="REPLACEABLE"
><I
>T2</I
></TT
>.
       </P
><P
>        
        
        Finally, <TT
CLASS="LITERAL"
>NATURAL</TT
> is a shorthand form of
        <TT
CLASS="LITERAL"
>USING</TT
>: it forms a <TT
CLASS="LITERAL"
>USING</TT
> list
        consisting of all column names that appear in both
        input tables.  As with <TT
CLASS="LITERAL"
>USING</TT
>, these columns appear
        only once in the output table.  If there are no common
        column names, <TT
CLASS="LITERAL"
>NATURAL JOIN</TT
> behaves like
        <TT
CLASS="LITERAL"
>JOIN ... ON TRUE</TT
>, producing a cross-product join.
       </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>         <TT
CLASS="LITERAL"
>USING</TT
> is reasonably safe from column changes
         in the joined relations since only the listed columns
         are combined.  <TT
CLASS="LITERAL"
>NATURAL</TT
> is considerably more risky since
         any schema changes to either relation that cause a new matching
         column name to be present will cause the join to combine that new
         column as well.
        </P
></BLOCKQUOTE
></DIV
></DD
></DL
></DIV
><P
>     To put this together, assume we have tables <TT
CLASS="LITERAL"
>t1</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
> num | name
-----+------
   1 | a
   2 | b
   3 | c</PRE
><P>
     and <TT
CLASS="LITERAL"
>t2</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
> num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz</PRE
><P>
     then we get the following results for the various joins:
</P><PRE
CLASS="SCREEN"
><SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 CROSS JOIN t2;</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 INNER JOIN t2 USING (num);</KBD
>
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 NATURAL INNER JOIN t2;</KBD
>
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 LEFT JOIN t2 USING (num);</KBD
>
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)</PRE
><P>
    </P
><P
>     The join condition specified with <TT
CLASS="LITERAL"
>ON</TT
> can also contain
     conditions that do not relate directly to the join.  This can
     prove useful for some queries but needs to be thought out
     carefully.  For example:
</P><PRE
CLASS="SCREEN"
><SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)</PRE
><P>
     Notice that placing the restriction in the <TT
CLASS="LITERAL"
>WHERE</TT
> clause
     produces a different result:
</P><PRE
CLASS="SCREEN"
><SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</KBD
>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)</PRE
><P>
     This is because a restriction placed in the <TT
CLASS="LITERAL"
>ON</TT
>
     clause is processed <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>before</I
></SPAN
> the join, while
     a restriction placed in the <TT
CLASS="LITERAL"
>WHERE</TT
> clause is processed
     <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>after</I
></SPAN
> the join.
     That does not matter with inner joins, but it matters a lot with outer
     joins.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="QUERIES-TABLE-ALIASES"
>7.2.1.2. Table and Column Aliases</A
></H3
><P
>     A temporary name can be given to tables and complex table
     references to be used for references to the derived table in
     the rest of the query.  This is called a <I
CLASS="FIRSTTERM"
>table
     alias</I
>.
    </P
><P
>     To create a table alias, write
</P><PRE
CLASS="SYNOPSIS"
>FROM <TT
CLASS="REPLACEABLE"
><I
>table_reference</I
></TT
> AS <TT
CLASS="REPLACEABLE"
><I
>alias</I
></TT
></PRE
><P>
     or
</P><PRE
CLASS="SYNOPSIS"
>FROM <TT
CLASS="REPLACEABLE"
><I
>table_reference</I
></TT
> <TT
CLASS="REPLACEABLE"
><I
>alias</I
></TT
></PRE
><P>
     The <TT
CLASS="LITERAL"
>AS</TT
> key word is optional noise.
     <TT
CLASS="REPLACEABLE"
><I
>alias</I
></TT
> can be any identifier.
    </P
><P
>     A typical application of table aliases is to assign short
     identifiers to long table names to keep the join clauses
     readable.  For example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;</PRE
><P>
    </P
><P
>     The alias becomes the new name of the table reference so far as the
     current query is concerned &mdash; it is not allowed to refer to the
     table by the original name elsewhere in the query.  Thus, this is not
     valid:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM my_table AS m WHERE my_table.a &gt; 5;    -- wrong</PRE
><P>
    </P
><P
>     Table aliases are mainly for notational convenience, but it is
     necessary to use them when joining a table to itself, e.g.:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;</PRE
><P>
     Additionally, an alias is required if the table reference is a
     subquery (see <A
HREF="queries-table-expressions.html#QUERIES-SUBQUERIES"
>Section 7.2.1.3</A
>).
    </P
><P
>     Parentheses are used to resolve ambiguities.  In the following example,
     the first statement assigns the alias <TT
CLASS="LITERAL"
>b</TT
> to the second
     instance of <TT
CLASS="LITERAL"
>my_table</TT
>, but the second statement assigns the
     alias to the result of the join:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...</PRE
><P>
    </P
><P
>     Another form of table aliasing gives temporary names to the columns of
     the table, as well as the table itself:
</P><PRE
CLASS="SYNOPSIS"
>FROM <TT
CLASS="REPLACEABLE"
><I
>table_reference</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>AS</SPAN
>] <TT
CLASS="REPLACEABLE"
><I
>alias</I
></TT
> ( <TT
CLASS="REPLACEABLE"
><I
>column1</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>column2</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, ...</SPAN
>]</SPAN
>] )</PRE
><P>
     If fewer column aliases are specified than the actual table has
     columns, the remaining columns are not renamed.  This syntax is
     especially useful for self-joins or subqueries.
    </P
><P
>     When an alias is applied to the output of a <TT
CLASS="LITERAL"
>JOIN</TT
>
     clause, the alias hides the original
     name(s) within the <TT
CLASS="LITERAL"
>JOIN</TT
>.  For example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...</PRE
><P>
     is valid SQL, but:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c</PRE
><P>
     is not valid; the table alias <TT
CLASS="LITERAL"
>a</TT
> is not visible
     outside the alias <TT
CLASS="LITERAL"
>c</TT
>.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="QUERIES-SUBQUERIES"
>7.2.1.3. Subqueries</A
></H3
><P
>     Subqueries specifying a derived table must be enclosed in
     parentheses and <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>must</I
></SPAN
> be assigned a table
     alias name.  (See <A
HREF="queries-table-expressions.html#QUERIES-TABLE-ALIASES"
>Section 7.2.1.2</A
>.)  For
     example:
</P><PRE
CLASS="PROGRAMLISTING"
>FROM (SELECT * FROM table1) AS alias_name</PRE
><P>
    </P
><P
>     This example is equivalent to <TT
CLASS="LITERAL"
>FROM table1 AS
     alias_name</TT
>.  More interesting cases, which cannot be
     reduced to a plain join, arise when the subquery involves
     grouping or aggregation.
    </P
><P
>     A subquery can also be a <TT
CLASS="COMMAND"
>VALUES</TT
> list:
</P><PRE
CLASS="PROGRAMLISTING"
>FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
     AS names(first, last)</PRE
><P>
     Again, a table alias is required.  Assigning alias names to the columns
     of the <TT
CLASS="COMMAND"
>VALUES</TT
> list is optional, but is good practice.
     For more information see <A
HREF="queries-values.html"
>Section 7.7</A
>.
    </P
></DIV
><DIV
CLASS="SECT3"
><H3
CLASS="SECT3"
><A
NAME="QUERIES-TABLEFUNCTIONS"
>7.2.1.4. Table Functions</A
></H3
><P
>     Table functions are functions that produce a set of rows, made up
     of either base data types (scalar types) or composite data types
     (table rows).  They are used like a table, view, or subquery in
     the <TT
CLASS="LITERAL"
>FROM</TT
> clause of a query. Columns returned by table
     functions can be included in <TT
CLASS="LITERAL"
>SELECT</TT
>,
     <TT
CLASS="LITERAL"
>JOIN</TT
>, or <TT
CLASS="LITERAL"
>WHERE</TT
> clauses in the same manner
     as a table, view, or subquery column.
    </P
><P
>     If a table function returns a base data type, the single result
     column name matches the function name. If the function returns a
     composite type, the result columns get the same names as the
     individual attributes of the type.
    </P
><P
>     A table function can be aliased in the <TT
CLASS="LITERAL"
>FROM</TT
> clause,
     but it also can be left unaliased. If a function is used in the
     <TT
CLASS="LITERAL"
>FROM</TT
> clause with no alias, the function name is used
     as the resulting table name.
    </P
><P
>     Some examples:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TABLE foo (fooid int, foosubid int, fooname text);

CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
    SELECT * FROM foo WHERE fooid = $1;
$$ LANGUAGE SQL;

SELECT * FROM getfoo(1) AS t1;

SELECT * FROM foo
    WHERE foosubid IN (
                        SELECT foosubid
                        FROM getfoo(foo.fooid) z
                        WHERE z.fooid = foo.fooid
                      );

CREATE VIEW vw_getfoo AS SELECT * FROM getfoo(1);

SELECT * FROM vw_getfoo;</PRE
><P>
    </P
><P
>     In some cases it is useful to define table functions that can
     return different column sets depending on how they are invoked.
     To support this, the table function can be declared as returning
     the pseudotype <TT
CLASS="TYPE"
>record</TT
>.  When such a function is used in
     a query, the expected row structure must be specified in the
     query itself, so that the system can know how to parse and plan
     the query.  Consider this example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT *
    FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';</PRE
><P>
     The <A
HREF="contrib-dblink-function.html"
>dblink</A
> function
     (part of the <A
HREF="dblink.html"
>dblink</A
> module&#62;) executes
     a remote query.  It is declared to return
     <TT
CLASS="TYPE"
>record</TT
> since it might be used for any kind of query.
     The actual column set must be specified in the calling query so
     that the parser knows, for example, what <TT
CLASS="LITERAL"
>*</TT
> should
     expand to.
    </P
></DIV
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="QUERIES-WHERE"
>7.2.2. The <TT
CLASS="LITERAL"
>WHERE</TT
> Clause</A
></H2
><P
>    The syntax of the <A
HREF="sql-select.html#SQL-WHERE"
><I
><I
>WHERE</I
> Clause</I
></A
> is
</P><PRE
CLASS="SYNOPSIS"
>WHERE <TT
CLASS="REPLACEABLE"
><I
>search_condition</I
></TT
></PRE
><P>
    where <TT
CLASS="REPLACEABLE"
><I
>search_condition</I
></TT
> is any value
    expression (see <A
HREF="sql-expressions.html"
>Section 4.2</A
>) that
    returns a value of type <TT
CLASS="TYPE"
>boolean</TT
>.
   </P
><P
>    After the processing of the <TT
CLASS="LITERAL"
>FROM</TT
> clause is done, each
    row of the derived virtual table is checked against the search
    condition.  If the result of the condition is true, the row is
    kept in the output table, otherwise (i.e., if the result is
    false or null) it is discarded.  The search condition typically
    references at least one column of the table generated in the
    <TT
CLASS="LITERAL"
>FROM</TT
> clause; this is not required, but otherwise the
    <TT
CLASS="LITERAL"
>WHERE</TT
> clause will be fairly useless.
   </P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>     The join condition of an inner join can be written either in
     the <TT
CLASS="LITERAL"
>WHERE</TT
> clause or in the <TT
CLASS="LITERAL"
>JOIN</TT
> clause.
     For example, these table expressions are equivalent:
</P><PRE
CLASS="PROGRAMLISTING"
>FROM a, b WHERE a.id = b.id AND b.val &gt; 5</PRE
><P>
     and:
</P><PRE
CLASS="PROGRAMLISTING"
>FROM a INNER JOIN b ON (a.id = b.id) WHERE b.val &gt; 5</PRE
><P>
     or perhaps even:
</P><PRE
CLASS="PROGRAMLISTING"
>FROM a NATURAL JOIN b WHERE b.val &gt; 5</PRE
><P>
     Which one of these you use is mainly a matter of style.  The
     <TT
CLASS="LITERAL"
>JOIN</TT
> syntax in the <TT
CLASS="LITERAL"
>FROM</TT
> clause is
     probably not as portable to other SQL database management systems,
     even though it is in the SQL standard.  For
     outer joins there is no choice:  they must be done in
     the <TT
CLASS="LITERAL"
>FROM</TT
> clause.  The <TT
CLASS="LITERAL"
>ON</TT
> or <TT
CLASS="LITERAL"
>USING</TT
>
     clause of an outer join is <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>not</I
></SPAN
> equivalent to a
     <TT
CLASS="LITERAL"
>WHERE</TT
> condition, because it results in the addition
     of rows (for unmatched input rows) as well as the removal of rows
     in the final result.
    </P
></BLOCKQUOTE
></DIV
><P
>    Here are some examples of <TT
CLASS="LITERAL"
>WHERE</TT
> clauses:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT ... FROM fdt WHERE c1 &gt; 5

SELECT ... FROM fdt WHERE c1 IN (1, 2, 3)

SELECT ... FROM fdt WHERE c1 IN (SELECT c1 FROM t2)

SELECT ... FROM fdt WHERE c1 IN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10)

SELECT ... FROM fdt WHERE c1 BETWEEN (SELECT c3 FROM t2 WHERE c2 = fdt.c1 + 10) AND 100

SELECT ... FROM fdt WHERE EXISTS (SELECT c1 FROM t2 WHERE c2 &gt; fdt.c1)</PRE
><P>
    <TT
CLASS="LITERAL"
>fdt</TT
> is the table derived in the
    <TT
CLASS="LITERAL"
>FROM</TT
> clause. Rows that do not meet the search
    condition of the <TT
CLASS="LITERAL"
>WHERE</TT
> clause are eliminated from
    <TT
CLASS="LITERAL"
>fdt</TT
>. Notice the use of scalar subqueries as
    value expressions.  Just like any other query, the subqueries can
    employ complex table expressions.  Notice also how
    <TT
CLASS="LITERAL"
>fdt</TT
> is referenced in the subqueries.
    Qualifying <TT
CLASS="LITERAL"
>c1</TT
> as <TT
CLASS="LITERAL"
>fdt.c1</TT
> is only necessary
    if <TT
CLASS="LITERAL"
>c1</TT
> is also the name of a column in the derived
    input table of the subquery.  But qualifying the column name adds
    clarity even when it is not needed.  This example shows how the column
    naming scope of an outer query extends into its inner queries.
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="QUERIES-GROUP"
>7.2.3. The <TT
CLASS="LITERAL"
>GROUP BY</TT
> and <TT
CLASS="LITERAL"
>HAVING</TT
> Clauses</A
></H2
><P
>    After passing the <TT
CLASS="LITERAL"
>WHERE</TT
> filter, the derived input
    table might be subject to grouping, using the <TT
CLASS="LITERAL"
>GROUP BY</TT
>
    clause, and elimination of group rows using the <TT
CLASS="LITERAL"
>HAVING</TT
>
    clause.
   </P
><PRE
CLASS="SYNOPSIS"
>SELECT <TT
CLASS="REPLACEABLE"
><I
>select_list</I
></TT
>
    FROM ...
    [<SPAN
CLASS="OPTIONAL"
>WHERE ...</SPAN
>]
    GROUP BY <TT
CLASS="REPLACEABLE"
><I
>grouping_column_reference</I
></TT
> [<SPAN
CLASS="OPTIONAL"
>, <TT
CLASS="REPLACEABLE"
><I
>grouping_column_reference</I
></TT
></SPAN
>]...</PRE
><P
>    The <A
HREF="sql-select.html#SQL-GROUPBY"
><I
><I
>GROUP BY</I
> Clause</I
></A
> is
    used to group together those rows in a table that have the same
    values in all the columns listed. The order in which the columns
    are listed does not matter.  The effect is to combine each set
    of rows having common values into one group row that
    represents all rows in the group.  This is done to
    eliminate redundancy in the output and/or compute aggregates that
    apply to these groups.  For instance:
</P><PRE
CLASS="SCREEN"
><SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT * FROM test1;</KBD
>
 x | y
---+---
 a | 3
 c | 2
 b | 5
 a | 1
(4 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT x FROM test1 GROUP BY x;</KBD
>
 x
---
 a
 b
 c
(3 rows)</PRE
><P>
   </P
><P
>    In the second query, we could not have written <TT
CLASS="LITERAL"
>SELECT *
    FROM test1 GROUP BY x</TT
>, because there is no single value
    for the column <TT
CLASS="LITERAL"
>y</TT
> that could be associated with each
    group.  The grouped-by columns can be referenced in the select list since
    they have a single value in each group.
   </P
><P
>    In general, if a table is grouped, columns that are not
    listed in <TT
CLASS="LITERAL"
>GROUP BY</TT
> cannot be referenced except in aggregate
    expressions.  An example with aggregate expressions is:
</P><PRE
CLASS="SCREEN"
><SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT x, sum(y) FROM test1 GROUP BY x;</KBD
>
 x | sum
---+-----
 a |   4
 b |   5
 c |   2
(3 rows)</PRE
><P>
    Here <TT
CLASS="LITERAL"
>sum</TT
> is an aggregate function that
    computes a single value over the entire group.  More information
    about the available aggregate functions can be found in <A
HREF="functions-aggregate.html"
>Section 9.20</A
>.
   </P
><DIV
CLASS="TIP"
><BLOCKQUOTE
CLASS="TIP"
><P
><B
>Tip: </B
>     Grouping without aggregate expressions effectively calculates the
     set of distinct values in a column.  This can also be achieved
     using the <TT
CLASS="LITERAL"
>DISTINCT</TT
> clause (see <A
HREF="queries-select-lists.html#QUERIES-DISTINCT"
>Section 7.3.3</A
>).
    </P
></BLOCKQUOTE
></DIV
><P
>    Here is another example:  it calculates the total sales for each
    product (rather than the total sales of all products):
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT product_id, p.name, (sum(s.units) * p.price) AS sales
    FROM products p LEFT JOIN sales s USING (product_id)
    GROUP BY product_id, p.name, p.price;</PRE
><P>
    In this example, the columns <TT
CLASS="LITERAL"
>product_id</TT
>,
    <TT
CLASS="LITERAL"
>p.name</TT
>, and <TT
CLASS="LITERAL"
>p.price</TT
> must be
    in the <TT
CLASS="LITERAL"
>GROUP BY</TT
> clause since they are referenced in
    the query select list (but see below).  The column
    <TT
CLASS="LITERAL"
>s.units</TT
> does not have to be in the <TT
CLASS="LITERAL"
>GROUP
    BY</TT
> list since it is only used in an aggregate expression
    (<TT
CLASS="LITERAL"
>sum(...)</TT
>), which represents the sales
    of a product.  For each product, the query returns a summary row about
    all sales of the product.
   </P
><P
>    If the products table is set up so that, say,
    <TT
CLASS="LITERAL"
>product_id</TT
> is the primary key, then it would be
    enough to group by <TT
CLASS="LITERAL"
>product_id</TT
> in the above example,
    since name and price would be <I
CLASS="FIRSTTERM"
>functionally
    dependent</I
> on the product ID, and so there would be no
    ambiguity about which name and price value to return for each product
    ID group.
   </P
><P
>    In strict SQL, <TT
CLASS="LITERAL"
>GROUP BY</TT
> can only group by columns of
    the source table but <SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> extends
    this to also allow <TT
CLASS="LITERAL"
>GROUP BY</TT
> to group by columns in the
    select list.  Grouping by value expressions instead of simple
    column names is also allowed.
   </P
><P
>    If a table has been grouped using <TT
CLASS="LITERAL"
>GROUP BY</TT
>,
    but only certain groups are of interest, the
    <TT
CLASS="LITERAL"
>HAVING</TT
> clause can be used, much like a
    <TT
CLASS="LITERAL"
>WHERE</TT
> clause, to eliminate groups from the result.
    The syntax is:
</P><PRE
CLASS="SYNOPSIS"
>SELECT <TT
CLASS="REPLACEABLE"
><I
>select_list</I
></TT
> FROM ... [<SPAN
CLASS="OPTIONAL"
>WHERE ...</SPAN
>] GROUP BY ... HAVING <TT
CLASS="REPLACEABLE"
><I
>boolean_expression</I
></TT
></PRE
><P>
    Expressions in the <TT
CLASS="LITERAL"
>HAVING</TT
> clause can refer both to
    grouped expressions and to ungrouped expressions (which necessarily
    involve an aggregate function).
   </P
><P
>    Example:
</P><PRE
CLASS="SCREEN"
><SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT x, sum(y) FROM test1 GROUP BY x HAVING sum(y) &gt; 3;</KBD
>
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)

<SAMP
CLASS="PROMPT"
>=&gt;</SAMP
> <KBD
CLASS="USERINPUT"
>SELECT x, sum(y) FROM test1 GROUP BY x HAVING x &lt; 'c';</KBD
>
 x | sum
---+-----
 a |   4
 b |   5
(2 rows)</PRE
><P>
   </P
><P
>    Again, a more realistic example:
</P><PRE
CLASS="PROGRAMLISTING"
>SELECT product_id, p.name, (sum(s.units) * (p.price - p.cost)) AS profit
    FROM products p LEFT JOIN sales s USING (product_id)
    WHERE s.date &gt; CURRENT_DATE - INTERVAL '4 weeks'
    GROUP BY product_id, p.name, p.price, p.cost
    HAVING sum(p.price * s.units) &gt; 5000;</PRE
><P>
    In the example above, the <TT
CLASS="LITERAL"
>WHERE</TT
> clause is selecting
    rows by a column that is not grouped (the expression is only true for
    sales during the last four weeks), while the <TT
CLASS="LITERAL"
>HAVING</TT
>
    clause restricts the output to groups with total gross sales over
    5000.  Note that the aggregate expressions do not necessarily need
    to be the same in all parts of the query.
   </P
><P
>    If a query contains aggregate function calls, but no <TT
CLASS="LITERAL"
>GROUP BY</TT
>
    clause, grouping still occurs: the result is a single group row (or
    perhaps no rows at all, if the single row is then eliminated by
    <TT
CLASS="LITERAL"
>HAVING</TT
>).
    The same is true if it contains a <TT
CLASS="LITERAL"
>HAVING</TT
> clause, even
    without any aggregate function calls or <TT
CLASS="LITERAL"
>GROUP BY</TT
> clause.
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="QUERIES-WINDOW"
>7.2.4. Window Function Processing</A
></H2
><P
>    If the query contains any window functions (see
    <A
HREF="tutorial-window.html"
>Section 3.5</A
>,
    <A
HREF="functions-window.html"
>Section 9.21</A
> and
    <A
HREF="sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS"
>Section 4.2.8</A
>), these functions are evaluated
    after any grouping, aggregation, and <TT
CLASS="LITERAL"
>HAVING</TT
> filtering is
    performed.  That is, if the query uses any aggregates, <TT
CLASS="LITERAL"
>GROUP
    BY</TT
>, or <TT
CLASS="LITERAL"
>HAVING</TT
>, then the rows seen by the window functions
    are the group rows instead of the original table rows from
    <TT
CLASS="LITERAL"
>FROM</TT
>/<TT
CLASS="LITERAL"
>WHERE</TT
>.
   </P
><P
>    When multiple window functions are used, all the window functions having
    syntactically equivalent <TT
CLASS="LITERAL"
>PARTITION BY</TT
> and <TT
CLASS="LITERAL"
>ORDER BY</TT
>
    clauses in their window definitions are guaranteed to be evaluated in a
    single pass over the data. Therefore they will see the same sort ordering,
    even if the <TT
CLASS="LITERAL"
>ORDER BY</TT
> does not uniquely determine an ordering.
    However, no guarantees are made about the evaluation of functions having
    different <TT
CLASS="LITERAL"
>PARTITION BY</TT
> or <TT
CLASS="LITERAL"
>ORDER BY</TT
> specifications.
    (In such cases a sort step is typically required between the passes of
    window function evaluations, and the sort is not guaranteed to preserve
    ordering of rows that its <TT
CLASS="LITERAL"
>ORDER BY</TT
> sees as equivalent.)
   </P
><P
>    Currently, window functions always require presorted data, and so the
    query output will be ordered according to one or another of the window
    functions' <TT
CLASS="LITERAL"
>PARTITION BY</TT
>/<TT
CLASS="LITERAL"
>ORDER BY</TT
> clauses.
    It is not recommended to rely on this, however.  Use an explicit
    top-level <TT
CLASS="LITERAL"
>ORDER BY</TT
> clause if you want to be sure the
    results are sorted in a particular way.
   </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="queries-overview.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="queries-select-lists.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Overview</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="queries.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Select Lists</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>