MySQL七种Join理论

最新推荐文章于 2024-06-26 18:31:47 发布
最新推荐文章于 2024-06-26 18:31:47 发布
阅读量519 收藏 7
点赞数 3
分类专栏： MySQL 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43808717/article/details/115770381
版权

1、join理论简述

在这里插入图片描述
核心：7种join理论最终都回到了大学课本离散数学、概率论中的知识，这才是join理论的核心；这些维恩图都有固定的数学表达式，但是已经推不出来化简式只能写个大概的表达式。

  • 内连接：多张表之间平等关系，凡是能匹配上的记录都查询出来；

  • 内连接分类：等值连接、非等值连接、自连接。

  • 外连接：多张表之间存在主副之分，主要查询主表中的数据，其余表中的数据捎带着查询，当副表的没有存在于主表的数据匹配时附表中自动填充NULL值。

  • 外连接分类：左外连接、右外连接、特殊的左外连接、特殊的右外连接…



2、数据表

在这里插入图片描述

create table `dept` (
  `DEPTNO` int(2) not null,
  `DNAME` varchar(14) default null,
  `LOC` varchar(13) default null,
  primary key (`DEPTNO`)
) engine=innodb default charset=utf8;


insert  into `dept`(`DEPTNO`,`DNAME`,`LOC`) values 
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');


create table `emp` (
  `EMPNO` int(4) not null,
  `ENAME` varchar(10) default null,
  `JOB` varchar(9) default null,
  `MGR` int(4) default null,
  `HIREDATE` date default null,
  `SAL` double(7,2) default null,
  `COMM` double(7,2) default null,
  `DEPTNO` int(2) default null,
  primary key (`EMPNO`)
) engine=innodb default charset=utf8;


insert  into `emp`(`EMPNO`,`ENAME`,`JOB`,`MGR`,`HIREDATE`,`SAL`,`COMM`,`DEPTNO`) values 
(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,null,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,null,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,null,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,null,10),
(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000.00,null,20),
(7839,'KING','PRESIDENT',null,'1981-11-17',5000.00,null,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,0.00,30),
(7876,'ADAMS','CLERK',7788,'1987-05-23',1100.00,null,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,null,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,null,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,null,10);


create table `salgrade` (
  `GRADE` int(11) default null,
  `LOSAL` int(11) default null,
  `HISAL` int(11) default null
) engine=innodb default charset=utf8;


insert  into `salgrade`(`GRADE`,`LOSAL`,`HISAL`) values 
(1,700,1200),(2,1201,1400),(3,1401,2000),
(4,2001,3000),(5,3001,9999);


3、等值连接

  • 等值连接：是内连接的一种，特点是连接的条件是等量关系。

  • SQL92：92中的等值连接是在where条件中加入两表的等量关系；缺点就是会产生笛卡尔积，大量数据查询时速率可能较慢。

  • SQL99：99中等值连接采用inner join关键词；清晰明了、查询效率高、将连接关系从where中分离出来。

  • 数学表达式：A ∩ B

案例：查询每个员工的部门名称，要求显示员工名和部门名

-- SQL92版本
select e.ename, d.dname from emp e, dept d where e.`DEPTNO` = d.`DEPTNO`

-- SQL99版本
select e.ename, d.dname from emp e inner join dept d on e.`DEPTNO` = d.`DEPTNO`

在这里插入图片描述
inner可以不写，但是为了可读性建议加上。



4、非等值连接

  • 非等值连接：是内连接的一种，特点是连接的条件是非等量关系。

  • 关键词：inner join on

  • 数学表达式：A ∩ B

案例: 找出每个员工的工资等级、要求显示员工名字、工资、工资等级

select e.`ENAME`, e.`SAL`, s.`grade` from emp e inner join 
salgrade s on e.`SAL` between s.losal and s.hisal

-- 这种写法也可以
select e.`ENAME`, e.`SAL`, s.`grade` from emp e, salgrade s 
where e.`SAL` between s.losal and s.hisal

