数据库多表连接

数据库多表连接

小象实战讲义 · Python数据分析实战

在实际的数据分析工作中，数据往往分散在多个相关联的数据库表中。例如，员工的基本信息在一个表，其所属部门信息在另一个表。为了进行完整的分析，我们需要将这些表按照某种关系“连接”起来，形成一个更全面的数据集。本节将学习如何使用 SQL 的 JOIN 操作，实现数据库的多表连接，这是从关系型数据库中提取和整合数据的关键技能。

💡 核心导读

• 连接（JOIN）的本质：基于一个或多个共同列（键），将两个或多个表中的行组合起来。
• 连接的主要类型：交叉连接（CROSS JOIN）、内连接（INNER JOIN）和外连接（OUTER JOIN，包括 LEFT JOIN 和 RIGHT JOIN）。
• 连接的选择策略：根据分析需求决定使用哪种连接，例如，需要所有员工信息（无论是否有部门）时使用左连接。
• SQLite 的特别说明：SQLite 早期版本（< 3.39.0）不支持右连接，从 3.39.0（2022 年发布）起已原生支持 RIGHT JOIN；旧版本可通过交换表顺序用左连接实现相同效果。

为什么需要多表连接？

数据库设计时，为了减少数据冗余和保持一致性，通常会遵循“规范化”原则，将数据拆分到不同的表中。例如，一家公司的数据可能被设计成：

• employee 表：存储员工ID、姓名、年龄、地址、薪资等个人信息。
• department 表：存储部门ID、部门名称以及对应的员工ID。

当我们需要回答“财务部有哪些员工，他们的薪资是多少？”这类问题时，就必须将 employee 表和 department 表的信息合并起来。这个合并操作，就是通过多表连接完成的，连接的条件通常是两个表中意义相同的列，比如 employee.id 和 department.emp_id。

连接的类型与机制

SQL 提供了多种连接方式，核心区别在于如何处理两个表中无法匹配的行。

交叉连接 (CROSS JOIN)

交叉连接返回两个表的笛卡尔积。即左表的每一行都与右表的每一行进行组合。如果左表有 M 行，右表有 N 行，结果将包含 M × N 行。这种连接通常实用价值有限，但有助于理解连接的基础概念。

表A (3行)        表B (2行)              CROSS JOIN 结果 (3x2=6行)
+----+          +----+               +----+----+
| id |          | id |               | A.id | B.id |
+----+          +----+               +------+------+
|  1 |          |  x |               |   1  |   x  |
|  2 |          |  y |               |   1  |   y  |
|  3 |          +----+               |   2  |   x  |
+----+                               |   2  |   y  |
                                     |   3  |   x  |
                                     |   3  |   y  |
                                     +------+------+

内连接 (INNER JOIN)

内连接返回两个表中连接键值匹配的行，即只返回两个表的交集部分。这是最常用的一种连接方式。

表A                    表B                    INNER JOIN ON A.id = B.a_id
+----+------+          +----+------+          +----+------+----+------+
| id | name |          | id | a_id | dept |   | A.id | name | B.id | dept |
+----+------+          +----+------+------+   +------+------+------+------+
|  1 | Alice|          | 101|   1  |  IT  |   |   1  | Alice|  101 |  IT  |
|  2 | Bob  |          | 102|   4  |  HR  |   |   3  | Carol|  103 | Sales|
|  3 | Carol|          | 103|   3  | Sales|   +------+------+------+------+
+----+------+          +----+------+------+

（只有 id 为 1 和 3 的员工在 department 表中有对应记录）

外连接 (OUTER JOIN)

外连接会保留一个表中的所有记录，并用 NULL 填充另一个表中无法匹配的部分。它主要分为两种：

• 左连接 (LEFT JOIN / LEFT OUTER JOIN)：返回左表的全部记录，以及右表中匹配的记录。右表无匹配则填充 NULL。
• 右连接 (RIGHT JOIN / RIGHT OUTER JOIN)：返回右表的全部记录，以及左表中匹配的记录。左表无匹配则填充 NULL。

# 左连接 (LEFT JOIN) 结果示例
+------+------+------+------+
| A.id | name | B.id | dept |
+------+------+------+------+
|   1  | Alice|  101 |  IT  |
|   2  | Bob  | NULL | NULL |  <- 左表有，右表无匹配
|   3  | Carol|  103 | Sales|
+------+------+------+------+

# 右连接 (RIGHT JOIN) 结果示例
+------+------+------+------+
| A.id | name | B.id | dept |
+------+------+------+------+
|   1  | Alice|  101 |  IT  |
| NULL | NULL |  102 |  HR  |  <- 右表有，左表无匹配
|   3  | Carol|  103 | Sales|
+------+------+------+------+

版本提示：早期 SQLite（< 3.39.0）不支持 RIGHT JOIN（也不支持 FULL OUTER JOIN），当时的惯用替代是交换左右表顺序改写为 LEFT JOIN——因为 A RIGHT JOIN B 等价于 B LEFT JOIN A。SQLite 自 3.39.0（2022 年发布）起已原生支持这两者，现在 Python 自带的 sqlite3 模块通常已包含该能力（可用 sqlite3.sqlite_version 查看版本）。

