mysql的left join_mysql中的left join、right join、join

sql准备

INSERT INTO name(name, age, grade) VALUES ('小白', 20, 1), ('小黑', 21, 2), ('小红', 22, 3), ('小花', 23, 4), ('小绿', 24, 5) ;

INSERT INTO classes (cname) VALUES ('欧阳锋'), ('杨过'), ('乔峰');

INSERT INTO classes (id,cname) VALUES (7, '溜哒');

各种join的使用

left join 即为以sql语句中的左边的表为主要表关联右边的表,其中使用on作为条件筛选,where为过滤条件

以name为主表，classes为关联表

SELECT *

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id;

可以看到小花和小绿并没有关联到classes中的任何数据，我们以name为左表，然后以classes为右表然后进行关联，展示5行数据，条件不符合的小花和小绿(没有想对应的班级、师傅领养)，即为野生，需要使用null补全，而溜哒同学干脆直接无视。

以classes为主表以name为关联表

SELECT *

FROM name t1 RIGHT JOIN classes t2 ON t1.grade = t2.id;

可以看到以classes为主表，以name为关联表结果为4条，虽然溜哒同学什么也不会，但是还是可以下班之后溜哒溜哒溜溜狗的嘛(单身狗也是狗)！！

无主次关联join(inner join)

SELECT *

FROM name t1 JOIN classes t2 ON t1.grade = t2.id;

而join可以看到只有两个表完全的交集才能被显示出来，这里显示3条。果然，最后登上光明顶的还是名师下边的高徒。我等可以继续溜哒溜哒。

传说中的full join(mysql不支持，使用union来进行模拟)

SELECT *

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id

UNION

SELECT *

FROM name t1 RIGHT JOIN classes t2 ON t1.grade = t2.id;

可以看到，full join表示全并集，即6条数据。论起溜哒溜哒溜溜单身汪，溜哒同学还是可以的。

综上所述，可以整理得到：以哪个表为主表则检索出哪个表的全部内容，关联表中符合on关联要求的可以进行数据关联，如果不符合要求，那么需要以null行展示;如果使用join进行关联那么无主次关联，只显示符合要求的数据。

on、where的使用

on：两个表关联的时候使用，决定被关联的表的数据是否能与主表关联(name left join classes on name.grade = classes.id)主表name数据完全显示，不被关联的数据需要在grade侧以null数据补全

where：两个表关联后，再进行条件过滤

区别：on主要作用再被关联表中，where作用在关联后的左右数据上

基本作用

SELECT *

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id

WHERE t2.id IN (1, 3);

然后，把where中的条件拿到on中

SELECT *

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id AND t2.id IN(1,3);

可以看到，加上where条件后作用再left join on关联后的数据，将不符合where条件的全部去掉，只使用on，和我们先前得到的结论一致，不符合关联条件的需要null行补充。

on where 约束力相同的时候

先回顾一下join(inner join)。

SELECT *

FROM name t1 JOIN classes t2 ON t1.grade = t2.id AND t2.id IN(1,3);

得到join条件的两条数据。如果我们把追加的t2.id in(1,3)拿到where中是什么情况呢？

SELECT *

FROM name t1 JOIN classes t2 ON t1.grade = t2.id

WHERE t2.id IN(1,3);

可以看到，此处的t2.id IN(1,3)在on和在where中效果一样。溜哒同学表示可以理解为是join关键词造成的，应为join表示完全符合条件的才进行关联展示，不会进行数据null行补充，而where用于过滤数据，那么也是表示符合条件的进行展示。(如果问我为啥不把 t1.grade = t2.id也拿到where中去，那么同学可以自己动手试试嘛！)

各种join、on、where该怎么联合使用

首先拿出最开始的例子，普通left join和on使用，展示5条数据。

SELECT *

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id

然后我们进行数据统计，使用count()来进行查看数据条数。

SELECT count(t1.id)

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id;```


SELECT count(t2.id)

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id;


这里可以看到count(null)是不计入数据统计的(不要问为什么)，那么可以看出，关联后的统计在各自部分统计是没有什么问题的，name表侧是5条，而符合on条件的classes侧是3条。由此，可以引申出另一个容易出错的地方：在on中如果有多个条件进行关联限制，此时如果这些条件中有想进行过滤的那么需要拿到where中去，才会进行正确数据筛选。

####多on条件搭配where使用(不要用错)

SELECT *

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id AND t2.id IN(1, 3);`

count(t1.id) = 5

count(t2.id) = 2

这里如果想要统计符合classes.id in(1,3)的数据的时候，一定要把t2.id in(1,3)拿到where中去。比如，希望统计西毒、乔峰这两个前辈的高徒的年龄和需要将t2.id in(1,3)放到where中去。

SELECT sum(t1.age)

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id AND t2.id IN(1, 3);

SELECT sum(t1.age)

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id

WHERE t2.id IN(1, 3);

那么有没有可能不用where数据也能统计对的情况呢？

必然有(就好像数学老师说，这道题选A对不对啊？？大多数情况下不对。。。)，两个表进行关联，不使用where，但是当两部分有计算操作的时候，效果是理想的。

我们操作name中的age去加上classes中的id，(就当做师傅给徒弟传各自id数值的年限的内力)

SELECT t1.age+t2.id

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id AND t2.id IN(1, 3);

SELECT t1.age+t2.id

FROM name t1 LEFT JOIN classes t2 ON t1.grade = t2.id

WHERE t2.id IN(1, 3);

如上，两部分进行了加和计算操作，这时候如果再进行sum()等统计操作，其实出来的数据是一样的。使用规范，看你所在团队更倾向于哪一种。当然，如果这里使用join 内连接，那么就没有这么多不同了。

关于性能

首先必须明确的事情就是：

使用关联肯定是要从关联表中拿数据进行展示。

数据库输出的数据越多，性能越低。

数据库有自己的优化引擎，可能会将你的sql语句进行自我优化。

溜哒同学本身技术不好，这次不进行性能讨论，从输出上来看join性能要好一些，但是在工作中会存在明显的left join比join快的情况，网上都是说select中的统计并没有用到关联表的数据，所以存在数据库优化将关联表不进行关联，然后直接进行统计的情况，此时，join还是需要进行关联(双向的)， 所以会出现left join 比join快的情况。同时也会出现索引使用的问题。

当然这只是网上的文章，不是自己操作的没有办法说服自己，还是多看看书，做点小实验明确一下。(会有人看么？衰.jpg)