duplicate column names in the two tables you'd need to
    <firstterm>qualify</firstterm> the column names to show which one you
    meant, as in:

<programlisting>
SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;
</programlisting>

    It is widely considered good style to qualify all column names
    in a join query, so that the query won't fail if a duplicate
    column name is later added to one of the tables.
   </para>

   <para>
    Join queries of the kind seen thus far can also be written in this
    form:

<programlisting>
SELECT *
    FROM weather, cities
    WHERE city = name;
</programlisting>

    This syntax pre-dates the <literal>JOIN</literal>/<literal>ON</literal>
    syntax, which was introduced in SQL-92.  The tables are simply listed in
    the <literal>FROM</literal> clause, and the comparison expression is added
    to the <literal>WHERE</literal> clause.  The results from this older
    implicit syntax and the newer explicit
    <literal>JOIN</literal>/<literal>ON</literal> syntax are identical.  But
    for a reader of the query, the explicit syntax makes its meaning easier to
    understand: The join condition is introduced by its own key word whereas
    previously the condition was mixed into the <literal>WHERE</literal>
    clause together with other conditions.
   </para>

   <indexterm><primary>join</primary><secondary>outer</secondary></indexterm>

   <para>
    Now we will figure out how we can get the Hayward records back in.
    What we want the query to do is to scan the
    <structname>weather</structname> table and for each row to find the
    matching <structname>cities</structname> row(s).  If no matching row is
    found we want some <quote>empty values</quote> to be substituted
    for the <structname>cities</structname> table's columns.  This kind
    of query is called an <firstterm>outer join</firstterm>.  (The
    joins we have seen so far are <firstterm>inner joins</firstterm>.)
    The command looks like this:

<programlisting>
SELECT *
    FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;
</programlisting>

<screen>
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)
</screen>

    This query is called a <firstterm>left outer
    join</firstterm> because the table mentioned on the left of the
    join operator will have each of its rows in the output at least
    once, whereas the table on the right will only have those rows
    output that match some row of the left table.  When outputting a
    left-table row for which there is no right-table match, empty (null)
    values are substituted for the right-table columns.
   </para>

   <formalpara>
    <title>Exercise:</title>

    <para>
     There are also right outer joins and full outer joins.  Try to
     find out what those do.
    </para>
   </formalpara>

   <indexterm><primary>join</primary><secondary>self</secondary></indexterm>
   <indexterm><primary>alias</primary><secondary>for table name in query</secondary></indexterm>
   <para>
    We can also join a table against itself.  This is called a
    <firstterm>self join</firstterm>.  As an example, suppose we wish
    to find all the weather records that are in the temperature range
    of other weather records.  So we need to compare the
    <structfield>temp_lo</structfield> and <structfield>temp_hi</structfield> columns of
    each <structname>weather</structname> row to the
    <structfield>temp_lo</structfield> and
    <structfield>temp_hi</structfield> columns of all other
    <structname>weather</structname>