Convert HTML docs to more streamlined TXT

[ai-pg] / full-docs / txt / tutorial-join.txt

2.6. Joins Between Tables #

   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 join 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 city column of each row of the weather table with
   the name column of all rows in the cities table, and select the pairs
   of rows where these values match.^[4] This would be accomplished by the
   following query:
SELECT * FROM weather JOIN cities ON city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | locatio
n
---------------+---------+---------+------+------------+---------------+--------
---
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,5
3)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,5
3)
(2 rows)

   Observe two things about the result set:
     * There is no result row for the city of Hayward. This is because
       there is no matching entry in the cities table for Hayward, so the
       join ignores the unmatched rows in the weather table. We will see
       shortly how this can be fixed.
     * There are two columns containing the city name. This is correct
       because the lists of columns from the weather and cities tables are
       concatenated. In practice this is undesirable, though, so you will
       probably want to list the output columns explicitly rather than
       using *:
SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather JOIN cities ON city = name;

   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 qualify the column names to show which
   one you meant, as in:
SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather JOIN cities ON weather.city = cities.name;

   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.

   Join queries of the kind seen thus far can also be written in this
   form:
SELECT *
    FROM weather, cities
    WHERE city = name;

   This syntax pre-dates the JOIN/ON syntax, which was introduced in
   SQL-92. The tables are simply listed in the FROM clause, and the
   comparison expression is added to the WHERE clause. The results from
   this older implicit syntax and the newer explicit JOIN/ON 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 WHERE
   clause together with other conditions.

   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 weather table and for each row
   to find the matching cities row(s). If no matching row is found we want
   some “empty values” to be substituted for the cities table's columns.
   This kind of query is called an outer join. (The joins we have seen so
   far are inner joins.) The command looks like this:
SELECT *
    FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | locatio
n
---------------+---------+---------+------+------------+---------------+--------
---
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,5
3)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,5
3)
(3 rows)

   This query is called a left outer join 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.

   Exercise:  There are also right outer joins and full outer joins. Try
   to find out what those do.

   We can also join a table against itself. This is called a self join. 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 temp_lo and temp_hi columns of each weather row to the temp_lo and
   temp_hi columns of all other weather rows. We can do this with the
   following query:
SELECT w1.city, w1.temp_lo AS low, w1.temp_hi AS high,
       w2.city, w2.temp_lo AS low, w2.temp_hi AS high
    FROM weather w1 JOIN weather w2
        ON w1.temp_lo < w2.temp_lo AND w1.temp_hi > w2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

   Here we have relabeled the weather table as w1 and w2 to be able to
   distinguish the left and right side of the join. You can also use these
   kinds of aliases in other queries to save some typing, e.g.:
SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

   You will encounter this style of abbreviating quite frequently.

   ^[4] 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.