数据表的设计
表A:
-- auto-generated definition
CREATETABLE table_a (
PK INT(10) NOTNULLPRIMARYKEY,
ValueVARCHAR(25) NULL
) COMMENT '表A';
表B:
-- auto-generated definition
CREATETABLE table_b (
PK INT(10) NOTNULLPRIMARYKEY,
ValueVARCHAR(50) NULL
) COMMENT '表B';
表A的数据
![1af5c141eceb34006bc514686e17dca5.png](https://i-blog.csdnimg.cn/blog_migrate/028cbbad9990181253ee4ec725a43eb2.png)
表B的数据
![313b2450511374925cf7ec2f968e869f.png](https://i-blog.csdnimg.cn/blog_migrate/b183729a6e62d884ccec078089e8a75e.png)
韦恩图
![9b3072e1cbdf8a3550a38be78e4fdce9.png](https://i-blog.csdnimg.cn/blog_migrate/ba27dc5f0b9808558cac9bafd97bc02c.jpeg)
详细分析
1.INNER JOIN (内连接)
内连接是一种一一映射关系,就是两张表都有的才能显示出来
用韦恩图表示是两个集合的交集,如图:
![469039022d31643dd81cb065169ab52c.png](https://i-blog.csdnimg.cn/blog_migrate/8532bb0dad44695caa5f14d5b637052a.png)
实现代码:
SELECT A.PK AS A_PK,A.ValueAS A_Value,B.PK AS B_PK,B.ValueAS B_Value FROM table_a A INNERJOIN table_b B ON A.PK = B.PK;
查询结果:
![1670d94f6470fe630ca6172569a27eab.png](https://i-blog.csdnimg.cn/blog_migrate/e17adabf0377752be6448d54a6dd2762.png)
2.LEFT JOIN (左连接)
左连接是左边表的所有数据都有显示出来,右边的表数据只显示共同有的那部分,没有对应的部分只能补空显示,所谓的左边表其实就是指放在left join的左边的表
用韦恩图表示如下:
![df186c63c7d002f1cbb92e35d0c025f5.png](https://i-blog.csdnimg.cn/blog_migrate/afcdec8f530301141fe415395eecaae3.png)
实现代码
SELECT A.PK AS A_PK,A.ValueAS A_Value,B.PK AS B_PK,B.ValueAS B_Value FROM table_a A LEFTJOIN table_b B ON A.PK = B.PK;
查询结果:
![c1b5ea57ac8ef8b7193635a69da26c00.png](https://i-blog.csdnimg.cn/blog_migrate/b27332a1802a874de9946826ed1eaf66.png)
3.RIGHT JOIN(右连接)
右连接正好是和左连接相反的,这里的右边也是相对right join来说的,在这个右边的表就是右表
用韦恩图表示如下:
![6b87bae9608998ee6f7895b5bfb0509e.png](https://i-blog.csdnimg.cn/blog_migrate/76b6cc12a5046e5293117b8ddf0760c4.png)
实现代码:
SELECT A.PK AS A_PK,A.ValueAS A_Value,B.PK AS B_PK,B.ValueAS B_Value FROM table_a A RIGHTJOIN table_b B ON A.PK = B.PK;
查询结果:
![5730011ba2b108ceb216489432ef03f0.png](https://i-blog.csdnimg.cn/blog_migrate/233164550a889c5f21a43b6885236a93.png)
4.OUTER JOIN(外连接、全连接)
查询出左表和右表所有数据,但是去除两表的重复数据
韦恩图表示如下:
![a1f15aebe4578fda46d8d7cd410cab34.png](https://i-blog.csdnimg.cn/blog_migrate/0c7d4ab9c0921b04f95c13c9dfda47c3.png)
实现代码:
SELECT A.PK AS A_PK,A.ValueAS A_Value,B.PK AS B_PK,B.ValueAS B_Value FROM table_a A FULLJOIN table_b B ON A.PK = B.PK;
上面代码在mysql执行是报错的,因为mysql不支持全连接,只能用以下代码实现效果,含义是左连接+右连接+去重=全连接:
SELECT A.PK AS A_PK,A.ValueAS A_Value,B.PK AS B_PK,B.ValueAS B_Value FROM table_a A LEFTJOIN table_b B ON A.PK = B.PK UNIONSELECT A.PK AS A_PK,A.ValueAS A_Value,B.PK AS B_PK,B.ValueAS B_Value FROM table_a A RIGHTJOIN table_b B ON A.PK = B.PK;
查询结果:
![78d0406b8c62efaba4d50682eb9c7030.png](https://i-blog.csdnimg.cn/blog_migrate/e4588b9c408e5194f766210f007d11c2.png)
5.LEFT JOIN EXCLUDING INNER JOIN(左连接不包含内连接)
这个查询是只查询左边表有的数据,共同有的也不查出来
韦恩图表示如下
![de4258f928f230b4c8f9d74ec97723b5.png](https://i-blog.csdnimg.cn/blog_migrate/2639cd87e2e70ff77906b8327f47ff35.png)
实现代码:
SELECT A.PK AS A_PK, A.ValueAS A_Value, B.ValueAS B_Value, B.PK AS B_PK FROM Table_A A LEFTJOIN Table_B B ON A.PK = B.PK WHERE B.PK ISNULL
查询结果:
![d704aa2be5938c0bb0e6842f1baf5aeb.png](https://i-blog.csdnimg.cn/blog_migrate/0815ac63922f55372d783cf9ef92c270.png)
6.RIGHT JOIN EXCLUDING INNER JOIN(右连接不包含内连接)
这个查询是只查询右边表有的数据,共同有的也不查出来
韦恩图表示如下:
![386ad55368e8bbc0f9bfa6b2fbbd6ee8.png](https://i-blog.csdnimg.cn/blog_migrate/ce0e1e8693af044844d3be57b9a5f89f.png)
实现代码:
SELECT A.PK AS A_PK, A.ValueAS A_Value, B.PK AS B_PK, B.ValueAS B_Value FROM Table_A A RIGHTJOIN Table_B B ON A.PK = B.PK WHERE A.PK ISNULL
查询结果:
![0a9799e5da1c9077d0e976fbbd0c63a4.png](https://i-blog.csdnimg.cn/blog_migrate/bce128ad43e5874c96793650601808bc.png)
7.OUTER JOIN EXCLUDING INNER JOIN(外连接不包含内连接)
意思就是查询左右表各自拥有的那部分数据
韦恩图表示如下:
![22c398c8a87ec5d4fb6a8651197d753e.png](https://i-blog.csdnimg.cn/blog_migrate/9ed790f7b2834a3791702f6da3a4254c.png)
实现代码:
SELECT A.PK AS A_PK, A.ValueAS A_Value, B.ValueAS B_Value, B.PK AS B_PK FROM Table_A A FULLOUTERJOIN Table_B B ON A.PK = B.PK WHERE A.PK ISNULLOR B.PK ISNULL
由于mysql不支持full join,只能通过下面代码模拟实现:
SELECT A.PK AS A_PK, A.ValueAS A_Value, B.PK AS B_PK, B.ValueAS B_Value FROM table_a A LEFTJOIN table_b B ON A.PK = B.PK WHERE B.PK ISNULLUNIONALLSELECT * FROM table_a A RIGHTJOIN table_b B ON A.PK = B.PK WHERE A.PK ISNULL;
查询结果:
![92e6d6f13cc6a4d7b90d9de34f2abc58.png](https://i-blog.csdnimg.cn/blog_migrate/883e5c44db2fba684dff10cd53f595af.png)