表之间的连接

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

bash

SELECT * FROM weather JOIN cities ON city = name;

bash

     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是连接的。但在实践中，这是不可取的，因此您可能希望明确列出输出列，而不是使用*：

bash

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

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

bash

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

人们普遍认为，在连接查询中限定所有列名是一种好的做法，这样，如果稍后在其中一个表中添加重复的列名，查询也不会失败。

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

bash

SELECT *
    FROM weather, cities
    WHERE city = name;

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

现在，我们将弄清楚如何重新获取 Hayward 记录。我们希望查询扫描表weather并针对每一行查找匹配的cities行。如果未找到匹配的行，我们希望用一些“空值”替换cities表的列。这种查询称为外连接。（到目前为止，我们看到的连接都是内连接。）命令如下所示：

bash

SELECT * 
    FROM weather LEFT OUTER JOIN cities ON weather.city = cities.name;

bash

     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) 值替换右表列。

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

我们还可以将一个表与其自身连接起来。这称为自连接。例如，假设我们希望找到所有处于其他天气记录温度范围内的天气记录。因此，我们需要将每行的temp_lo和列与所有其他行的和列进行比较。我们可以使用以下查询来执行此操作：temp_hi``weather``temp_lo``temp_hi``weather

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;

bash

     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以便能够区分连接的左侧和右侧。您还可以在其他查询中使用这些类型的别名来节省一些输入，例如：

bash

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

您会经常遇到这种缩写风格。

表之间的连接 ​

表之间的连接