在这里插入图片描述



5、自连接

  • 自连接：内连接的一种，特点就是自己和自己连接

  • 核心：一张表拆成两张表，用两张相同的表进行查询

  • 关键词：inner join on

  • 数学表达式：A ∩ B

案例：找出每个员工的上级领导，要求显示员工名字、上级领导名字。

select e1.ename '员工', e2.ename '上司' from 
emp e1 inner join emp e2 on e1.mgr = e2.empno

在这里插入图片描述

注意：

  • 注意员工应该是e1中的ename字段、上级是e2中的ename字段。

  • 问题：结果中少了king的结果，这也就是内连接的特点；表之间没有主副之分且只显示能匹配的字段



6、左连接

  • 左连接：以左表为主表所有数据全部展示，右表（副表）中无匹配的数据时字段值自动补NULL。

  • 核心：右表无法成功匹配的数据字段自动填充NULL

  • 关键词：left outer join on，其中outer可以省略。

  • 数学表达式：A（主表）

案例：找出每个员工的上级领导，要求显示员工名字、上级领导名字。

select e1.ename, e2.ename from emp e1 
left join emp e2 on e1.mgr = e2.empno

在这里插入图片描述

对比自连接可以看到多出一个king数据、虽然没有上司但是会自动填充NULL值



7、特殊的左连接

  • 特殊的左连接：以左表为主表同时去除掉与右表（副表）的成功匹配的部分，即inner join等值连接的部分。

  • 核心：只留下的就是主表中特有的数据，也就是左连接中右表需要填充NULL的数据

  • 关键字：left join on … where 右表.xxx字段 is null。

  • 数学表达式：A - B

案例：查询哪个员工的没有上级，显示员工名称，上级名称。

相对左连接来说就是只找到并输出king

select e1.ename as '员工', e2.ename as '上级' from emp e1 
left join emp e2 on e1.mgr = e2.empno where e2.ename is null

在这里插入图片描述



8、右连接

  • 右连接：以右表为主表，所有数据全部无条件展示；左表（副表）中无匹配的数据时字段值自动补NULL。

  • 核心：左表无法成功匹配时，查询的字段值自动填充NULL。

  • 关键词：right outer join on，其中outer可以省略。

  • 数学表达式：B（主表）

案例：查询各个部门有哪些员工，显示员工的名称，部门编号，部门名称。

select e.`EMPNO`, d.deptno, d.`DNAME` from emp e right join 
dept d on e.`DEPTNO` = d.`DEPTNO`

在这里插入图片描述



9、特殊的右连接

  • 特殊的右连接：以右表为主表，去除掉所有与左表（副表）成功匹配的数据行。

  • 核心：只保留右表（主表）独有的数据

  • 关键词：right join on … where 左表.xxx字段 is null

  • 数学表达式：B - A

案例：查询哪个部门没有员工，输出员工名称、部门编号、部门名。

select e.`ENAME`, d.deptno, d.`DNAME` from emp e right join 
dept d on e.`DEPTNO` = d.`DEPTNO` where e.`ENAME` is null

在这里插入图片描述



总结

  1. 内连接的 inner 关键词可以不写，但是为了可读性一定要写上。

  2. 外连接的 outer 关键词也可以不写，由于存在left、right关键词可以不写outer。

  3. 根据分析区分内外连接的关键并不是是否使用inner、outer关键词，而是是否存在left、right关键词。

  4. 特殊的左连接与右连接，实际上就是左连接与右连接增加了一个where判断条件。

  5. 因为可能存在少数据的情况，大多数情况下内连接很少使用；更多的是使用外连接。

  6. 左连接和右连接可以相互转化，先转化的方式就是将两表顺序对换，再将left与right关键词互改即可。

  7. 全连接以及特殊的全连接很少使用，MySQL好像并不支持全连接？



如有错误欢迎各位大佬指正；共同学习，共同进步！

