2.6. 表之间的联接#

到目前为止，我们的查询一次只访问一个表。查询可以一次访问多个表，或以这样的方式访问同一个表，即同时处理该表的多个行。一次访问多个表（或同一个表的多个实例）的查询称为联接查询。它们将一个表中的行与第二个表中的行组合在一起，并通过一个表达式指定要配对哪些行。例如，要返回所有天气记录以及相关城市的位置，数据库需要将city``weather表中每行的列与cities表中所有行的name列进行比较，并选择这些值匹配的行对。[4]以下查询将完成此操作

SELECT * FROM weather JOIN cities ON 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表中没有海沃德市的匹配条目，因此联接忽略了weather表中不匹配的行。我们很快就会看到如何解决此问题。
有两个列包含城市名称。这是正确的，因为来自 weather 和 cities 表的列列表被连接在一起。但在实践中这是不可取的，因此你可能希望显式列出输出列，而不是使用 *
```
SELECT city, temp_lo, temp_hi, prcp, date, location
    FROM weather JOIN cities ON city = name;
```

由于所有列都有不同的名称，因此解析器会自动找到它们所属的表。如果两个表中有重复的列名称，则需要限定列名称以显示你的意思是哪一个，如下所示

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

在连接查询中限定所有列名称被广泛认为是一种良好的风格，这样如果稍后向其中一个表添加了重复的列名称，查询就不会失败。

到目前为止看到的这种连接查询也可以用这种形式编写

SELECT *
    FROM weather, cities
    WHERE city = name;

此语法早于 SQL-92 中引入的JOIN/ON语法。这些表只是在FROM子句中列出，并将比较表达式添加到WHERE子句中。此较旧的隐式语法和较新的显式JOIN/ON语法产生的结果是相同的。但是对于查询的读者来说，显式语法使其含义更容易理解：连接条件由其自己的关键字引入，而以前该条件与其他条件一起混合到WHERE子句中。

现在，我们将弄清楚如何将 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)

此查询被称为左外联接，因为联接运算符左侧的表将至少一次在其输出中拥有每一行，而右侧的表将仅输出与左侧表中的某一行匹配的行。当输出没有右表匹配项的左表行时，将用空（null）值替换右表列。

**练习：**还有右外联接和全外联接。尝试找出它们的作用。

我们还可以将一个表与自身联接。这称为自联接。例如，假设我们希望找到所有温度范围在其他天气记录中的天气记录。因此，我们需要将每个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 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)

在此，我们将 weather 表重新标记为w1和w2，以便区分联接的左侧和右侧。您还可以在其他查询中使用此类别名来节省一些键入，例如

SELECT *
    FROM weather w JOIN cities c ON w.city = c.name;

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

[4]这仅是一个概念模型。联接通常以比实际比较每一对可能的行更有效的方式执行，但这对用户是不可见的。

2.6. 表之间的联接# ​

2.6. 表之间的联接#