表连接

在数据库设计中，数据通常被分散存储在多个相关表中，以避免数据冗余和维护问题。当我们需要从这些相关表中获取信息时，就需要使用表连接操作。本章将详细介绍 PostgreSQL 中的表连接操作。

表之间的连接

到目前为止，我们的查询一次只访问一个表。但实际业务场景中，我们通常需要同时从多个表中获取数据。查询可以同时访问多个表，或者以同时处理表的多个行的方式访问同一个表。一次访问多个表（或同一表的多个实例）的查询称为连接查询。

什么是表连接？

表连接是将一个表中的行与另一个表中的行基于某种关系（通常是它们共有的列）组合在一起的操作。连接操作通过一个表达式指定要配对的行。

INFO

表连接的本质是通过共同的字段将两个或多个表中的数据关联起来，从而可以从多个表中同时查询数据。

连接的类型

PostgreSQL 支持多种类型的连接操作：

1. 内连接（INNER JOIN）
2. 外连接（OUTER JOIN）
   • 左外连接（LEFT OUTER JOIN）
   • 右外连接（RIGHT OUTER JOIN）
   • 全外连接（FULL OUTER JOIN）
3. 交叉连接（CROSS JOIN）
4. 自然连接（NATURAL JOIN）
5. 自连接（Self JOIN）

下面我们将使用天气和城市数据的示例来逐一了解这些连接类型。

内连接（INNER JOIN）

内连接是最常见的连接类型，只返回两个表中满足连接条件的行。

语法

SELECT * FROM table1 JOIN table2 ON table1.column = table2.column;

或使用更传统的语法：

SELECT * FROM table1, table2 WHERE table1.column = table2.column;

实际示例

假设我们有两个表：weather（天气）和cities（城市），我们想要返回所有天气记录以及相关城市的位置：

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)

注意结果集中有两个重要特点：

1. Hayward 城市没有出现在结果中，因为cities表中没有与之匹配的条目
2. 结果中有两列包含城市名称（city和name），这是因为我们使用了*通配符

为了避免列名重复的问题，我们可以明确指定要显示的列：

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;

在编写连接查询时，养成限定所有列名的好习惯，这样如果将来表结构变化，查询不会因为列名冲突而失败。

外连接（OUTER JOIN）

与内连接不同，外连接可以返回一侧表中没有匹配的行。

左外连接（LEFT OUTER JOIN）

左外连接返回左表中的所有行，即使右表中没有匹配的行。如果没有匹配的行，右表的列将填充 NULL 值。

语法

SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;

实际示例

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)

INFO

注意现在 Hayward 记录也出现在结果中，但其对应的城市信息（name 和 location）为 NULL。

右外连接（RIGHT OUTER JOIN）

右外连接与左外连接相反，它返回右表中的所有行，即使左表中没有匹配的行。

语法

SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;

全外连接（FULL OUTER JOIN）

全外连接返回左表和右表中的所有行，无论是否有匹配的行。如果没有匹配的行，对应表的列将填充 NULL 值。

语法

SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;

自连接（Self JOIN）

自连接是将一个表与自身连接的操作。这在处理层次结构数据或比较同一表中不同行时非常有用。

语法与别名使用

在自连接中，必须为表指定不同的别名，以便区分同一表的不同实例：

SELECT * FROM table1 t1 JOIN table1 t2 ON t1.column = t2.column;

实际示例

假设我们想找出所有温度范围在其他天气记录范围内的天气记录：

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)

使用别名简化查询

可以使用别名来简化查询，特别是在表名很长或需要多次引用同一个表的情况下：

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

TIP

使用有意义的别名不仅可以减少输入工作量，还可以提高查询的可读性。

连接优化

从概念上讲，连接操作比较一个表中的每一行与另一个表中的每一行。但在实际执行中，PostgreSQL 的查询优化器会使用更高效的算法（如哈希连接或合并连接）来减少需要比较的行数。

实际业务场景应用

场景 1：订单和客户信息查询

在电子商务系统中，订单表和客户表通常是分开存储的。当需要查看订单及其对应的客户信息时：

SELECT o.order_id, o.order_date, o.total_amount,
       c.customer_name, c.email, c.phone
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2023-01-01';

场景 2：员工和部门层次结构

对于展示公司组织架构：

SELECT e.employee_name, m.employee_name AS manager_name,
       d.department_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id
JOIN departments d ON e.department_id = d.department_id
ORDER BY d.department_name, m.employee_name, e.employee_name;

场景 3：产品销售分析

分析不同类别产品的销售情况：

SELECT c.category_name, p.product_name,
       SUM(od.quantity) as total_sold,
       SUM(od.quantity * od.unit_price) as revenue
FROM order_details od
JOIN products p ON od.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_name, p.product_name
ORDER BY revenue DESC;

练习题

1. 创建一个左外连接查询，显示所有部门及其员工，即使某些部门没有员工。
2. 编写一个查询，找出所有没有下过订单的客户。
3. 使用自连接，找出同一个城市中的所有客户对。

答案

1. 显示所有部门及其员工：

SELECT d.department_name, e.employee_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
ORDER BY d.department_name, e.employee_name;

2. 找出没有下过订单的客户：

SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

3. 同一个城市的所有客户对：

SELECT c1.customer_name AS customer1, c2.customer_name AS customer2,
       c1.city
FROM customers c1
JOIN customers c2 ON c1.city = c2.city AND c1.customer_id < c2.customer_id
ORDER BY c1.city, customer1, customer2;

小结

表连接是 SQL 中最强大的功能之一，它允许我们从多个相关表中检索数据。掌握不同类型的连接及其适用场景，对于有效地查询关系型数据库至关重要。在实际应用中，选择正确的连接类型和优化连接操作可以极大地提高查询性能。

连接操作在概念上是逐行比较的，但 PostgreSQL 在实际执行时通常使用更高效的算法。从用户的角度看，您只需确保连接条件正确，查询优化器会负责选择最高效的执行方式。