Splaying
关注
专栏目录
mysql索引详解
学Java，找哪吒
07-07 10万+
MySQL的存储引擎架构将查询处理与数据的存储/提取相分离
MySQL联表的七种join理论
qq_40710190的博客
09-15 631
join的七种理论 学习sql数据库，联表查询是永远也绕不开的话题。 left join : 左连接，返回左表中所有的记录以及右表中连接字段相等的记录。 right join : 右连接，返回右表中所有的记录以及左表中连接字段相等的记录。 即使不为真，也会返回相应表的记录(看不懂没关系，下面会说) inner join : 内连接，又叫等值连接，只返回两个表中连接字段相等的行。 full join : 外连接，返回两个表中的行：left join + right join。 cross join :
mysql七种Join理论
fanghellofang的博客
07-23 329
  2018年06月04日 20:48:29 阅读数：29 编辑 在sql99语法中，增加了 join这中关联表的查询，具体有left join ,right join ,inner join。这几种查询方式组合起来大概有一下七种查询方式，如图: 这里用手机–>所属公司来具体实践一下，先查看一下表的内容:   手机数据表: 所属公司数据表: --------...
MySQL 7种Join的定义&图解&示范&结果（所有join类型）
最新发布
HumorChen的博客
06-26 2462
笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}
MySql七种join理论
code_mzh的博客
08-27 118
1.内连接 select * from A inner join on B where A.key=B.key; 2.左连接（就是左侧的无联系都会查出来） select * from A left join B on A.key=B.key where B.key is null; 右连接 select * from A right join B on A.key=B.key where A.key is null; 4.左外连接 select * from A left join .
七种join理论
qq_39643546的博客
04-28 228
7种join理论 查询： 1.分析需求，确定查询的字段来自那些表 （连接查询） 2.确定使用哪种连接查询 3.确定交叉点，即相同字段 4.基于什么判断条件 三种连接查询的区别： inner join:只会返回交集的部分 left join:以左表为基准返回左表满足的所有记录，无论右表是否为null right join:以右表为基准返回右表满足的所有记录，无论左表是否为null ...
MySql七种join理论（超详细、新手必看）
qq_43975081的博客
12-02 339
MySql七种join理论（超详细、新手必看） 文章目录MySql七种join理论（超详细、新手必看）INNER JOIN不添加ON条件添加ON条件LEFT JOINRIGHT JOINFULL OUTER JOINFULL OUTER EXCUDING INNER JOIN 先给大家看一下分别有哪七种join理论： 在了解七种join理论之前，我们可以先了解一下笛卡尔积 ： 笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的
MySQL7种join理论与实践
Free的午后
05-18 870
因为热爱所以坚持，因为热爱所以等待。熬过漫长无戏可演的日子，终于换来了人生的春天，共勉！！！ 目录 前言： 正题： 1、内连接(INNER JOIN) 2、外连接（OUTRE JOIN） 2.1 左外连接(LEFT OUTER JOIN) 2.2 右外连接(RIGHT OUTER JOIN) 2.3 满外连接(FULL OUTER JOIN) 3、UNION用法 4、7中种JOIN的实现 4.1 左上图 4.2右上图 4.3左中图 4.4中间图 4.5右中图 4.6左...
MySQL的七种Join理论
我能在河边钓一整天的鱼
05-03 768
一、内连接 求两个表共有的记录 select * from tb1_emp a inner join tb1_dept b on a.deptId = b.id; 二、左连接 求A表所有记录以及AB表共有的记录(B表没有的字段补null) select * from tb1_emp a left join tb1_dept b on ...
Mysql的7种join理论
pipizhen_的博客
03-30 200
Mysql的7种join理论 1、Mysql分层结构： （1）连接层 （2）服务层 （3）引擎层 （4）存储层 2、Mysql引擎十多种，主流的有两种：MyISAM，InnoDB 对比项 MyISAM InnoDB 主外键 不支持 支持 事务 不支持 支持 行级锁 表级锁，即使操作一条记录也会锁住表，不适合高并发 行级锁，只锁住操作的那行数据，适合高并发 缓存 只缓存索引，不缓存真实数据 不仅缓存索引，还缓存真实数据，对内存要求较高，内存的大小对性能有决定性影响 表空间 小
MySQL 七种join 理论
huang__2的博客
11-03 3262
MySQL 七种join 理论 建表语句如下 建表语句： CREATE TABLE tbl_dep(id INT(11) NOT NULL AUTO_INCREMENT, deptName VARCHAR(22) DEFAULT NULL, addr VARCHAR(22) DEFAULT NULL, PRIMARY KEY(id) )ENGINE = INNODB DEFAULT CHARSET...
mysql的7种join理论
阿官
03-05 1354
一、复习：1、创建索引的格式： 表名》user   列名》id,name,sex,email单列索引：create index idx_user_name on user(name);组合索引：create index idx_user_nameEmail on user(name,email);2、select查询顺序 from>on>join/where>group by&g...
MySQL--七种JOIN理论详解
吴声子夜歌的博客
11-24 972
七种JOIN理论 1. INNER JOIN 内连接 2. LEFT JOIN 左连接 3. RIGHT JOIN 右连接 4. OUTER JOIN 外连接 5. LEFT JOIN EXCLUDING INNER JOIN 左连接-内连接 6. RIGHT JOIN EXCLUDING INNER JOIN 右连接-内连接 7. OUTER JOIN EXCLUDING INNE...
BOM概述-window常见时间-定时器
【2023年电力EI会议】
04-19 812
3、定时器3.1、两种定时器3.2、setTimeout()定时器3.3、案例：5s后关闭广告3.4、清除定时器案例点击停止定时器3.5、setInterval()定时器3.6、两个定时器的区别setTimeout 延迟时间到了，就去调用这个回调函数，只调用一次，就结束了这个定时器setInterval 每隔这个延迟时间，就去调用这个回调函数，会调用很多次，重复调用这个函数3.7、倒计时案例1233.7、停止setInterval()定时器开启定时器停止定时器。
7种join理论
qq_42449963的博客
10-09 3503
以上7种join，从左到右从上到下分别是左连接、右连接、左连接之后去除共有部分、内连接、右连接之后去除共有部分、全连接、全连接之后去除共有部分 接下来我使用mysql数据库来建表进行解释： 首先建立表a： 之后建立表b： 请注意：表a和表b的字段名称有一个相同，另外一个不同，其中两个表中的id也是一个相同，另外一个不同，我们接下来将用这两张表来模拟以上7中join的连接情况； 1、左连接 select * from a left join b on a.id = b.id 2、右连接 select *.
七种 JOIN 理论
实践求真知
09-12 230
一七种JOIN图 二说明 a 这里有7张图，每张图对应一个join连接方式 1左连接：LEFT JOIN 2右连接：RIGHT JOIN 3内连接：INNER JOIN 4A独占的左连接：LEFT JOIN（ONLYHASA） 5B独占的右连接：RIGHT JOIN （ONLYHASB） 6外连接：OUTER JOIN 7A独占和B独占的外连接：OUTER JOIN（ONLY HASA，ONLY HASB） b每张图有3部分 1 A独占 ...
mysql数据库：7种join理论
oscaryxy
02-27 148
1：mysql的逻辑架构： 连接层（提供客户端与服务器的连接，类似于socket通信，tcp/ip通信） 服务层（完成大多数核心服务，如SQL接口，缓存的查询） 引擎层（负责mysql的数据存储和提取） 存储层（将数据存储于文件系统上，并完成与存储引擎的交互） 2：常用mysql引擎：InnoDB，MyISAM 3：SQL性能下降的原因：查询语句写的烂，索引失效，关联查询了太多的join，服务器调优及各个参数设置； 4：join语法的通用格式： 5：开发人员写的mysql语句逻辑与编译器处理时的语句逻辑
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值