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



    在这里插入图片描述

    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
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28
    • 29
    • 30
    • 31
    • 32
    • 33
    • 34
    • 35
    • 36

    在这里插入图片描述

    在这里插入图片描述

    在这里插入图片描述

    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
    
    • 1

    在这里插入图片描述

    select A.*,B.* from xin_stu_t A left outer join xin_teach_t B on A.relation_id = B.id
    
    • 1

    在这里插入图片描述

    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
    
    • 1

    在这里插入图片描述

    select A.*,B.* from xin_stu_t A right outer join xin_teach_t B on A.relation_id = B.id
    
    • 1

    在这里插入图片描述

    3. inner join 简称 join 交集

    select A.*,B.* from xin_stu_t A inner join xin_teach_t B on A.relation_id = B.id
    
    • 1

    在这里插入图片描述

    select A.*,B.* from xin_stu_t A join xin_teach_t B on A.relation_id = B.id
    
    • 1

    在这里插入图片描述

    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
    
    • 1

    在这里插入图片描述

    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
    
    • 1

    在这里插入图片描述

    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
    
    • 1

    在这里插入图片描述
    在这里插入图片描述

    2. 带连接条件的笛卡尔积变inner join

    select A.*,B.* from xin_stu_t A, xin_teach_t B where A.relation_id = B.id
    
    • 1

    在这里插入图片描述

    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,'徐州中学');
    
    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13
    • 14
    • 15
    • 16
    • 17
    • 18
    • 19
    • 20
    • 21
    • 22
    • 23
    • 24
    • 25
    • 26
    • 27
    • 28

    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后边条件的真假

  • 相关阅读:
    SpringBoot的starter到底是什么?
    [前端]前端项目,重启项目端口号改变的问题
    hadoop配置文件自检查(解决常见报错问题,超级详细!)
    鸿蒙NEXT开发实战:【网络管理-数据请求】
    测试自动化中遵循的最佳实践
    ubuntu20.04蓝牙连接airpods
    封装axios
    Outlook 邮箱使用技巧
    Eureka的介绍与使用
    2024.05.14 Diffusion 代码学习笔记
  • 原文地址:https://blog.csdn.net/weixin_43024834/article/details/132613091