MySQL各种表连接（图文并茂，过目难忘）

将 脚本之家 设为“星标⭐”
第一时间收到文章更新

来源 | 互联网全栈架构

数据表连接是非常常见的一种数据库操作，在MySQL中，这种JOIN操作有很多类型，包括内连接、左外连接、右外连接等等，而每种连接的含义都不一样，如果死记硬背，不仅很难记住，而且也容易搞混淆，今天我们尝试使用图解的方式来讲解各种连接的内涵，因为结合了具体的例子，相信会让大家印象深刻。

MySQL中常见的表关联有如下几种：

一、准备工作

先创建两张表，一张是订单表，一张是客户表，订单表中的字段customer_id与客户表的主键关联。数据表创建完成后，再往表里插入简单的测试数据：

先是订单表：

CREATE TABLE `t_order` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `order_no` int(11) DEFAULT NULL COMMENT '订单号',
  `customer_id` int(11) DEFAULT NULL COMMENT '客户id'


    
,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO `t_order` VALUES ('1', '1001', '1');
INSERT INTO `t_order` VALUES ('2', '1002', '26');

然后是客户表：

CREATE TABLE `t_customer` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `customer_name` varchar(255) DEFAULT NULL COMMENT '客户姓名 ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入数据
INSERT INTO `t_customer` VALUES ('1', 'John');
INSERT INTO `t_customer` VALUES ('2', 'Tom');

两张表中的数据分别如下：

二、INNER JOIN

INNER JOIN应该是最常用的表连接了，它只返回两个表中满足关联条件的数据：

以内连接的方式关联查询订单表和客户表：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A INNER JOIN t_customer B
ON A.customer_id=B.id

查询结果如下，可以看出，只返回了一行记录，内连接可以理解为查询两个表的交集：

三、LEFT JOIN

LEFT JOIN是左连接，它会返回左表中所有数据，即使右表没有匹配行，如果右表没有相匹配的记录，则返回NULL：

SQL语句和查询结果如下：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B
ON A.customer_id=B.id

可以看出，左连接返回了左表的数据，对于客户id为26的记录，在客户表中并不存在，所以它们的值为NULL。

四、RIGHT JOIN

而RIGHT JOIN是右连接，跟左连接的逻辑类似，只不过它以右表为基准进行关联，它返回右表中所有数据，即使左表没有匹配行，如果左表没有相匹配的记录，则返回NULL：

SQL语句和查询结果如下：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B
ON A.customer_id=B.id

它返回了客户表（右表）中的所有数据，对于客户id为2的记录，订单表中没有对应的数据，所以相应的字段值为NULL。

五、FULL OUTER JOIN

对于上面提到的LEFT JOIN和RIGHT JOIN，它们分别表示左外连接和右外连接，完整的写法还需要加上OUTER关键字，也就是LEFT OUTER JOIN和RIGHT OUTER JOIN。

除了左外连接和右外连接，有时候还需要把两个表中的数据都查询出来，也就是满外连接，相当于是两个表的并集。目前版本5.7还不支持这样的查询，可以使用UNION来进行模拟：

SQL语句和查询结果如下：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B
ON A.customer_id=B.id
UNION
SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B
ON A.customer_id=B.id

六、LEFT JOIN EXCLUDING INNER JOIN

返回左表有但右表没有的数据：

SQL语句和查询结果如下：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B
ON A.customer_id=B.id
WHERE B.id IS NULL;

七、RIGHT JOIN EXCLUDING INNER JOIN

返回右表有但左表没有的数据：

SQL语句和查询结果如下：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B
ON A.customer_id=B.id
WHERE A.id IS NULL;

八、OUTER JOIN EXCLUDING INNER JOIN

返回左表和右表没有相互关联的数据：

SQL语句和查询结果如下：

SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A LEFT JOIN t_customer B
ON A.customer_id=B.id
WHERE B.id IS NULL
UNION
SELECT A.id AS A_id, A.order_no, A.customer_id, B.id AS B_id, B.customer_name
FROM t_order A RIGHT JOIN t_customer B
ON A.customer_id=B.id
WHERE A.id IS NULL

九、总结

另外，对于CROSS JOIN，它返回两个表的笛卡尔积，也就是把两个表中的数据组合起来，本文不再详述。

对于上面提到的几种关联方式，用一个大图来汇总，方便记忆：


  推荐阅读：