在 Python 中实践 SQL 多表连接

我们将使用 sqlite3 库，并在内存中创建示例数据来演示各种连接操作。每个代码块都是完全独立、可运行的。

首先，建立数据库连接并创建示例表和数据。

import sqlite3

# 在内存中创建数据库连接
conn = sqlite3.connect(':memory:')
cur = conn.cursor()

# 创建员工表 (employee)
cur.execute('''
CREATE TABLE employee (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    address TEXT,
    salary REAL
);
''')

# 创建部门表 (department)
cur.execute('''
CREATE TABLE department (
    id INTEGER PRIMARY KEY,
    dept TEXT,
    emp_id INTEGER
);
''')

# 插入员工数据
employees = [
    (1, 'Paul', 32, 'California', 20000.0),
    (2, 'Allen', 25, 'Texas', 15000.0),
    (3, 'Teddy', 23, 'Norway', 20000.0),
    (4, 'Mark', 25, 'Rich-Mond', 65000.0),
    (5, 'David', 27, 'Texas', 85000.0),
    (6, 'Kim', 22, 'South-Hall', 45000.0),
    (7, 'James', 24, 'Houston', 10000.0)
]
cur.executemany('INSERT INTO employee VALUES (?,?,?,?,?)', employees)

# 插入部门数据
departments = [
    (1, 'IT Billing', 1),
    (2, 'Engineering', 2),
    (3, 'Finance', 7)
]
cur.executemany('INSERT INTO department VALUES (?,?,?)', departments)

conn.commit()
print("数据库及示例数据创建完成。")

数据库及示例数据创建完成。

1. 交叉连接 (CROSS JOIN)

让我们看看两个表进行交叉连接的结果。employee 表有7条记录，department 表有3条记录，结果应为 7 × 3 = 21 条记录。

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
# 重建表结构（为保持代码块独立，此处简化，实际使用同上一个块的数据）
cur.execute('CREATE TABLE employee (id INT, name TEXT);')
cur.execute('CREATE TABLE department (id INT, dept TEXT, emp_id INT);')
cur.executemany('INSERT INTO employee VALUES (?,?)', [(1,'Paul'),(2,'Allen'),(3,'Teddy'),(4,'Mark'),(5,'David'),(6,'Kim'),(7,'James')])
cur.executemany('INSERT INTO department VALUES (?,?,?)', [(1,'IT Billing',1), (2,'Engineering',2), (3,'Finance',7)])
conn.commit()

result = cur.execute('''
SELECT employee.name, department.dept, department.emp_id
FROM employee CROSS JOIN department
ORDER BY employee.id, department.id;
''').fetchall()

print("交叉连接 (CROSS JOIN) 结果 (共{}条):".format(len(result)))
for i, row in enumerate(result[:6], 1): # 只显示前6条
    print(f"{i:2d}. {row}")
print("... (其余记录省略)")
conn.close()

交叉连接 (CROSS JOIN) 结果 (共21条):
 1. ('Paul', 'IT Billing', 1)
 2. ('Paul', 'Engineering', 2)
 3. ('Paul', 'Finance', 7)
 4. ('Allen', 'IT Billing', 1)
 5. ('Allen', 'Engineering', 2)
 6. ('Allen', 'Finance', 7)
... (其余记录省略)

2. 内连接 (INNER JOIN)

现在我们进行内连接，只获取那些在 department 表中有明确部门分配的员工信息。连接条件是 employee.id = department.emp_id。

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE employee (id INT, name TEXT);')
cur.execute('CREATE TABLE department (id INT, dept TEXT, emp_id INT);')
cur.executemany('INSERT INTO employee VALUES (?,?)', [(1,'Paul'),(2,'Allen'),(3,'Teddy'),(4,'Mark'),(5,'David'),(6,'Kim'),(7,'James')])
cur.executemany('INSERT INTO department VALUES (?,?,?)', [(1,'IT Billing',1), (2,'Engineering',2), (3,'Finance',7)])
conn.commit()

result = cur.execute('''
SELECT department.emp_id, employee.name, department.dept
FROM employee INNER JOIN department
ON employee.id = department.emp_id;
''').fetchall()

print("内连接 (INNER JOIN) 结果：")
for row in result:
    print(row)
conn.close()

内连接 (INNER JOIN) 结果：
(1, 'Paul', 'IT Billing')
(2, 'Allen', 'Engineering')
(7, 'James', 'Finance')

结果只有三条记录，对应 employee 表中 ID 为 1、2、7 的员工，这正是两个表在 emp_id/id 列上的交集。

3. 左连接 (LEFT JOIN)

