数据库中的连接运算

一、定义

连接一般定义为 $\underset{A\theta B}{R\infty S}$，其中 $\infty$ 表示连接运算符，$\theta$ 为比较运算符，$A\theta B$ 是条件表达式。含义是从 $R$ 和 $S$ 的笛卡尔积 $R\times S$ 中选取 $R$ 关系在 $A$ 属性组上的值与 $S$ 关系在 $B$ 属性组上的值满足比较关系 $\theta$ 的元组。

连接操作是数据库中用于将两个或多个表的数据行合并在一起的操作，常用于在不同表之间建立关联以进行查询。连接操作有多种类型，包括内连接、外连接以及交叉连接。

1. 内连接（Inner Join）：内连接是连接操作的一种，它返回两个表之间满足连接条件的所有行，等值连接是一种特殊的内连接，而自然连接是一种特殊的等值连接。
   • 等值连接（Equi Join）：比较运算符 $\theta$ 为 $=$ 的连接运算称为等值连接，它是从关系 $R$ 与 $S$ 的广义笛卡尔积中选取 $A$、$B$ 属性值相等的那些元组。
   • 自然连接（Natural Join）：自然连接是等值连接的一种特殊形式，它会自动匹配两个表中具有相同列名的列，并将它们用作连接条件。此外自然连接还会自动去除重复的列，因此不需要指定连接条件。

---

2. 外连接（Outer Join）：外连接用于返回连接操作中的全部数据行，包括未匹配到的数据行，它分为左外连接、右外连接和全外连接。
   • 左外连接（Left Outer Join）：左外连接会返回左表中的所有数据行，同时返回右表中与左表匹配的数据行。如果没有匹配的数据行，右表中的列将包含 NULL 值。
   • 右外连接（Right Outer Join）：右外连接与左外连接类似，但会返回右表的所有数据行以及与左表匹配的数据行。如果没有匹配的数据行，左表中的列将包含 NULL 值。
   • 全外连接（Full Outer Join）：全外连接返回左表和右表的所有数据行，如果没有匹配的数据行，对应的列将包含 NULL 值。

---

3. 交叉连接（Cross Join）：交叉连接返回两个表的笛卡尔积，即左表中的每个数据行都与右表中的所有数据行组合在一起。交叉连接得到的结果包含大量数据行，通常需要谨慎使用。

---

二、示例

省份表 provinces 与 省会表 capital：

-- 创建省份表
DROP TABLE IF EXISTS provinces;
CREATE TABLE provinces
(
    province_name VARCHAR(255),
    country_name  VARCHAR(255),
    capital_name       VARCHAR(255)
);

-- 插入省份数据
INSERT INTO provinces (province_name, country_name, capital_name)
VALUES ('Guangdong', 'China', 'Guangzhou'),
       ('Sichuan', 'China', 'Chengdu'),
       ('Jiangsu', 'China', 'Nanjing'),
       ('California', 'USA', 'Sacramento'),
       ('Hawaii', 'USA', 'Honolulu'),
       ('Texas', 'USA', 'Houston');

province_name	country_name	capital_name
Guangdong	China	Guangzhou
Sichuan	China	Chengdu
Jiangsu	China	Nanjing
California	USA	Sacramento
Hawaii	USA	Honolulu
Texas	USA	Houston

-- 创建省会表
DROP TABLE IF EXISTS capital;
CREATE TABLE capital
(
    capital_name VARCHAR(255),
    population   INT
);

-- 插入省会数据
INSERT INTO capital (capital_name, population)
VALUES ('Guangzhou', 15000000),
       ('Nanjing', 8000000),
       ('Sacramento', 500000),
       ('Honolulu', 380000),
       ('Tokyo', 14000000),
       ('London', 9000000);

capital_name	population
Guangzhou	15000000
Nanjing	8000000
Sacramento	500000
Honolulu	380000
Tokyo	14000000
London	9000000

---

2.1 等值连接

SELECT *
FROM provinces
JOIN capital ON provinces.capital_name = capital.capital_name;

province_name	country_name	provinces.capital_name	capital.capital_name	population
Guangdong	China	Guangzhou	Guangzhou	15000000
Jiangsu	China	Nanjing	Nanjing	8000000
California	USA	Sacramento	Sacramento	500000
Hawaii	USA	Honolulu	Honolulu	380000

