深入理解数据库中的各种连接

按照我的理解，我将数据库中的连接分类为广义内连接和外连接，如下图，稍后我将解释我为什么这么分。

1 广义内连接（交叉连接）

所有的广义内连接的核心和共同点是完全基于笛卡尔积原理的，两个表进行笛卡尔积运算得到的结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，称为‘笛卡尔积表’，结果表的行数等于两个表的行数之积，接下来介绍的所有广义内连接的结果都能在笛卡尔积表里找到。两个表直接做笛卡尔积运算：

-- 写法1,INNER可省略
SELECT * from 表1 [INNER] JOIN 表2；
-- 写法2
SELECT * from 表1，表2；

注意，这里没有任何限制和条件，这种联结称为交叉连接，得到的结果就是两个表的笛卡尔积，这是所有广义内连接的基础，所有广义内连接在原理上都是通过在笛卡尔积的基础上进行过滤得到的。

1.1 内连接（等值连接）

现在我们要在笛卡尔积的基础上进行过滤了，以选择我们需要的记录（行）：

-- 写法1：显式内连接，通过ON进行过滤
SELECT * from 表1 [INNER] JOIN 表2 ON 等值条件；
-- 写法2：隐式内连接，通过WHERE进行过滤
SELECT * from 表1，表2 WHERE 等值条件；

注意这里的条件是等值条件，一般类似 表1.字段1=表2.字段2 这种（字段就是列），所以内连接又称为等值连接。

1.2 自然连接

上面提到内连接是对行进行过滤，那可不可以对列进行过滤哪？当然可以。一般的情况就是当我们对两个表进行交叉连接后，会有重复的列，即此列在两个表里同时存在，自然连接会排除多次出现的列，使每一列只返回一次。怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。自然连接要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成：

-- 写法1
SELECT 表1.*, 表2.不包含重复列的其他列 from 表1 [INNER] JOIN 表2；
-- 写法2
SELECT 表1.*, 表2.不包含重复列的其他列 from 表1，表2；

但一般我们是既对行进行过滤也对列进行过滤，事实上，我们几乎使用的每个内连接都是自然连接，很可能永远都不会用到不是自然连接的内连接，即它们两个是搭配使用的：

-- 写法1
SELECT 表1.*, 表2.不包含重复列的其他列 from 表1 [INNER] JOIN 表2 ON 等值条件；
-- 写法2
SELECT 表1.*, 表2.不包含重复列的其他列 from 表1，表2 WHERE 等值条件；

1.3 自连接

自连接顾名思义就是自己跟自己连接，说白了他本质上还是一种特殊的自己跟自己的交叉连接，自连接的实现必须借助给表起别名，如果不用别名就会报错，因为要多次调用同一个表：

-- 写法1，AS t1为起别名操作，注意Oracle没有AS，直接t1即可
SELECT * from 表1 AS t1 [INNER] JOIN 表2 AS t2；
-- 写法2
SELECT * from 表1 AS t1，表2 AS t2；

但一般我们不这么用，与前面类似，自连接的使用一般搭配内连接和自然连接：

-- 写法1
SELECT 表1.*, 表2.不包含重复列的其他列 from 表1 AS t1 [INNER] JOIN 表2 AS t2 ON 等值条件；
-- 写法2
SELECT 表1.*, 表2.不包含重复列的其他列 from 表1 AS t1，表2 AS t2 WHERE 等值条件；

2. 外连接

之前我们提到所有的广义内连接是完全基于笛卡尔积的，即所有广义内连接的结果都能在笛卡尔积表里找到，内连接将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行，这些行在是笛卡尔积中是不存在的，这时就要用到外连接了，即外连接不是完全基于笛卡尔积的。这可能听起来难以理解，我将举例子说明。

2.1 左外连接

返回包括左表中的所有记录和右表中连接字段相等的记录。这个要求笛卡尔积表并不能满足。例如有两个表如下：

员工表：
    +----------+--------------+----------+
    | 员工ID   | 员工名字     | 部门ID   |
    +----------+--------------+----------+
    | 1        | 张三         | 1        |
    | 2        | 李四         | 2        |
    | 3        | 王五         | NULL     |
    +----------+--------------+----------+
部门表：
    +----------+--------------+
    | 部门ID   | 部门名字     |
    +----------+--------------+
    | 1        | 销售部       |
    | 2        | 财务部       |
    +----------+--------------+

假设这么一种情况，王五刚入职，还没有分配部门，这时领导要求查询所有员工的信息及所在的部门信息，包括未分配部门的员工。那这个要求在笛卡尔积表里能找到吗？
我们试一下内连接：

mysql> SELECT * FROM emp [INNER] JOIN dep ON emp.`员工ID` = dep.`部门ID`;

+----------+--------------+----------+----------+--------------+
| 员工ID   | 员工名字     | 部门ID   | 部门ID   | 部门名字     |
+----------+--------------+----------+----------+--------------+
| 1        | 张三         | 1        | 1        | 销售部       |
| 2        | 李四         | 2        | 2        | 财务部       |
+----------+--------------+----------+----------+--------------+

好像不行，并不包括王五的记录啊，那我们看一下笛卡尔表里都有什么：

mysql> SELECT * FROM emp [INNER] JOIN dep;
+----------+--------------+----------+----------+--------------+
| 员工ID   | 员工名字     | 部门ID   | 部门ID   | 部门名字     |
+----------+--------------+----------+----------+--------------+
| 1        | 张三         | 1        | 1        | 销售部       |
| 1        | 张三         | 1        | 2        | 财务部       |
| 2        | 李四         | 2        | 1        | 销售部       |
| 2        | 李四         | 2        | 2        | 财务部       |
| 3        | 王五         | NULL     | 1        | 销售部       |
| 3        | 王五         | NULL     | 2        | 财务部       |
+----------+--------------+----------+----------+--------------+

我们发现笛卡尔积表里并没有符合要求的记录，此时就要用到外连接了：

mysql> SELECT * FROM emp LEFT [OUTER] JOIN dep ON emp.`员工ID` = dep.`部门ID`;

+----------+--------------+----------+----------+--------------+
| 员工ID   | 员工名字     | 部门ID   | 部门ID   | 部门名字     |
+----------+--------------+----------+----------+--------------+
| 1        | 张三         | 1        | 1        | 销售部       |
| 2        | 李四         | 2        | 2        | 财务部       |
| 3        | 王五         | NULL     | NULL     | NULL         |
+----------+--------------+----------+----------+--------------+

我们可以看到王五那条记录在笛卡尔积表是不存在的，这也是我们所说的外连接不是完全基于笛卡尔积的，左外连接会返回包括左表中的所有记录和右表中连接字段相等的记录。这存在一个隐式的条件，外连接可以没有条件吗？我们知道内连接没有条件会变成交叉连接，答案是外连接必须存在等值条件，不然这种连接就跟外连接的定义相违背，SQL也会报错。

2.2 右外连接

跟左外连接原理完全相同，只不过返回包括右表中的所有记录和左表中连接字段相等的记录。其实掌握一个就行，左外连接中两个表互换位置就是右外连接了。语法：

SELECT * from 表1 RIGHT [OUTER] JOIN 表2 ON 等值条件；

2.3 全外连接

返回左右表中所有的记录和左右表中连接字段相等的记录。
语法：

SELECT * from 表1 FULL [OUTER] JOIN 表2 ON 等值条件；

注意：FULL OUTER JOIN的支持： Access、MariaDB、MySQL、Open Office Base和SQLite不支持FULL OUTER JOIN语法