左连接以 employee 表为基准，返回所有员工的信息。对于有部门的员工，显示部门信息；对于没有部门的员工，其部门信息显示为 NULL。

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE employee (id INT, name TEXT);')
cur.execute('CREATE TABLE department (id INT, dept TEXT, emp_id INT);')
cur.executemany('INSERT INTO employee VALUES (?,?)', [(1,'Paul'),(2,'Allen'),(3,'Teddy'),(4,'Mark'),(5,'David'),(6,'Kim'),(7,'James')])
cur.executemany('INSERT INTO department VALUES (?,?,?)', [(1,'IT Billing',1), (2,'Engineering',2), (3,'Finance',7)])
conn.commit()

result = cur.execute('''
SELECT department.emp_id, employee.name, department.dept
FROM employee LEFT OUTER JOIN department
ON employee.id = department.emp_id;
''').fetchall()

print("左连接 (LEFT JOIN) 结果：")
for row in result:
    # 处理 dept 为 None 的情况，显示为 'NULL'
    formatted_row = (row[0], row[1], row[2] if row[2] is not None else 'NULL')
    print(formatted_row)
conn.close()

左连接 (LEFT JOIN) 结果：
(1, 'Paul', 'IT Billing')
(2, 'Allen', 'Engineering')
(None, 'Teddy', 'NULL')
(None, 'Mark', 'NULL')
(None, 'David', 'NULL')
(None, 'Kim', 'NULL')
(7, 'James', 'Finance')

可以看到，所有7名员工都出现在结果中。ID为3、4、5、6的员工在 department 表中没有对应记录，所以他们的 emp_id 和 dept 字段都是 NULL。

4. 右连接 (RIGHT JOIN)

右连接返回右表的全部记录，以及左表中匹配的记录；左表无匹配时填充 NULL。SQLite 自 3.39.0 起原生支持 RIGHT JOIN。为了演示”右表记录在左表无匹配”时的 NULL 填充效果，我们在 department 表中额外新增一条没有对应员工的部门记录（emp_id=9）：

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE employee (id INT, name TEXT);')
cur.execute('CREATE TABLE department (id INT, dept TEXT, emp_id INT);')
cur.executemany('INSERT INTO employee VALUES (?,?)', [(1,'Paul'),(2,'Allen'),(3,'Teddy'),(4,'Mark'),(5,'David'),(6,'Kim'),(7,'James')])
cur.executemany('INSERT INTO department VALUES (?,?,?)', [(1,'IT Billing',1), (2,'Engineering',2), (3,'Finance',7), (4,'Marketing',9)])
conn.commit()

result = cur.execute('''
SELECT department.emp_id, employee.name, department.dept
FROM employee RIGHT OUTER JOIN department
ON employee.id = department.emp_id;
''').fetchall()

print("右连接 (RIGHT JOIN) 结果：")
for row in result:
    # 处理 name 为 None 的情况
    formatted_row = (row[0], row[1] if row[1] is not None else 'NULL', row[2])
    print(formatted_row)
conn.close()

右连接 (RIGHT JOIN) 结果：
(1, 'Paul', 'IT Billing')
(2, 'Allen', 'Engineering')
(7, 'James', 'Finance')
(9, 'NULL', 'Marketing')

结果包含 department 表的全部 4 条记录：前三条都能匹配到员工姓名；而 Marketing 部门（emp_id=9）在 employee 表中没有对应员工，name 字段被填充为 NULL——这正是右连接”保证右表完整”的体现。

等价技巧：employee RIGHT JOIN department 总可以改写为 department LEFT JOIN employee（交换左右表顺序的左连接）。如果你使用的 SQLite 版本低于 3.39.0、直接写 RIGHT JOIN 会报语法错误，用这一招即可实现完全相同的效果。

📝 动手练一练

1. 概念辨析：假设你有 orders（订单表）和 customers（客户表），你想分析“所有客户的下单情况，包括那些从未下过单的客户”。你应该使用哪种连接？为什么？

2. 动手实践：基于上面创建的示例 employee 和 department 表，请写出一个 SQL 查询，找出 所有在 ‘Finance’ 部门工作的员工的姓名和薪资。（提示：需要连接两个表，并使用 WHERE 子句进行过滤）

SELECT employee.name, employee.salary
FROM employee INNER JOIN department
ON employee.id = department.emp_id
WHERE department.dept = 'Finance';

本章小结

本节深入探讨了数据库操作中至关重要的多表连接技术。我们理解了通过共享键将分散的数据重新组合的价值，并系统学习了交叉连接、内连接和外连接（左连接、右连接）这几种核心连接方式的机制、区别及应用场景。

📋 行动清单

• 理解并记忆：能够清晰阐述 INNER JOIN、LEFT JOIN 和 CROSS JOIN 的区别，并能用 Venn 图或简单例子说明。
• 代码实践：在 Python 环境中，使用 sqlite3 库创建两个有逻辑关联的表，并分别用代码实现这三种连接，观察输出结果。
• 场景匹配：面对一个具体的数据分析问题（如“统计每个部门的平均薪资”），能够判断是否需要多表连接，并选择正确的连接类型来编写 SQL 查询。

—— 小象教研组