Join,left join,right join(1)--连接原理(三十九)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,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

设计多表的条件:如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 是没有驱动表和被驱动表的概念。

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
4月前
|
SQL Oracle 关系型数据库
各种JOIN的区别
各种JOIN的区别
129 2
|
2月前
|
关系型数据库 数据挖掘 数据库
解析数据库联结:应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN
解析数据库联结:应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN
47 2
|
2月前
|
关系型数据库 数据挖掘 数据库
解析数据库联结:应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN
解析数据库联结:应用与实践中的 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN 与 CROSS JOIN
65 1
|
7月前
|
SQL Oracle 关系型数据库
Oracle查询优化-left join、right join、inner join、full join和逗号的区别
【1月更文挑战第5天】【1月更文挑战第13篇】实际查询时,多表联查是常规操作,但是连接方式有多种。
566 0
|
关系型数据库 MySQL
left join、right join和join,傻傻分不清?
真的是一张图道清所有join的区别啊,可惜我还是看不懂,可能人比较懒,然后基本一个left join给我就是够用的了,所以就没怎么去仔细研究了,但是现实还是逼我去搞清楚,索性自己动手,总算理解图中的含义了,下面就听我一一道来。
106 1
|
关系型数据库 MySQL
inner join 、left join、right join,优先使用inner join
inner join 、left join、right join,优先使用inner join
|
关系型数据库 MySQL
八、inner join 、left join、right join,优先使用inner join
八、inner join 、left join、right join,优先使用inner join
472 0
|
SQL 数据库
SQL中关于Join、Inner Join、Left Join、Right Join、Full Join、On、 Where区别
SQL中关于Join、Inner Join、Left Join、Right Join、Full Join、On、 Where区别
137 0
SQL中关于Join、Inner Join、Left Join、Right Join、Full Join、On、 Where区别
|
SQL 语音技术 数据库
SQL基础【十五、join、Inner join、Left join、Right join、Full join】
SQL基础【十五、join、Inner join、Left join、Right join、Full join】
162 0
SQL基础【十五、join、Inner join、Left join、Right join、Full join】
|
分布式计算 MaxCompute 索引
【转载】MaxCompute full outer join改写left anti join实践
ods层数据同步时经常会遇到增全量合并的模型,即T-1天增量表 + T-2全量表 = T-1全量表。可以通过full outer join脚本来完成合并,但是数据量很大时非常消耗资源。本文将为您介绍在做增量数据的增加、更新时如何通过full outer join改写left anti join来实现的最佳实践。
12879 0
【转载】MaxCompute full outer join改写left anti join实践