你真的会用join吗？

你真的会用join吗？

不知道有多少人会在平常工作中使用MySQL里的join，就我个人而言，从来没有用过，因为join的在MySQL中的应用场景有明显缺陷。假如通过join不同的表，最后用count()、sum()等去做数据统计，一旦数据量上升性能会急剧下降，尤其需要考虑最坏情况下产生笛卡尔积的影响（单表1万行，双表笛卡尔积就超过1亿行了）！

在这种情况下，实时数据分析应该由olap类型的数据仓库ClickHouse、AnalyticDB等来处理以保证性能。非实时情况下，比如报表类等T+1日产出的需求，只需要采用Hive、DataWorks等转存，然后每天统计一次，跑几个小时也没关系。

如果一定要在MySQL中用join

虽然非常不推荐这种做法，但是如果考虑到额外开发成本，我们至少要遵循一些MySQL中使用join的最佳实践：

1. 小表驱动大表。小表定义：join相关表按照各自条件过滤，过滤完成后计算参与join的总数据量，量级较小的那个就是“小表”。

2. 尽可能用上被驱动表的索引，可以通过explain看下有没有用到索引，这种join方式称为“Index Nested-Loop Join”。而通常笛卡尔积得到的join是“Simple Nested-Loop Join”，MySQL会用内存缓冲区优化，称为“Block Nested-Loop Join”，但是效率仍远不如能利用索引的join。

所以join还能不能用？

join虽然不适合在MySQL中用，但是这并不意味join本身失去作用。join在大数据离线统计中是十分常用的，忽略了响应时间，我们就可以将不同表连接起来而无后顾之忧。我们对于join更应该掌握的是其在数据统计中的灵活运用，由于数据聚合后误差非常难以排查，出错只能逐条记录核对，正确理解join的结果将大大降低排查修复成本。

join怎么用？

本文以下面两张表为例进行分析：

create table t1 (
  id int not null,
  name varchar(64) not null,
  age int not null,
  male int not null,
  PRIMARY KEY (id),
  key idx_age (age)
) ENGINE=InnoDB;

INSERT INTO t1 VALUES (1, 'a', 10, 0), (2, 'b', 20, 0), (3, 'c', 30, 1), (4, 'd', 40, 1), (5, 'e', 50, 1);

create table t2 (
  id int not null,
  name varchar(64) not null,
  age int not null,
  class int not null,
  PRIMARY KEY (id),
  key idx_age (age)
) ENGINE=InnoDB;

INSERT INTO t2 VALUES (1, 'a', 10, 1), (3, 'c', 30, 1), (5, 'y', 50, 2);

内连接

在MySQL中，JOIN 、 CROSS JOIN 和 INNER JOIN 是语法等价的。在其他数据库的SQL语法标准中，CROSS JOIN 语义可能会有所不同，但各个数据库使用JOIN/INNER JOIN传达的语义都是一样的，而我们通常也就是使用JOIN/INNER JOIN。 JOIN 通常与 ON 子句一起使用，用来过滤不符合条件的数据 。假如我们不添加任何条件，JOIN得到的结果就是笛卡尔积：

SELECT * FROM t1 JOIN t2;

这通常不是我们想要的结果，实际使用中需要通过ON来指定需要等值连接的列。

SELECT * FROM t1 JOIN t2 on t1.id = t2.id;

自然连接

内连接也可以称为等值连接，这是与自然连接区别的一种方式，因为自然连接的结果中同名列只展示一次。其次，自然连接自动匹配所有同名列，所以不需要过滤条件。

即

SELECT * FROM t1 NATURAL JOIN t2;

等价于

SELECT * FROM t1 JOIN t2 on t1.id = t2.id AND t1.name = t2.name AND t1.age = t2.age;

当然最终自然连接会进行去重：

而内连接不会：

实际使用中，我们通常需要明确知道SQL是在干什么以保证数据准确，所以还是尽量避免使用自然连接这种隐式表达的语法。使用内连接能够清晰表明要连接的列，同时列重复对求和、计数等统计也不会产生负面影响。

外连接

外连接是实际大数据离线统计中最常见的连接方式，它与内连接的使用区别也往往是最麻烦的地方，错误使用了外连接或者内连接都会导致统计偏差。左连接和右连接本质是一样的，只不过一个是以左表为驱动表，另一个是以右表为驱动表。在外连接中和内连接类似，OUTER是可以省去的，即LEFT OUTER JOIN等价于LEFT JOIN。下面就以左连接为例。

• 左连接

左连接相比内连接最大的区别就是会保留左表查出来的所有行，如果右表没有对应的匹配记录，则结果集中右表的列全部设为NULL，效果如下：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id;

这是一个很重要的特性，在做数据分析的时候，我们需要保证符合条件的主体记录不遗漏。例如我们往往会以符合条件的订单作为左表，此时不管是否能从其他表查到相关的优惠活动明细信息，我们都必须保证结果集中包含每一条符合条件的订单记录，相关优惠信息的列可以为NULL。

但是，最终结果集的行数只多不少，这是很多人可能会忽略的点。以左表为准，并不代表总数与左表返回的行数丝毫不差，假如右表有符合join条件的N行，那么原来左表返回的一行记录就会存成N行记录。假如我们往t2表再插入一行：

INSERT INTO t2 VALUES (6, 'X', 10, 1);

那么此时根据age来进行join会返回6行记录，这比t1的总行数还要多！

SELECT * FROM t1 LEFT JOIN t2 ON t1.age = t2.age;

• 全外连接

全外连接（FULL OUTER JOIN）并不被MySQL官方支持，但是我们可以通过UNION来模拟其效果，即

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

等价于

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1 RIGHT JOIN t2 ON t1.id = t2.id;

• 求差集

左外连接还有一个重要的小技巧就是求差集。目前为止，外连接对于驱动表来说都是补充信息的作用。假如我们想要删除信息应该怎么办呢？我们还是可以利用左连接，只不过我们得到结果集之后需要利用WHERE进行一次过滤，得到差集：

SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

这么做的意义是什么呢？一个主要作用就是对左驱动表进行二次过滤，例如我们要找订单完成计费的记录，但是订单完成计费和订单取消计费是两条记录，我们要找出无取消退款的完单计费记录就需要利用左连接求差集。

在我实际工作开发中，左连接是最常见的统计方式，因为能够保证主体信息不缺漏，至于从其他表获取的次要信息只是作为补充，而且默认值为MULL并不影响SUM()等聚合函数的统计。最后，我们用一张网络上流传广泛的JOIN示意图作为结尾：