Mysql表关联简单介绍（inner join、left join、right join、full join不支持、笛卡尔积）

2024-06-04 4312阅读

文章目录

0. 交集、并集、差集含义说明
1. 简单演示上图七种情况
- 0. A、B表数据准备
- 1. left outer join 简称 left join 左表所有数据，右表关联数据，没有的以null填充
- 2. right outer join 简称 right join，右表所有数据，左表关联数据，没有的以null填充
- 3. inner join 简称 join 交集
- 4. A left join B where B.key is null，A、B的差集是 A-B = 1
- 5. B right join where A.key is null，B、A的差集是 B-A = 4
- 6、7. full outer join 简写为full join（mysql不支持,oracle可以）
- 2. 笛卡尔积 A * B
- - 1. 不带连接条件的笛卡尔积
  - 2. 带连接条件的笛卡尔积变inner join
  - 3. inner/left join重复数据，查询会扩散，造成笛卡尔积现象
  - - 3.1 inner join 重复数据结果集会扩散
    - 3.2 inner join 重复数据，结果集去重后同inner join不重复数据
    - 3.3 left join 重复数据结果集会扩散
    - 3.4 left join 重复数据，结果集去重后同left join不重复数据
    - 4. a inner/left join b，没有on关键字会报错
    - 5. left join on 后 and中主表的过滤条件不会起作用
      
      0. 交集、并集、差集含义说明
      
      A 是1、2、3
      B是2、3、4
      A、B的交集是A∩B = 2、3
      A、B的并集是 AUB = 1、2、3、4
      A、B的差集是 A-B = 1
      B、A的差集是 B-A = 4
      
      1. 简单演示上图七种情况
      
      0. A、B表数据准备
```
CREATE TABLE `xin_stu_t` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `relation_id` bigint DEFAULT NULL COMMENT '外键, 记录教师id',
  `student_name` varchar(30) DEFAULT NULL COMMENT '姓名',
  `student_age` bigint DEFAULT NULL COMMENT '年龄',
  `school` varchar(300) DEFAULT NULL COMMENT '学校',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `xin_s_relation_id` (`relation_id`),
  KEY `xin_s_student_name` (`student_name`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生表';
CREATE TABLE `xin_teach_t` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `teacher_name` varchar(30) DEFAULT NULL COMMENT '教师姓名',
  `teacher_age` bigint DEFAULT NULL COMMENT '教师年龄',
  `school` varchar(300) DEFAULT NULL COMMENT '学校',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='教师表';
INSERT INTO lelele.xin_stu_t (relation_id,student_name,student_age,school) VALUES
	 (NULL,'尤仁义1',11,'徐州中学'),
	 (1,'尤仁义2',12,'徐州中学'),
	 (NULL,'朱有理1',11,'徐州中学'),
	 (2,'朱有理2',12,'徐州中学'),
	 (2,'朱有理3',13,'徐州中学'),
	 (3,'宋昆明1',11,'徐州中学'),
	 (3,'宋昆明2',12,'徐州中学'),
	 (9,'宋昆明3',13,'徐州中学');
INSERT INTO lelele.xin_teach_t (teacher_name,teacher_age,school) VALUES
	 ('王翠花1',31,'徐州中学'),
	 ('王翠花2',31,'徐州中学'),
	 ('王翠花3',33,'徐州中学'),
	 ('王翠花4',34,'徐州中学'),
	 ('王翠花5',35,'徐州中学');
	 
```
      1. left outer join 简称 left join 左表所有数据，右表关联数据，没有的以null填充
```
select A.*,B.* from xin_stu_t A left join xin_teach_t B on A.relation_id = B.id
```
```
select A.*,B.* from xin_stu_t A left outer join xin_teach_t B on A.relation_id = B.id
```
      2. right outer join 简称 right join，右表所有数据，左表关联数据，没有的以null填充
```
select A.*,B.* from xin_stu_t A right join xin_teach_t B on A.relation_id = B.id
```
```
select A.*,B.* from xin_stu_t A right outer join xin_teach_t B on A.relation_id = B.id
```
      3. inner join 简称 join 交集
```
select A.*,B.* from xin_stu_t A inner join xin_teach_t B on A.relation_id = B.id
```
```
select A.*,B.* from xin_stu_t A join xin_teach_t B on A.relation_id = B.id
```
      4. A left join B where B.key is null，A、B的差集是 A-B = 1
```
 select A.*,B.* from xin_stu_t A left join xin_teach_t B on A.relation_id = B.id where B.id is null
```
      5. B right join where A.key is null，B、A的差集是 B-A = 4
```
select A.*,B.* from xin_stu_t A right join xin_teach_t B on A.relation_id = B.id where A.id is null
```
      6、7. full outer join 简写为full join（mysql不支持,oracle可以）
      
      2. 笛卡尔积 A * B
      
      1. 不带连接条件的笛卡尔积
```
select A.*,B.* from xin_stu_t A, xin_teach_t B
```
      2. 带连接条件的笛卡尔积变inner join
```
select A.*,B.* from xin_stu_t A, xin_teach_t B where A.relation_id = B.id
```
      3. inner/left join重复数据，查询会扩散，造成笛卡尔积现象
      
      造数据
