大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
原英文文章链接:https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins
准备
表A:
-- auto-generated definition
CREATE TABLE table_a ( PK INT(10) NOT NULL PRIMARY KEY, Value VARCHAR(25) NULL ) COMMENT '表A';
表B:
-- auto-generated definition
CREATE TABLE table_b ( PK INT(10) NOT NULL PRIMARY KEY, Value VARCHAR(50) NULL ) COMMENT '表B';
表A数据:
表B数据:
下面是各种连接的韦恩图:
详细分析
1.INNER JOIN (内连接)
内连接是一种一一映射关系,就是两张表都有的才能显示出来
用韦恩图表示是两个集合的交集,如图:
实现代码:
SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value FROM table_a A INNER JOIN table_b B ON A.PK = B.PK;
查询结果:
2.LEFT JOIN (左连接)
左连接是左边表的所有数据都有显示出来,右边的表数据只显示共同有的那部分,没有对应的部分只能补空显示,所谓的左边表其实就是指放在left join的左边的表
用韦恩图表示如下:
实现代码:
SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value FROM table_a A LEFT JOIN table_b B ON A.PK = B.PK;
查询结果:
3.RIGHT JOIN(右连接)
右连接正好是和左连接相反的,这里的右边也是相对right join来说的,在这个右边的表就是右表
用韦恩图表示如下:
实现代码:
SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value FROM table_a A RIGHT JOIN table_b B ON A.PK = B.PK;
查询结果:
4.OUTER JOIN(外连接、全连接)
查询出左表和右表所有数据,但是去除两表的重复数据
韦恩图表示如下:
实现代码:
SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value FROM table_a A FULL JOIN table_b B ON A.PK = B.PK;
上面代码在mysql执行是报错的,因为mysql不支持全连接,只能用以下代码实现效果,含义是左连接+右连接+去重=全连接:
SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value FROM table_a A LEFT JOIN table_b B ON A.PK = B.PK UNION SELECT A.PK AS A_PK,A.Value AS A_Value,B.PK AS B_PK,B.Value AS B_Value FROM table_a A RIGHT JOIN table_b B ON A.PK = B.PK;
查询结果:
5.LEFT JOIN EXCLUDING INNER JOIN(左连接不包含内连接)
这个查询是只查询左边表有的数据,共同有的也不查出来
韦恩图表示如下:
实现代码:
SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A LEFT JOIN Table_B B ON A.PK = B.PK WHERE B.PK IS NULL
查询结果:
6.RIGHT JOIN EXCLUDING INNER JOIN(右连接不包含内连接)
这个查询是只查询右边表有的数据,共同有的也不查出来
韦恩图表示如下:
实现代码:
SELECT A.PK AS A_PK, A.Value AS A_Value, B.PK AS B_PK, B.Value AS B_Value FROM Table_A A RIGHT JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL
查询结果:
7.OUTER JOIN EXCLUDING INNER JOIN(外连接不包含内连接)
意思就是查询左右表各自拥有的那部分数据
韦恩图表示如下:
实现代码:
SELECT A.PK AS A_PK, A.Value AS A_Value, B.Value AS B_Value, B.PK AS B_PK FROM Table_A A FULL OUTER JOIN Table_B B ON A.PK = B.PK WHERE A.PK IS NULL OR B.PK IS NULL
由于mysql不支持full join,只能通过下面代码模拟实现:
SELECT A.PK AS A_PK, A.Value AS A_Value, B.PK AS B_PK, B.Value AS B_Value FROM table_a A LEFT JOIN table_b B ON A.PK = B.PK WHERE B.PK IS NULL UNION ALL SELECT * FROM table_a A RIGHT JOIN table_b B ON A.PK = B.PK WHERE A.PK IS NULL;
查询结果:
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/191698.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...