图文并茂详解 SQL JOIN

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

1、INNER JOIN && OUTER JOIN

2、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

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:

[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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏面朝大海春暖花开

mybatis递归,一对多代码示例

由于只有这么两级,可以不用使用递归,直接查询父集,之后foreach查询子集放入对应的list集合。

1931
来自专栏PHP在线

mysql

cross join :笛卡尔交集。 Inner join :内连接。 left join :只要满足左边表的需求就可以了,右表有无都可以。 right ...

3698
来自专栏全栈工程师成长之路

深入浅出后端开发(SQL指令笔记)

3377
来自专栏AhDung

【SQL】分配函数一枚[AllotToTable]

像这种分配法我估计在多种场景都有在用,它应该有个名堂的,只是我不知道,还请知道的猿友告知,谢谢。

1291
来自专栏章鱼的慢慢技术路

SQL数据查询之——单表查询

2375
来自专栏Jackson0714

【T-SQL基础】02.联接查询

3939
来自专栏乐沙弥的世界

SQL基础-->分组与分组函数

使用group by column1,column2,..按columm1,column2进行分组,即column1,column2组合相同的值为一个组

1382
来自专栏Golang语言社区

mysql的空值与NULL的区别

Mysql数据库是一个基于结构化数据的开源数据库。SQL语句是MySQL数据库中核心语言。不过在MySQL数据库中执行SQL语句,需要小心两个陷阱。   陷阱一...

2837
来自专栏机器学习算法与Python学习

SQL Server 学习笔记

之前学过一点数据库但由于一直没有使用忘得差不多了,最近重新复习一下相关的知识,把基本的语法YOU又看了一遍,为了强化记忆在写一遍~~~~~~ ? 基本的 se...

37915
来自专栏java达人

有哪些常用的sql语句

首行当然是最基本的增删查改啦,其中最重要的是查。 还有就是一些要注意的地方,就是SQL语句对大小写不敏感,语句中列名对应的值要用单引号''括起来不是双引号。...

19610

扫码关注云+社区

领取腾讯云代金券