```
drop table if exists  xin_stu_t_bak;
drop table if exists xin_teach_t_bak;
create table `xin_stu_t_bak` (
  `id` bigint not null COMMENT '主键',
  `relation_id` bigint default null COMMENT '外键, 记录教师id',
  `student_name` varchar(30) default null COMMENT '姓名',
  `student_age` bigint default null COMMENT '年龄',
  `school` varchar(300) default null COMMENT '学校'
);
CREATE TABLE `xin_teach_t_bak` (
  `id` bigint NOT NULL COMMENT '主键',
  `teacher_name` varchar(30) DEFAULT NULL COMMENT '教师姓名',
  `teacher_age` bigint DEFAULT NULL COMMENT '教师年龄',
  `school` varchar(300) DEFAULT NULL COMMENT '学校'
);
insert into xin_stu_t_bak select * from  xin_stu_t;
insert into xin_teach_t_bak select * from  xin_teach_t;
INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) values (1,'王翠花1',31,'徐州中学');
INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) VALUES (1,'王翠花1',31,'徐州中学');
INSERT INTO lelele.xin_teach_t_bak (id,teacher_name,teacher_age,school) VALUES (2,'王翠花2',31,'徐州中学');
```
      3.1 inner join 重复数据结果集会扩散
      
      select A.,B. from xin_stu_t_bak A inner join xin_teach_t_bak B on A.relation_id = B.id order by A.id;
      
      3.2 inner join 重复数据，结果集去重后同inner join不重复数据
      
      select distinct A.,B. from xin_stu_t_bak A inner join xin_teach_t_bak B on A.relation_id = B.id order by A.id;
      
      3.3 left join 重复数据结果集会扩散
      
      select A.,B. from xin_stu_t_bak A left join xin_teach_t_bak B on A.relation_id = B.id order by A.id;
      
      3.4 left join 重复数据，结果集去重后同left join不重复数据
      
      select distinct A.,B. from xin_stu_t_bak A left join xin_teach_t_bak B on A.relation_id = B.id order by A.id;
      
      4. a inner/left join b，没有on关键字会报错
      - on是放a、b表的连接条件，只会查出满足条件的数据
      - 若b表数据重复，会发生笛卡尔积现象
      - 若on后的关联条件不是正常的连接条件A.relation_id = B.id，而是A.relation_id=‘111’，虽然不会报错，但它也变成笛卡尔积语句了
      - from a,b where a.id = b.relation_id，相当于from a inner join b on a.id = b.relation_id
        5. left join on 后 and中主表的过滤条件不会起作用
        
        A left join B on A.relation_id = B.id and A.列名 = ‘’ and B.列名 = ‘’ 左表，A.列名 = ''这个条件不起作用
        
        引用
        
        说明：
        
        left join on 后的 and 条件中主表的条件不生效，从表的条件生效，并且从表先进行筛选后数据量可能变少，更便于与主表关联，有利于提高查询效率。
        
        建议：
        
        主表的筛选条件放在 where 中，从表的筛选条件放在 on 后的 and 中。
        
        on条件是在生成临时表时使用的条件，它不管and中的条件是否为真，都会返回左边表中的所有记录。所以说 and后加左表的条件对
        左表无影响。and后的条件只对右表产生影响（right join翻过来）
        where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left
        join的含义（必须返回左表的记录）了，条件不为真的就全部过滤掉。即对 join后的数据再进行过滤，过滤出只符合where后的条件。
        在匹配阶段 where子句的条件都不会被使用。仅在匹配阶段完成以后，wehre子句条件才会被使用。它将从匹配阶段产生的数据中检索过滤
        总结:
        
        当条件加在 left(right) join on之后，则只会影响右(左)边数据，不会影响左(右)表数据，不管条件是否成立，左(右))边数据都能正常返回；
        
        inner join and 和 inner join where and 其查询结果基本一致；
        
        当条件加在full on之后，则会根据and之后的条件分别影响左表或右表数据；
        
        当条件加在where 之后则表示对关联结果再进行筛选，此时的结果将会依赖于where后边条件的真假

相关阅读：

1、亚马逊测评买家号安全攻略，VPS使用指南，保障账号无忧测评！

2、VPS代理安卓App使用指南与优势深度解析

3、阿里云VPS个人使用指南，高效、安全、便捷，个人云托管的最佳选择！

4、VPS面板使用指南，带你探索未知神秘世界！

5、掌握虚拟专用服务，手机VPS软件使用指南

高速稳定云服务器25元起

免责声明：我们致力于保护作者版权，注重分享，被刊用文章因无法核实真实出处，未能及时与作者取得联系，或有版权异议的，请联系管理员，我们会立即处理! 部分文章是来自自研大数据AI进行生成,内容摘自(百度百科,百度知道,头条百科,中国民法典,刑法,牛津词典,新华词典,汉语词典,国家院校,科普平台)等数据,内容仅供学习参考,不准确地方联系删除处理! 图片声明：本站部分配图来自人工智能系统AI生成,觅知网授权图片,PxHere摄影无版权图库和百度，360，搜狗等多加搜索引擎自动关键词搜索配图，如有侵权的图片，请第一时间联系我们，邮箱：ciyunidc@ciyunshuju.com。本站只作为美观性配图使用,无任何非法侵犯第三方意图,一切解释权归图片著作权方,本站不承担任何责任。如有恶意碰瓷者,必当奉陪到底严惩不贷!

相关阅读

目录[+]