7.合并数据

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. A query that accesses multiple rows of the same or different
tables at one time is called a join query. As an example, say you wish
to list all the weather records together with the location of the
associated city. To do that, we need 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.

Note

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.

This would be accomplished by the following query:

SELECT *

FROM weather, cities

WHERE city = name;

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)

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, cities

• WHERE city = name;

**Exercise: ** Attempt to determine the semantics of this query when
the WHERE clause is omitted.

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, cities

WHERE cities.name = weather.city;

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
alternative form:

SELECT *

FROM weather INNER JOIN cities ON (weather.city = cities.name);

This syntax is not as commonly used as the one above, but we show it
here to help you understand the following topics.

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 | 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)

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, weather W2

WHERE 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, cities c

WHERE w.city = c.name;

You will encounter this style of abbreviating quite frequently.

Like most other relational database
products, PostgreSQL supports aggregate functions. An aggregate
function computes a single result from multiple input rows. For example,
there are aggregates to compute
the count, sum, avg (average), max (maximum) and min (minimum) over a
set of rows.

As an example, we can find the highest low-temperature reading anywhere
with:

SELECT max(temp_lo) FROM weather;

max

---

46

(1 row)

If we wanted to know what city (or cities) that reading occurred in, we
might try:

SELECT city FROM weather WHERE temp_lo = max(temp_lo); WRONG

but this will not work since the aggregate max cannot be used in
the WHERE clause. (This restriction exists because the WHERE clause
determines which rows will be included in the aggregate calculation; so
obviously it has to be evaluated before aggregate functions are
computed.) However, as is often the case the query can be restated to
accomplish the desired result, here by using a subquery:

SELECT city FROM weather

WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

city

---

San Francisco

(1 row)

This is OK because the subquery is an independent computation that
computes its own aggregate separately from what is happening in the
outer query.

Aggregates are also very useful in combination with GROUP BY clauses.
For example, we can get the maximum low temperature observed in each
city with:

SELECT city, max(temp_lo)

FROM weather

GROUP BY city;

city | max

—————+—–

Hayward | 37

San Francisco | 46

(2 rows)

which gives us one output row per city. Each aggregate result is
computed over the table rows matching that city. We can filter these
grouped rows using HAVING:

SELECT city, max(temp_lo)

FROM weather

GROUP BY city

HAVING max(temp_lo) < 40;

city | max

———+—–

Hayward | 37

(1 row)

which gives us the same results for only the cities that have
all temp_lo values below 40. Finally, if we only care about cities whose
names begin with “S”, we might do:

SELECT city, max(temp_lo)

FROM weather

WHERE city LIKE ‘S%’ – (1)

GROUP BY city

HAVING max(temp_lo) < 40;

---

[(1)]{.ul} The LIKE operator does pattern matching and is explained
in [Section 9.7]{.ul}.

---

---

: Callout list

It is important to understand the interaction between aggregates
and SQL’s WHERE and HAVING clauses. The fundamental difference
between WHERE and HAVING is this: WHERE selects input rows before groups
and aggregates are computed (thus, it controls which rows go into the
aggregate computation), whereas HAVING selects group rows after groups
and aggregates are computed. Thus, the WHERE clause must not contain
aggregate functions; it makes no sense to try to use an aggregate to
determine which rows will be inputs to the aggregates. On the other
hand, the HAVING clause always contains aggregate functions. (Strictly
speaking, you are allowed to write a HAVING clause that doesn’t use
aggregates, but it’s seldom useful. The same condition could be used
more efficiently at the WHERE stage.)

In the previous example, we can apply the city name restriction
in WHERE, since it needs no aggregate. This is more efficient than
adding the restriction to HAVING, because we avoid doing the grouping
and aggregate calculations for all rows that fail the WHERE check.