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 tables at one time is called a join query. For example, say you wish to list all the weather records together with the location of the associated city. To do that, you 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. 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 of the weather and the cities table 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 find out the semantics of this query when the WHERE clause is omitted.
Since the columns all had different names, the parser automatically found out which table they belong to, but it is good style to fully qualify column names in join queries:
SELECT weather.city, weather.temp_lo, weather.temp_hi, weather.prcp, weather.date, cities.location FROM weather, cities WHERE cities.name = weather.city; |
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); |
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, find the matching cities row. 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) |
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. 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) |
SELECT * FROM weather w, cities c WHERE w.city = c.name; |