queries.sgml

PostgreSQL7.4.6 for Linux

           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.
          </para>
         </listitem>
        </varlistentry>
       </variablelist>
       </para>
      </listitem>
     </varlistentry>
    </variablelist>

    <para>
     Joins of all types can be chained together or nested: either or
     both of <replaceable>T1</replaceable> and
     <replaceable>T2</replaceable> may be joined tables.  Parentheses
     may be used around <literal>JOIN</> clauses to control the join
     order.  In the absence of parentheses, <literal>JOIN</> clauses
     nest left-to-right.
    </para>

    <para>
     To put this together, assume we have tables <literal>t1</literal>
<programlisting>
 num | name
-----+------
   1 | a
   2 | b
   3 | c
</programlisting>
     and <literal>t2</literal>
<programlisting>
 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz
</programlisting>
     then we get the following results for the various joins:
<screen>
<prompt>=></> <userinput>SELECT * FROM t1 CROSS JOIN t2;</>
 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)

<prompt>=></> <userinput>SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;</>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

<prompt>=></> <userinput>SELECT * FROM t1 INNER JOIN t2 USING (num);</>
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

<prompt>=></> <userinput>SELECT * FROM t1 NATURAL INNER JOIN t2;</>
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

<prompt>=></> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;</>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

<prompt>=></> <userinput>SELECT * FROM t1 LEFT JOIN t2 USING (num);</>
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

<prompt>=></> <userinput>SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;</>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

<prompt>=></> <userinput>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)
</screen>
    </para>

    <para>
     The join condition specified with <literal>ON</> 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:
<screen>
<prompt>=></> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)
</screen>
    </para>
   </sect3>

   <sect3 id="queries-table-aliases">
    <title>Table and Column Aliases</title>

    <indexterm zone="queries-table-aliases">
     <primary>alias</primary>
     <secondary>in the FROM clause</secondary>
    </indexterm>

    <indexterm>
     <primary>label</primary>
     <see>alias</see>
    </indexterm>

    <para>
     A temporary name can be given to tables and complex table
     references to be used for references to the derived table in
     further processing.  This is called a <firstterm>table
     alias</firstterm>.
    </para>

    <para>
     To create a table alias, write
<synopsis>
FROM <replaceable>table_reference</replaceable> AS <replaceable>alias</replaceable>
</synopsis>
     or
<synopsis>
FROM <replaceable>table_reference</replaceable> <replaceable>alias</replaceable>
</synopsis>
     The <literal>AS</literal> key word is noise.
     <replaceable>alias</replaceable> can be any identifier.
    </para>

    <para>
     A typical application of table aliases is to assign short
     identifiers to long table names to keep the join clauses
     readable.  For example:
<programlisting>
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;
</programlisting>
    </para>

    <para>
     The alias becomes the new name of the table reference for the
     current query -- it is no longer possible to refer to the table
     by the original name.  Thus
<programlisting>
SELECT * FROM my_table AS m WHERE my_table.a > 5;
</programlisting>
     is not valid SQL syntax.  What will actually happen (this is a
     <productname>PostgreSQL</productname> extension to the standard)
     is that an implicit table reference is added to the
     <literal>FROM</literal> clause, so the query is processed as if
     it were written as
<programlisting>
SELECT * FROM my_table AS m, my_table AS my_table WHERE my_table.a > 5;
</programlisting>
     which will result in a cross join, which is usually not what you
     want.
    </para>

    <para>
     Table aliases are mainly for notational convenience, but it is
     necessary to use them when joining a table to itself, e.g.,
<programlisting>
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...
</programlisting>
     Additionally, an alias is required if the table reference is a
     subquery (see <xref linkend="queries-subqueries">).
    </para>

    <para>
     Parentheses are used to resolve ambiguities.  The following
     statement will assign the alias <literal>b</literal> to the
     result of the join, unlike the previous example:
<programlisting>
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...
</programlisting>
    </para>

    <para>
     Another form of table aliasing also gives temporary names to the columns of the table:
<synopsis>
FROM <replaceable>table_reference</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> ( <replaceable>column1</replaceable> <optional>, <replaceable>column2</replaceable> <optional>, ...</optional></optional> )
</synopsis>
     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.
    </para>

    <para>
     When an alias is applied to the output of a <literal>JOIN</>
     clause, using any of these forms, the alias hides the original
     names within the <literal>JOIN</>.  For example,
<programlisting>
SELECT a.* FROM my_table AS a JOIN your_table AS b ON ...
</programlisting>
     is valid SQL, but
<programlisting>
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c
</programlisting>
     is not valid: the table alias <literal>a</> is not visible
     outside the alias <literal>c</>.
    </para>
   </sect3>

   <sect3 id="queries-subqueries">
    <title>Subqueries</title>

    <indexterm zone="queries-subqueries">
     <primary>subquery</primary>
    </indexterm>

    <para>
     Subqueries specifying a derived table must be enclosed in
     parentheses and <emphasis>must</emphasis> be assigned a table
     alias name.  (See <xref linkend="queries-table-aliases">.)  For
     example:
<programlisting>
FROM (SELECT * FROM table1) AS alias_name
</programlisting>
    </para>

    <para>
     This example is equivalent to <literal>FROM table1 AS
     alias_name</literal>.  More interesting cases, which can't be
     reduced to a plain join, arise when the subquery involves
     grouping or aggregation.
    </para>
   </sect3>

   <sect3 id="queries-tablefunctions">
    <title>Table Functions</title>

    <indexterm zone="queries-tablefunctions"><primary>table function</></>

    <indexterm zone="queries-tablefunctions">
     <primary>function</>
     <secondary>in the FROM clause</>
    </indexterm>

    <para>
     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 <literal>FROM</> clause of a query. Columns returned by table
     functions may be included in <literal>SELECT</>,
     <literal>JOIN</>, or <literal>WHERE</> clauses in the same manner
     as a table, view, or subquery column.
    </para>

    <para>
     If a table function returns a base data type, the single result
     column is named like the function. If the function returns a
     composite type, the result columns get the same names as the
     individual attributes of the type.
    </para>

    <para>
     A table function may be aliased in the <literal>FROM</> clause,
     but it also may be left unaliased. If a function is used in the
     <literal>FROM</> clause with no alias, the function name is used
     as the resulting table name.
    </para>

    <para>
     Some examples:
<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;
</programlisting>
    </para>

    <para>
     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 <type>record</>.  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:
<programlisting>
SELECT *
    FROM dblink('dbname=mydb', 'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';
</programlisting>
     The <literal>dblink</> function executes a remote query (see
     <filename>contrib/dblink</>).  It is declared to return
     <type>record</> since it might be used for any kind of query.