<title>Joins Between Tables</title>

   <indexterm zone="tutorial-join">
    <primary>join</primary>
   </indexterm>

   <para>
    Thus far, our queries have only accessed one table at a time.
    Queries can access multiple tables at once, or access the same
    table in such a way that multiple rows of the table are being
    processed at the same time.  Queries that access multiple tables
    (or multiple instances of the same table) at one time are called
    <firstterm>join</firstterm> queries.  They combine rows from one table
    with rows from a second table, with an expression specifying which rows
    are to be paired.  For example, to return all the weather records together
    with the location of the associated city, the database needs to compare
    the <structfield>city</structfield>
    column of each row of the <structname>weather</structname> table with the
    <structfield>name</structfield> column of all rows in the <structname>cities</structname>
    table, and select the pairs of rows where these values match.<footnote>
     <para>
      This  is only a conceptual model.  The join is usually performed
      in a more efficient manner than actually comparing each possible
      pair of rows, but this is invisible to the user.
     </para>
    </footnote>
    This would be accomplished by the following query:

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

<screen>
     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 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)
(2 rows)
</screen>

   </para>

   <para>
    Observe two things about the result set:
    <itemizedlist>
     <listitem>
      <para>
       There is no result row for the city of Hayward.  This is
       because there is no matching entry in the
       <structname>cities</structname> table for Hayward, so the join
       ignores the unmatched rows in the <structname>weather</structname> table.  We will see
       shortly how this can be fixed.
      </para>
     </listitem>

     <listitem>
      <para>
       There are two columns containing the city name.  This is
       correct because the lists of columns from the
       <structname>weather</structname> and
       <structname>cities</structname> tables are concatenated.  In
       practice this is undesirable, though, so you will probably want
       to list the output columns explicitly rather than using
       <literal>*</literal>:
<programlisting>
SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather JOIN cities ON city = name;
</programlisting>
      </para>
     </listitem>
    </itemizedlist>
   </para>

   <para>
    Since the columns all had different names, the parser
    automatically found which table they belong to.  If there
    were 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>