文档章节

图文并茂详解 SQL JOIN

大数据之路
 大数据之路
发布于 2013/05/31 01:56
字数 1206
阅读 6974
收藏 45

Join是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一个表中的行匹配的数据,这时我们应该考虑使用Join,本文将通过可视化图表介绍SQL中的各种常用Join特性、原理和使用场景:

1、INNER JOIN && OUTER JOIN

venn-join

2、CROSS JOIN

venn-cross-join

3、韦恩图 JOIN 全解

create table table_1 (
	 `id` INT (11) NOT NULL,
	user_name varchar(20) NOT NULL
)

create table table_2 (
	 `id` INT (11) NOT NULL,
	user_name varchar(20) NOT NULL
)


set sql_safe_updates=0;
insert into table_1 values (1,"zhangsan_1_1"),(2,"lisi_1_1"),(3,"wangmazi_1"),(1,"zhangsan_1_2"),(2,"lisi_1_2");
select * from table_1
-- DELETE from table_1
insert into table_2 values (4,"zhaoliu_2_1"),(2,"lisi_2_1"),(3,"wangmazi_2_1"),(1,"zhangsan_2"),(2,"lisi_2_2"),(4,"zhaoliu_2_2"),(3,"wangmazi_2_2")
-- DELETE from table_2
select * from table_2
SELECT * FROM table_1 t1 INNER JOIN table_2 t2 ON t1.id = t2.id;
SELECT * FROM table_1 t1 WHERE EXISTS (SELECT 1 FROM table_2 t2 WHERE t2.id = t1.id );
-- 结果集:1+2==3
-- 1、inner join: SELECT * FROM table_1 t1, table_2 t2 where t1.id = t2.id;
-- 2、anti-join: SELECT * FROM table_1 t1, table_2 t2 where t1.id != t2.id;
-- 3、cross join:select * from table_1 t1, table_2 t2; -- 不带条件等同于 cross join


SELECT * FROM table_1 t1 WHERE EXISTS (SELECT 1 FROM table_2 t2 WHERE t2.id = t1.id );
1   zhangsan_1_1
2   lisi_1_1
3   wangmazi_1
1   zhangsan_1_2
2   lisi_1_2


SELECT * FROM table_1 t1 INNER JOIN table_2 t2 ON t1.id = t2.id;
2   lisi_1_1        2   lisi_2_1
2   lisi_1_2        2   lisi_2_1
3   wangmazi_1      3   wangmazi_2_1
1   zhangsan_1_1    1   zhangsan_2
1   zhangsan_1_2    1   zhangsan_2
2   lisi_1_1        2   lisi_2_2
2   lisi_1_2        2   lisi_2_2
3   wangmazi_1      3   wangmazi_2_2

joinTypeThumbnail

4、特殊 Join:Semi-join 和 Anti-semi-join

Semi Join 也叫半连接,Semi-join从一个表中返回的行与另一个表中数据行进行不完全联接查询(查找到匹配的数据行就返回,不再继续查找)。

Anti-semi-join从一个表中返回的行与另一个表中数据行进行不完全联接查询,然后返回不匹配的数据。

不同于其他的联接运算,Semi-join和Anti-semi-join没有明确的语法来实现,但Semi-join和Anti-semi-join在RDBMS中有多种应用场合。我们可以使用EXISTS/IN子句来实现Semi-join查询,Not EXISTS来实现Anti-semi-join。

# select dname from dept where exists( select null from emp where emp.deptno=dept.deptno)
for x in ( select * from dept )
loop
	for y in ( select * from emp)
	loop
		if ( x.deptno == y.deptno )
			OutPut_Record(x.dname)
            # 多在子查询exists中使用,对外部row source的每个键值,查找到内部row source匹配的第一个键值后就返回,如果找到就不用再查找内部row source其他的键值了。
			Break;
		End if
	end loop
end loop

# select ename,deptno from emp,dept where emp.deptno!=dept.deptno
# 多用于!= not in 等查询;如果找到满足条件(!= not in)的不返回,不满足条件(!= not in)的返回。和 join 相反。
# for example: nested loop anti-join 
for x in ( select * from emp )
loop
	for y in ( select * from dept)
	loop
		if ( x.deptno != y.deptno )
			OutPut_Record(x.dname,y.deptno)
		End if
	end loop
end loop

具体看这个 case 就能体会其中的差别,以及需要注意的坑:

[0] Hive 中的 LEFT SEMI JOIN 与 JOIN ON 的前世今生

https://my.oschina.net/leejun2005/blog/188459

 

注:

1、mysql并不支持 full outer join  

2、outer、inner 关键字在常见数据库SQL中一般可以省略

3、在早期HIVE版本中,并不支持 Exist/IN 子查询,而是在 0.5 之后提供了 left semi join 语法

4、注意 Anti-semi-join 与 anti-join 区别

5、select * from A,B; 不带 where 或者 join 等同于 cross join,带 where 或   等同于 inner join

