Join，left join，right join(1)--连接原理（三十九)
Join，left join，right join(1)--连接原理（三十九)
前面说了mysql优化器访问数据库的方法有const，ref，ref_or_null，range，index，all。然后又分为条件全部是索引回表查询，和条件有非索引查询，则需要回表之后，在过滤。又有intersection合并索引和union并集索引，当两个单独二级索引查询，不是联合索引查询，可能会触发这两个索引查询，用and是intersection，用or是union查询，触发有两个注重点：
二级索引必须等值匹配，联合索引必须所有值匹配。
主键索引可以范围匹配。
因为二级索引建立在主键索引等值的情况下查询的，二级索引如果是个联合索引，则是按照最左原则，一个个排序的，若范围，则不能排序。主键索引可以范围排序的原因，比如查询出等值的二级索引之后，在二级索引的叶子节点直接找到其主键范围的数据，因为数据原本就是按主键排序好的
索引合并Intersection、union (3)--单表访问方法（三十八)
连接简介
我们先建立两个表：
mysql> create table t1(m1 int,n1 char(1));Query OK, 0 rows affected (0.15 sec) mysql> create table t2(m2 int,n2 char(1));Query OK, 0 rows affected (0.04 sec) mysql> insert into t1 values (1,'a'),(2,'b'),(3,'c');Query OK, 3 rows affected (0.01 sec)Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into t2 values (2,'b'),(3,'c'),(4,'d');Query OK, 3 rows affected (0.00 sec)Records: 3 Duplicates: 0 Warnings: 0从表的数据我们可以看到，t1是1，a；2，b；3，c；t2是2，b；3，c；4，d；连接的本质就是把每条数据依次匹配起来的组合加入一个结果集返回给用户，所以依次匹配之后他们有3 * 3 = 9条数据，像这样的结果集我们称为笛卡尔积，sql也很简单，我们只需要这样查询就好：
Select * from t1,t2;
链接过程简介
如果我们愿意，可以连接任意数量的表，但不建议这么使用，因为如果三张表，每张表只有很少的100条数据，但连接之后的数据量就是100*100*100 = 1000000条数据查询出来返回给用户，所以连接时候加入特定的条件过滤一下是很有必要的。
涉及单表的条件：其实就是搜索条件，比如t1.m<a，或者t2.m<d；
设计多表的条件：如t1.m = t2.m，t1.n1>t2.n2等。
下面我们来分析一下他的搜索过程：
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
这里面有三个条件：
t1.m1>1
t1.m1 = t2.m2
t2.n2> ‘d’
那么大致过程如下：
首先确定第一个需要查询的表，为驱动表， t1.m1>1， mysql优化器会选择一个方法访问数据库，因为我们没给表建立索引，所以访问数据库的方法是ALL，全表的方式查询出t1.m1>1的数据有两条，2，b;和3,c。
针对上面的数据，我们需要去t2表匹配，我们称t2表为被驱动表。因为上面有两条数据，所以需要去被驱动表查询两次：1）因为t1.m1=t2.m2，所以t2.m2=2 and t2.n2>d这两个条件过滤查询。2)和t2.m2 = 3 and t2.n2>d这两个条件过滤查询。
从上面的情况可以看到，当用连接查询表时候，驱动表会访问一次，被驱动表会访问两次，当我们吧t1.m1>1的条件去掉的话，那我们t1就能查出三条数据，这时候被驱动表也要查询三次，也就是在两个表连接查询的时候，驱动表会查询一次，被驱动表可能查询多次。
内连接和外连接
为了大家能更直观的了解，我们再建立两个表：
mysql> create table student( -> number int not null auto_increment comment '学号', -> name varchar(5) comment '姓名', -> major varchar(30) comment '专业', -> primary key(number) -> )engine=InnoDB charset=utf8 comment '学生信息表';Query OK, 0 rows affected (0.05 sec) mysql> create table score( -> number int comment '学号', -> subject varchar(30) comment '科目', -> score tinyint comment '分数', -> primary key (number,subject) -> )engine=InnoDB CHARSET=UTF8 COMMENT '学生成绩表';Query OK, 0 rows affected (0.04 sec)我们向表里插入一些数据，然后查询出来可以看到：
mysql> SELECT * FROM student;+----------+-----------+--------------------------+| number | name | major |+----------+-----------+--------------------------+| 20180101 | 杜子腾 | 软件学院 || 20180102 | 范统 | 计算机科学与工程 || 20180103 | 史珍香 | 计算机科学与工程 |+----------+-----------+--------------------------+3 rows in set (0.00 sec) mysql> SELECT * FROM score;+----------+-----------------------------+-------+| number | subject | score |+----------+-----------------------------+-------+| 20180101 | 母猪的产后护理 | 78 || 20180101 | 论萨达姆的战争准备 | 88 || 20180102 | 论萨达姆的战争准备 | 98 || 20180102 | 母猪的产后护理 | 100 |+----------+-----------------------------+-------+4 rows in set (0.00 sec)如果我们想把某个学生对应的分数查询出来该怎么写sql呢，可以用t1.number = t2.number：
mysql> SELECT * FROM student, score WHERE student.number = score.number;+----------+-----------+--------------------------+----------+-----------------------------+-------+| number | name | major | number | subject | score |+----------+-----------+--------------------------+----------+-----------------------------+-------+| 20180101 | 杜子腾 | 软件学院 | 20180101 | 母猪的产后护理 | 78 || 20180101 | 杜子腾 | 软件学院 | 20180101 | 论萨达姆的战争准备 | 88 || 20180102 | 范统 | 计算机科学与工程 | 20180102 | 论萨达姆的战争准备 | 98 || 20180102 | 范统 | 计算机科学与工程 | 20180102 | 母猪的产后护理 | 100 |+----------+-----------+--------------------------+----------+-----------------------------+-------+4 rows in set (0.00 sec)但这时候没有史珍香的数据，因为她没有考试，但老师想知道她考了0分，这时候该怎么显示出来呢
这时候我们外连接就出现了，外连接分为左连接和右连接：
1、内连接：如果用inner join连接两个表，如果找到对应的值，则会直接不显示。
2、外连接：1）左连接，以左边表为驱动连接。2）右连接，以右边表为驱动连接。
where语句过滤：where就是我们平时用的那种，不论是内连接还是外连接，凡事不符合where结果集的都会被过滤掉。
on语句过滤：对于外连接来说，如果在驱动表里查询到了值，就会直接返回，即是被驱动表没有值，也会返回null。(值得注意的是，如果是内连接，on和where的用法是一样的)
on连接又称为连接条件，一般来说，我们涉及到单表查询的就用where，如果两个表都需要过滤的，就用on。
Left左连接的使用
mysql> SELECT s1.number, s1.name, s2.subject, s2.score FROM student AS s1 LEFT JOIN score AS s2 ON s1.number = s2.number;+----------+-----------+-----------------------------+-------+| number | name | subject | score |+----------+-----------+-----------------------------+-------+| 20180101 | 杜子腾 | 母猪的产后护理 | 78 || 20180101 | 杜子腾 | 论萨达姆的战争准备 | 88 || 20180102 | 范统 | 论萨达姆的战争准备 | 98 || 20180102 | 范统 | 母猪的产后护理 | 100 || 20180103 | 史珍香 | NULL | NULL |+----------+-----------+-----------------------------+-------+5 rows in set (0.04 sec)结果如同我们之前说的那样，当用on的时候，作为驱动表的数据，会显示出来，如果被驱动表没有数据，则会显示null。
right 右连接和他类似，这里就不介绍了。
内连接Inner join
在外连接前面我们写的基本都是内连接，但还有其他写法，比如吧left join换成inner join就是内连接了，与外连接的根本区别就是，内连接on语句和where一样，只要不符合，都不返回，索引内连接用on 是没有驱动表和被驱动表的概念。
腾讯云开发者
