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

<prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;</userinput>
 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</literal> 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>=&gt;</prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';</userinput>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)
</screen>
     Notice that placing the restriction in the <literal>WHERE</literal> clause
     produces a different result:
<screen>
<prompt>=&gt;</prompt> <userinput>SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';</userinput>
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)
</screen>
     This is because a restriction placed in the <literal>ON</literal>
     clause is processed <emphasis>before</emphasis> the join, while
     a restriction placed in the <literal>WHERE</literal> clause is processed
     <emphasis>after</emphasis> the join.
     That does not matter with inner joins, but it matters a lot with outer
     joins.
    </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
     the rest of the query.  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 optional 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 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:
<programlisting>
SELECT * FROM my_table AS m WHERE my_table.a &gt; 5;    -- wrong
</programlisting>
    </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 people AS mother JOIN people AS child ON mother.id = child.mother_id;
</programlisting>
    </para>

    <para>
     Parentheses are used to resolve ambiguities.  In the following example,
     the first statement assigns the alias <literal>b</literal> to the second
     instance of <literal>my_table</literal>, but the second statement assigns the
     alias to the result of the join:
<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 ...
</programlisting>