概念

JOIN

INNER JOIN（内连接，或等值连接）： 获取两个表中字段匹配关系的记录。INNER JOIN 中的 INNER 可省略。
LEFT JOIN（左连接）： 获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

笛卡尔乘积

定义：笛卡尔乘积是指在数学中，两个集合 X 和 Y 的笛卡尔积（Cartesian product），又称直积，表示为 X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。假设集合 A={a, b}，集合 B={0, 1, 2}，则两个集合的笛卡尔积为 {(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

来源：百度百科

举例：假设 A 表有 20 条记录，B 表有 30 条记录，则二者关联后的笛卡尔积共 20 * 30 = 600 条记录。也就是说 A 表中的每条记录都会与 B 表的所有记录关联一次，三种关联方式实际上就是对“笛卡尔积”的处理方式不同。

驱动表与被驱动表

当使用 LEFT JOIN 时，左表是驱动表，右表是被驱动表。
当使用 RIGHT JOIN 时，右表是驱动表，左表是被驱动表。
当使用 INNER JOIN 时，mysql 会选择数据量小的表作为驱动表，大表作为被驱动表。

测试

下面我们以 LEFT JOIN 为例，来演示、理解 JOIN 命令。

建表

商品表建表语句如下：

drop table goods;
CREATE TABLE `goods` (
  `goods_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '商品id',
  `goods_name` varchar(128) NOT NULL DEFAULT '' COMMENT '商品名',
  `category_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT '分类id',
  PRIMARY KEY (`goods_id`),
  KEY idx_category_id (`category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品表';

商品分类表建表语句如下：

drop table goods_category;
CREATE TABLE `goods_category` (
  `category_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '分类id',
  `category_name` varchar(128) NOT NULL DEFAULT '' COMMENT '分类名',
  PRIMARY KEY (`category_id`),
  KEY idx_category_name (`category_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品分类表';

数据插入

向商品表插入数据语句如下：

INSERT INTO `goods` (`goods_id`, `goods_name`, `category_id`) VALUES
(1,	'男鞋1',	1),
(2,	'男鞋2',	1),
(3,	'男鞋3',	4),
(4,	'T恤1',	2),
(5,	'T恤2',	2);

向商品分类表插入数据语句如下：

INSERT INTO `goods_category` (`category_id`, `category_name`) VALUES
(2,	'T恤'),
(3,	'羽绒服'),
(1,	'鞋');

表数据

最终查看的数据如下：

mysql> select * from goods;
+----------+------------+-------------+
| goods_id | goods_name | category_id |
+----------+------------+-------------+
|        1 | 男鞋1      |           1 |
|        2 | 男鞋2      |           1 |
|        3 | 男鞋3      |           4 |
|        4 | T恤1       |           2 |
|        5 | T恤2       |           2 |
+----------+------------+-------------+
5 rows in set (0.00 sec)

mysql> select * from goods_category;
+-------------+---------------+
| category_id | category_name |
+-------------+---------------+
|           2 | T恤           |
|           3 | 羽绒服        |
|           1 | 鞋            |
+-------------+---------------+
3 rows in set (0.00 sec)

LEFT JOIN 结果提问

提问，下面语句的执行结果是什么呢？

(1) select * from goods left join goods_category on goods.category_id = goods_category.category_id;

(2) select * from goods left join goods_category on (goods.category_id = goods_category.category_id);

(3) select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤1');

(4) select * from goods g left join goods_category c on (g.category_id = c.category_id and c.category_name = 'T恤');

(5) select * from goods g left join goods_category c on (g.category_id = c.category_id) where c.category_name = '鞋';

(6) select * from goods g left join goods_category c on (g.category_id = c.category_id) where g.goods_name = 'T恤1';

(7) select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤2') where g.goods_name = 'T恤1';

LEFT JOIN 结果

mysql> select * from goods left join goods_category on goods.category_id = goods_category.category_id;
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |           1 | 鞋            |
|        2 | 男鞋2      |           1 |           1 | 鞋            |
|        3 | 男鞋3      |           3 |           3 | 羽绒服        |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from goods left join goods_category on goods.category_id = goods_category.category_id;
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |           1 | 鞋            |
|        2 | 男鞋2      |           1 |           1 | 鞋            |
|        3 | 男鞋3      |           4 |        NULL | NULL          |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤1');
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |        NULL | NULL          |
|        2 | 男鞋2      |           1 |        NULL | NULL          |
|        3 | 男鞋3      |           4 |        NULL | NULL          |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |        NULL | NULL          |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and c.category_name = 'T恤');
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |        NULL | NULL          |
|        2 | 男鞋2      |           1 |        NULL | NULL          |
|        3 | 男鞋3      |           4 |        NULL | NULL          |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id) where c.category_name = '鞋';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |           1 | 鞋            |
|        2 | 男鞋2      |           1 |           1 | 鞋            |
+----------+------------+-------------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id) where g.goods_name = 'T恤1';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        4 | T恤1       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
1 row in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤2') where g.goods_name = 'T恤1';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        4 | T恤1       |           2 |        NULL | NULL          |
+----------+------------+-------------+-------------+---------------+
1 row in set (0.00 sec)

从上述结果我们可以发现，同一条件放在 on 结构和 where 结构中，最终的结果是不同的。

那么究竟是什么原因导致的呢，下面我们来分析一下。

on 和 where 条件

在探索 on 和 where 条件有何不同时，我们需要先明确几个概念：

LEFT JOIN：LEFT JOIN 会返回左表中的所有行，即使在右表中没有匹配的行。
临时表：数据库在通过连接两张或者多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

on 条件分析

我们来单独分析一下 on 条件，以上述三个不同的 on 条件为例：

mysql> select * from goods left join goods_category on goods.category_id = goods_category.category_id;
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |           1 | 鞋            |
|        2 | 男鞋2      |           1 |           1 | 鞋            |
|        3 | 男鞋3      |           4 |        NULL | NULL          |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤1');
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |        NULL | NULL          |
|        2 | 男鞋2      |           1 |        NULL | NULL          |
|        3 | 男鞋3      |           4 |        NULL | NULL          |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |        NULL | NULL          |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and c.category_name = 'T恤');
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |        NULL | NULL          |
|        2 | 男鞋2      |           1 |        NULL | NULL          |
|        3 | 男鞋3      |           4 |        NULL | NULL          |
|        4 | T恤1       |           2 |           2 | T恤           |
|        5 | T恤2       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
5 rows in set (0.00 sec)

无论我们在 on 条件语句中增加 左表的条件 或 右表的条件，还是都不加，最终的结果都是五条数据，即左表的全部数据。

但是右表的数据却不同，具体如下：

左表右表条件都不加：右表返回所有左表关联的数据。
左表增加条件g.goods_name = 'T恤1' ：右表只返回与满足该条件的左表数据关联的数据。
右表增加条件c.category_name = 'T恤' ：右表只返回满足该条件，并且和左表有关联的数据。

on 条件结论

on 条件是生成临时表的条件，所以不管 on 中的条件是否为真，都会返回左表中的所有记录。
on 条件会过滤右表数据，无论 on 中的条件是左表的条件还是右表的条件，都是用来过滤右表的数据的。满足条件并且有关联的数据才会写入临时表，不然值为 NULL。

where 条件分析

我们再来分析一下 where 条件对 LEFT JOIN 结果的影响。

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id) where c.category_name = '鞋';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        1 | 男鞋1      |           1 |           1 | 鞋            |
|        2 | 男鞋2      |           1 |           1 | 鞋            |
+----------+------------+-------------+-------------+---------------+
2 rows in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id) where g.goods_name = 'T恤1';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        4 | T恤1       |           2 |           2 | T恤           |
+----------+------------+-------------+-------------+---------------+
1 row in set (0.00 sec)

mysql> select * from goods g left join goods_category c on (g.category_id = c.category_id and g.goods_name = 'T恤2') where g.goods_name = 'T恤1';
+----------+------------+-------------+-------------+---------------+
| goods_id | goods_name | category_id | category_id | category_name |
+----------+------------+-------------+-------------+---------------+
|        4 | T恤1       |           2 |        NULL | NULL          |
+----------+------------+-------------+-------------+---------------+
1 row in set (0.00 sec)

从上述结果我们可以看到，在 on 中的条件和在 where 中的条件是不相互影响的。

on (g.goods_name = 'T恤2') where g.goods_name = 'T恤1' 不会导致最终结果为空的。

同上述只有 on 条件的 SQL 对比来看，where 条件会过滤记录，只留下最后符合 where 条件的记录，条件不为真就全部过滤掉。

where 条件结论

where 条件是在临时表生成好了之后，再对临时表进行过滤的条件。这时已经没有 LEFT JOIN 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

总结

on 是在生成临时表的时候使用的条件，不管on的条件是否起到作用，都会返回左表所有的行。
where 则是在生成临时表之后使用的条件，此时已经不管是否使用了 LEFT JOIN 了，只要条件不为真的行，全部过滤掉。
(INNER | LEFT | RIGHT) JOIN 会生成临时表，该临时表为左表，所以我们在写 JOIN 语句的时候应该选择数据量较小的表作为驱动表。

MYSQL LEFT JOIN 详解

概念