6、left semi join 中最后 select 的结果只许出现左表,因为右表只有 join key 参与关联计算了,而 join on 默认是整个关系模型都参与计算了

7、对待右表中重复key的处理方式差异:因为 left semi join 是 in(keySet) 的关系,遇到右表重复记录,左表会跳过,而 join on 则会一直遍历做 key 内 cross join

REF:

[0] 看漫画就能学SQL,简直太cool了

https://mp.weixin.qq.com/s/t8JCJSP__qh11U2zl8hCeQ

[1] Say NO to Venn Diagrams When Explaining JOINs

https://blog.jooq.org/2016/07/05/say-no-to-venn-diagrams-when-explaining-joins/

[2] SQL Server Join Types Poster (Version 2)

http://stevestedman.com/2015/03/sql-server-join-types-poster-version-2/

[3] SQL Join的一些总结

http://www.cnblogs.com/rush/archive/2012/03/27/2420246.html

[4] 简单介绍join,outer-join,semi-join,anti-join的区别

http://blog.csdn.net/wanghai__/article/details/6426941

[5] Spark SQL 之 Join 实现

https://cloud.tencent.com/community/article/709612

© 著作权归作者所有

大数据之路
粉丝 1605
博文 514
码字总数 333288
作品 0
武汉
架构师
私信 提问
加载中

评论(5)

EOF
EOF

引用来自“xrzs”的评论

引用来自“Iuranus”的评论

对于FULL OUTER JOIN,跟select fromA, B…两者在应用场景和效率上有什么区别?

你可以 explain 看看他们的执行计划。

select from A, B是隐式inner join ,跟外连接没关系,另外 mysql 不支持full outer join
刀剑分天下
刀剑分天下
good
大数据之路
大数据之路 博主

引用来自“Iuranus”的评论

对于FULL OUTER JOIN,跟select fromA, B…两者在应用场景和效率上有什么区别?

你可以 explain 看看他们的执行计划。
Ged-J
Ged-J
经典
Iuranus
Iuranus
对于FULL OUTER JOIN,跟select fromA, B…两者在应用场景和效率上有什么区别?
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
连接的种类详解

外连接包括左向外联接、右向外联接和完整外部联接。 左连接:left join 或 left outer join 左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行,不仅是连接列所匹配的行。如果左表...

博为峰教研组
2016/12/18
10
0
详解弹性域

物料的辅助属性,是弹性域,即是根据最终用户的实际业务需要,动态生成的,各个客户的辅助属性维度并不相同。 为了支持其动态性,相关表格设计比较灵活,初接触,理解起来比较困难。 本帖,从...

51GT
2017/10/16
0
0
Hive SQL 编译过程详解

Hive是基于Hadoop的一个数据仓库系统,在各大公司都有广泛的应用。美团数据仓库也是基于Hive搭建,每天执行近万次的Hive ETL计算流程,负责每天数百GB的数据存储和分析。Hive的稳定性和性能对...

大数据之路
2014/05/21
1K
0
Oracle 数据库的内外连接区别及外连接详解

内连接(inner join):返回2个表中完全符合条件的记录,结果集中每个表的字段内容均来自各自的表; 外连接(outer join):返回2个表中完全符合条件的记录,再加上2个表中各自的记录,结果集...

尉迟逍遥
2015/09/24
310
0

没有更多内容

加载失败,请刷新页面

加载更多

PostgreSQL参数search_path影响及作用

search_path稍微熟悉PG就会用到,用法这里就不必讲,本篇主要讲它在程序里怎样处理。 1、GUC参数定义 这是个 config_string 参数 {{"search_path", PGC_USERSET, CLIENT_CONN_STATEMENT,...

有理想的猪
57分钟前
8
0
Qt程序各个平台打包发布及安装程序大全

本文链接:https://blog.csdn.net/zhengtianzuo06/article/details/78468111 通用: 1.准备图标 图标可以直接使用一般格式的图片制作, 比如jpg, png等 推荐使用Photoshop制作原始图 推荐使用I...

shzwork
今天
4
0
springboot2.0 maven打包分离lib,resources

springboot将工程打包成jar包后,会出现获取classpath下的文件出现测试环境正常而生产环境文件找不到的问题,这是因为 1、在调试过程中,文件是真实存在于磁盘的某个目录。此时通过获取文件路...

陈俊凯
今天
22
0
BootStrap

一、BootStrap 简洁、直观、强悍的前端开发框架,让web开发更加迅速、简单 中文镜像网站:http://www.bootcss.com 用于开发响应式布局、移动设备优先的WEB项目 1、使用boot 创建文件夹,在文...

wytao1995
今天
10
0
小知识:讲述Linux命令别名与资源文件的区别

别名 别名是命令的快捷方式。为那些需要经常执行,但需要很长时间输入的长命令创建快捷方式很有用。语法是: alias ppp='ping www.baidu.com' 它们并不总是用来缩短长命令。重要的是,你将它...

老孟的Linux私房菜
今天
18
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部