2.6. 表之间的联接

到目前为止，我们的查询一次只能访问一个表。查询可以一次访问多个表，也可以以同时处理表的多行的方式访问同一表。一次访问相同或不同表的多行的查询称为* join *查询。例如，假设您希望列出所有天气记录以及相关城市的位置。为此，我们需要将weather表中每一行的city列与cities表中所有行的name列进行比较，并选择这些值匹配的行对。

这可以通过以下查询完成：

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)

观察关于结果集的两件事：

• 海沃德市没有结果行。这是因为cities表中没有适用于 Hayward 的匹配条目，因此联接将忽略weather表中不匹配的行。我们很快将看到如何解决此问题。

• 有两列包含城市名称。这是正确的，因为weather和cities表中的列的列表是串联的。但是，在实践中这是不希望的，因此您可能希望显式列出输出列，而不是使用*：

SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather, cities
    WHERE city = name;

练习： 省略WHERE子句时，尝试确定此查询的语义。

由于这些列的名称均不同，因此解析器会自动找到它们所属的表。如果两个表中的列名重复，则需要对列名进行“限定”以显示您的意思，例如：

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

在联接查询中限定所有列名称是一种公认的好样式，因此，如果以后将重复的列名称添加到一个表中，则查询不会失败。

到目前为止看到的那种联接查询也可以用这种替代形式来写：

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

这种语法不像上面那样常用，但是我们在这里显示它来帮助您理解以下主题。

现在，我们将弄清楚如何重新获取 Hayward 记录。我们希望查询执行的操作是扫描weather表，并针对每一行查找匹配的cities行。如果找不到匹配的行，我们希望用“空值”代替cities表的列。这种查询称为外部联接。 (到目前为止，我们已经看到的联接是内部联接.)命令如下所示：

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)

该查询称为* left external join *，因为在 join 运算符左侧提到的表将在输出中至少包含其每一行，而在右侧的表将仅具有与某行匹配的那些行。左表。当输出没有右表匹配项的左表行时，将空(空)值替换为右表列。

锻炼： 还有正确的外部联接和完全外部联接。尝试找出那些在做什么。

我们还可以针对自己加入表格。这称为* self join *。例如，假设我们希望找到其他天气记录的温度范围内的所有天气记录。因此，我们需要将每个weather行的temp_lo和temp_hi列与所有其他weather行的temp_lo和temp_hi列进行比较。我们可以使用以下查询来做到这一点：

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)

在这里，我们将天气表重新标记为W1和W2，以便能够区分联接的左侧和右侧。您还可以在其他查询中使用这些别名来保存一些 Importing 内容，例如：

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

您会经常遇到这种缩写形式。