资讯专栏INFORMATION COLUMN

表连接类型

Ku_Andrew / 1720人阅读

摘要:右连接,取表的所有行,和表中匹配的值,没有匹配的则以值取代。条条条星型连接是指一张大的事实表和多个小表关联得出所需的数据,往四周发散,所以称为星型链接。

前言

对于关系型数据库来说,对于复杂的查询表连接是必不可少的,下面妹子就来介绍下表连接的方式

内连接(Inner Join)

Inner指的是 intersect相交,顾名思义就是取两表的交集

SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*inner-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a 
INNER JOIN `b_borrow_deductions` AS b 
ON a.per_id=b.per_id;/*51条*/
表a有63条数据,表b有10条数据,两表都有键值per_id,用INNER JOIN取交集为51条
外连接(Outer Join)

Outer指的是 union集合,就是取两表的并集
其中外连接分为3种:

Full outer join

full outer join 得到两表的乘集,也可叫做笛卡尔乘积。如果a表中的行在b表中没有对应的部分,b的部分将是 null, 反之亦然。

SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*full-outer-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a 
JOIN `b_borrow_deductions` AS b /*630条*/
取两表的乘集,则a表的行数乘以b表的行数,63*10=630(条)
Left join

左连接,取表a的所有行,和b表中匹配的值,没有匹配的则以null值取代。

SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*left-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a 
LEFT JOIN `b_borrow_deductions` AS b 
ON a.per_id=b.per_id;/*63条*/
a表有63行,b表中键值per_id在a表中都有。
Right join

右连接,取表b的所有行,和a表中匹配的值,没有匹配的则以null值取代。

SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*right-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a 
RIGHT JOIN `b_borrow_deductions` AS b 
ON a.per_id=b.per_id;/*51条*/
b表有10行,a表中有重复键值per_id,所以有51条
反连接(Anti Join)

Anti指的是 英语词根反对的意思,与inner join正好相反。
多用于!= not in 等查询;如果找到满足条件(!= not in)的不返回,不满足条件(!= not in)的返回。

SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*anti-join*/
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a ,`b_borrow_deductions` AS b 
WHERE a.per_id!=b.per_id;/*579条*/
相当于a表与b表的乘级减去a表与b表的交集,即(63*10)-51=579条
半连接(Semi Join)

半连接多在子查询exists中使用,找到存在的键值之后就不再查找内部其他的键值了。

SELECT COUNT(a.per_id) FROM `b_borrow_list` AS a;/*63条*/
SELECT COUNT(b.per_id) FROM `b_borrow_deductions` AS b;/*10条*/
/*semi-join */
SELECT COUNT(a.`per_id`) FROM `b_borrow_list` AS a 
WHERE EXISTS( 
SELECT NULL FROM `b_borrow_deductions` AS b  WHERE a.per_id=b.per_id)/*51条*/
星型连接(Star Join)

是指一张大的事实表和多个小表关联得出所需的数据,往四周发散,所以称为星型链接。
要使用星型转换,必须要求初始化参数star_transforation_enabled设为TRUE。

SELECT ...
FROM   really_big_fact f    -- 50 million rows
,      dim1 d1              -- 50 rows
,      dim2 d2              -- 100 rows
,      dim3 d3              -- 10 rows
,      dim4 d4              -- 15 rows
WHERE  f.key1 = d1.key1
AND    f.key2 = d2.key2
AND    f.key3 = d3.key3
AND    f.key4 = d4.key4
现在只有Oracle才有
表链接的方式

表链接的方式有:排序合并连接(Sort Merge join)、嵌套循环连接(Nested Logo join)、哈希连接(Mash join)、笛卡尔链接(Cross join)

参考资料

http://www.orafaq.com/tuningguide/star%20join.html

https://raoenhui.github.io/sql/2018/11/30/sqlLink/

Happy coding .. :)

文章版权归作者所有,未经允许请勿转载,若此文章存在违规行为,您可以联系管理员删除。

转载请注明本文地址:https://www.ucloud.cn/yun/35173.html

相关文章

  • MySQL explain命令详解

    摘要:使用方法列的解释值意义一旦找到了与行相联合匹配的行,就不再搜索了。这是重要的列,显示连接使用了何种类型。显示可能应用在这张表中的索引。这种情况下,可以在语句中使用来强制使用一个索引或者用来强制忽略索引。认为必须检查的用来返回请求数据的行数。 explain显示了MySQL如何使用索引来处理SELECT语句以及连接表。在分析慢查询时,可以帮助选择更好的索引和写出更优的查询语句。 使用方法...

    hufeng 评论0 收藏0
  • sql优化之逻辑优化

    摘要:优化时将外连接转换成内连接的意义答查询优化器在处理外连接操作时所需要执行的操作和时间多于内连接。子查询优化为啥要执行子查询优化答查询优化器对子查询一般采用嵌套执行方式,即对父查询中的每一行,都执行一次子查询,这样子查询会执行很多次。 在单机数据库系统中进行优化。面临的问题,比如说给定一个要query的sql语句,查询优化算法的目标就是找到查询的一个具有最小执行花费的执行计划,如果找到了...

    JohnLui 评论0 收藏0
  • MySql学习总结

    摘要:创建新用户步骤以管理员身份从本地连接到数据库服务器,可使用以下的命令实现。在语句中使用子句,可设置用户连接数据库服务器时使用的密码,这里不需要使用函数,而直接使用明文,语句会自动将设置的明文密码加密并保存到授权表中。 create database 数据库名 创建数据库 drop database if exists 数据库名 如果数据库已经存在删除数据库 use 数据库名 选...

    高胜山 评论0 收藏0
  • 【mysql的设计与优化专题(6)】mysql索引攻略

    摘要:来看这条郑州关联搜索首先考虑建单列索引在列上建立了索引。在这个结果集里先排除掉不等于郑州的记录再排除不等于的记录最后筛选出唯一的符合条件的记录。 所谓索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找,而用的最多,并且是mysql默认的就是二叉树算法 BTREE,通过BTREE算法建立索引的字段,比如扫描...

    UnixAgain 评论0 收藏0
  • MySQL性能调优与架构设计(一)—— MySQL架构组成

    摘要:物理文件组成之日志文件错误日志错误日志记录来在运行过程中所有较为严重的警告和错误信息。日志记录了所做的所有物理变更和事务信息通过日志和信息,保证了在任何情况下的事务安全性。文件也是专属与存储引擎的,主要存放表的索引相关信息。 前言 麻雀虽小,五脏俱全。mysql虽然以简单著称,但是其内部结构一点也不简单。 本章从mysql物理组成、逻辑组成、以及几个相关工具几个角度来介绍mysql的...

    hlcfan 评论0 收藏0
  • 自制小工具大大加速MySQL SQL语句优化(附源码)

    摘要:如果语句中使用了子查询集合操作临时表等情况,会给列带来很大的复杂性。会递归执行这些子查询,把结果放在临时表里。查询优化器从中所选择使用的索引。该字段显示了查询优化器通过系统收集的统计信息估算出来的结果集记录条数。 引言 优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的步骤。然...

    Yu_Huang 评论0 收藏0

发表评论

0条评论

Ku_Andrew

|高级讲师

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<