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

这篇具有很好参考价值的文章主要介绍了Mysql表关联简单介绍(inner join、left join、right join、full join不支持、笛卡尔积)。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。


mysql 表关联,Mysql,mysql,数据库

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,'徐州中学');
	 

mysql 表关联,Mysql,mysql,数据库

mysql 表关联,Mysql,mysql,数据库

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

3. inner join 简称 join 交集

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

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

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

mysql 表关联,Mysql,mysql,数据库

6、7. full outer join 简写为full join(mysql不支持,oracle可以)

mysql 表关联,Mysql,mysql,数据库

2. 笛卡尔积 A * B

1. 不带连接条件的笛卡尔积

select A.*,B.* from xin_stu_t A, xin_teach_t B

mysql 表关联,Mysql,mysql,数据库
mysql 表关联,Mysql,mysql,数据库

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

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

mysql 表关联,Mysql,mysql,数据库

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;

mysql 表关联,Mysql,mysql,数据库

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;

mysql 表关联,Mysql,mysql,数据库

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;

mysql 表关联,Mysql,mysql,数据库

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;
mysql 表关联,Mysql,mysql,数据库

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后边条件的真假文章来源地址https://www.toymoban.com/news/detail-798985.html

到了这里,关于Mysql表关联简单介绍(inner join、left join、right join、full join不支持、笛卡尔积)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL Inner Join 和 Left Join 详解

    Inner Join Inner Join 是一种 SQL 查询语句,用于在两个或多个表中查找共有的行。Inner Join 的语法如下: SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name = table2.column_name; 其中, table1 和 table2 是需要连接的表, column_name 是需要返回的列名。 ON 用于指定连接条件。

    2024年02月05日
    浏览(50)
  • MySQL - Left Join和Inner Join的效率对比,以及优化

    最近在写代码的时候,遇到了需要多表连接的一个问题,初始sql类似于: 这样的多个left join组合,总觉得这种写法是有问题的,后续使用inner join发现速度要比left join快一些 关于left join的概念,大家是都知道的(返回左边全部记录,右表不满足匹配条件的记录对应行返回nul

    2024年02月03日
    浏览(52)
  • mysql的两张表left join 进行关联后,索引进行优化案例

    1.表1没加索引  2.表2没加索引 3.查看索引 1.表1添加索引   2.表2添加索引   3.查看  

    2024年02月12日
    浏览(46)
  • 工作的记录 left join on and 和 inner join on and的多条件查询区别

    eElasticsearch使用——结合MybatisPlus使用ES es和MySQL数据一致性 结合RabbitMQ实现解耦-CSDN博客 关于相对定位与绝对定位的区别_相对定位和绝对定位的区别-CSDN博客 display:flex(弹性盒子布局)详解_display: flex-CSDN博客 justify-content - CSS:层叠样式表 | MDN (mozilla.org) align-items - CSS:层叠样式

    2024年02月10日
    浏览(47)
  • hive表的全关联full join用法

    背景:实际开发中需要用到全关联的用法,之前没遇到过,现在记录一下。需求是找到两张表的并集。 全关联的解释如下; 下面建两张表进行测试 test_a表的数据如下 test_b表的数据如下; 写第一个full join 的SQL进行查询测试 查询结果显示如下; 把两个表的结果拼在一行了,

    2024年02月11日
    浏览(40)
  • sql Left Join 关联多条数据情况下只取一条数据

    存在b表中foreign_key多条关联a表中的id 需要只取b表中的一条数据(例如取最新的时间的一条) 1.解决方式:使用ROW_NUMBER () over()新增一列编号,排序后对新增列进行筛选 2.解决方式:使用listagg函数多列转一行,再结合substr函数截取最后一条

    2024年02月06日
    浏览(44)
  • 【SQL开发实战技巧】系列(三十五):数仓报表场景☞根据条件返回不同列的数据以及Left /Full Join注意事项

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2023年04月12日
    浏览(64)
  • MySQL left join 和 left outer join 区别

    先说结论: left join 和 left outer join 的结果是一致的。 我不知道各位大神是怎么测试的,网上面就说两个不一样,我A、B表都是有重复数据的,为啥结果是一样的。 表A 表B 左连接 左外连接 以下MySQL官方文档的说明(2664页),outer join是为了某些第三方程序的兼容性而存在的。 微软

    2024年02月10日
    浏览(41)
  • 【SQL开发实战技巧】系列(六):从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率,记住内外关联条件不要乱放

    【SQL开发实战技巧】系列(一):关于SQL不得不说的那些事 【SQL开发实战技巧】系列(二):简单单表查询 【SQL开发实战技巧】系列(三):SQL排序的那些事 【SQL开发实战技巧】系列(四):从执行计划讨论UNION ALL与空字符串UNION与OR的使用注意事项 【SQL开发实战技巧】系列

    2024年01月21日
    浏览(47)
  • MSQL系列(十一) Mysql实战-Inner Join算法底层原理及驱动表选择

    Mysql实战-Inner Join算法驱动表选择 前面我们讲解了B+Tree的索引结构,及Mysql的存储引擎MyISAM和InnoDB,也详细讲解下 left Join的底层驱动表 选择, 并且初步了解 Inner join是Mysql 主动选择优化的驱动表,知道索引要建立在被驱动表上 那么对于Inner join 来说, 到底什么是小表? 1.建表及测

    2024年02月07日
    浏览(37)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包