---

2.2 自然连接

SELECT *
FROM provinces
NATURAL JOIN capital;

capital_name	province_name	country_name	population
Guangzhou	Guangdong	China	15000000
Nanjing	Jiangsu	China	8000000
Sacramento	California	USA	500000
Honolulu	Hawaii	USA	380000

---

2.3 左外连接

SELECT *
FROM provinces
LEFT JOIN capital ON provinces.capital_name = capital.capital_name;

province_name	country_name	capital_name	capital_name	population
Guangdong	China	Guangzhou	Guangzhou	15000000
Sichuan	China	Chengdu	null	null
Jiangsu	China	Nanjing	Nanjing	8000000
California	USA	Sacramento	Sacramento	500000
Hawaii	USA	Honolulu	Honolulu	380000
Texas	USA	Houston	null	null

---

2.4 右外连接

SELECT *
FROM provinces
RIGHT JOIN capital ON provinces.capital_name = capital.capital_name;

province_name	country_name	capital_name	capital_name	population
Guangdong	China	Guangzhou	Guangzhou	15000000
Jiangsu	China	Nanjing	Nanjing	8000000
California	USA	Sacramento	Sacramento	500000
Hawaii	USA	Honolulu	Honolulu	380000
null	null	null	Tokyo	14000000
null	null	null	London	9000000

---

2.5 全外连接

MySQL 不支持全外连接，这里只对相应结果进行展示。

province_name	country_name	capital_name	capital_name	population
Guangdong	China	Guangzhou	Guangzhou	15000000
Sichuan	China	Chengdu	null	null
Jiangsu	China	Nanjing	Nanjing	8000000
California	USA	Sacramento	Sacramento	500000
Hawaii	USA	Honolulu	Honolulu	380000
Texas	USA	Houston	null	null
null	null	null	Tokyo	14000000
null	null	null	London	9000000

---

2.6 交叉连接

交叉连接得到的是两张表数据的乘积，所以以下两种写法是等价的：

SELECT *
FROM provinces
CROSS JOIN capital;

SELECT *
FROM provinces, capital;

province_name	country_name	capital_name	capital_name	population
Texas	USA	Houston	Guangzhou	15000000
Hawaii	USA	Honolulu	Guangzhou	15000000
California	USA	Sacramento	Guangzhou	15000000
Jiangsu	China	Nanjing	Guangzhou	15000000
Sichuan	China	Chengdu	Guangzhou	15000000
Guangdong	China	Guangzhou	Guangzhou	15000000
Texas	USA	Houston	Nanjing	8000000
Hawaii	USA	Honolulu	Nanjing	8000000
California	USA	Sacramento	Nanjing	8000000
Jiangsu	China	Nanjing	Nanjing	8000000
Sichuan	China	Chengdu	Nanjing	8000000
Guangdong	China	Guangzhou	Nanjing	8000000
Texas	USA	Houston	Sacramento	500000
Hawaii	USA	Honolulu	Sacramento	500000
California	USA	Sacramento	Sacramento	500000
Jiangsu	China	Nanjing	Sacramento	500000
Sichuan	China	Chengdu	Sacramento	500000
Guangdong	China	Guangzhou	Sacramento	500000
Texas	USA	Houston	Honolulu	380000
Hawaii	USA	Honolulu	Honolulu	380000
California	USA	Sacramento	Honolulu	380000
Jiangsu	China	Nanjing	Honolulu	380000
Sichuan	China	Chengdu	Honolulu	380000
Guangdong	China	Guangzhou	Honolulu	380000
Texas	USA	Houston	Tokyo	14000000
Hawaii	USA	Honolulu	Tokyo	14000000
California	USA	Sacramento	Tokyo	14000000
Jiangsu	China	Nanjing	Tokyo	14000000
Sichuan	China	Chengdu	Tokyo	14000000
Guangdong	China	Guangzhou	Tokyo	14000000
Texas	USA	Houston	London	9000000
Hawaii	USA	Honolulu	London	9000000
California	USA	Sacramento	London	9000000
Jiangsu	China	Nanjing	London	9000000
Sichuan	China	Chengdu	London	9000000
Guangdong	China	Guangzhou	London	9000000