Marvin's Blog【程式人生】

Ability will never catch up with the demand for it

18 Aug 2020

SQL的各种Join操作

SQL数据库系统是使用表来存储数据,为了使表的数据存储达到最高效,最好在同一个表里面保存同性质的数据。所谓同性质数据,是指这些数据可以分为若干个栏,并且每栏中的数据的增长性比较一致。这样可以保证数据存储比较紧实,避免空洞,提升查询效率。这些要求通过各种范式(Database normalization)体现出来。

既然把数据分散在各个表中,那么在使用的时候就有必要把各个表中的数据联合起来使用。在SQL中,表与表之间的关系是通过两种操作来体现的,一种是join,另一种是union。join主要是把不同表中的栏拼接起来,形成一个更大的行。而Union主要是把不同表中的行直接拼接在一起,形成更多行。 join比union要复杂一些,虽然join的目的主要是把栏拼接起来,但是它也会影响查询结果中的行的数目。

如果去图片搜索引擎上搜索一下“SQL JOIN”,就会看到很多示意图,介绍不同类型的join。Quora上面的这篇文章What is the difference between union and join in DBMS? 更是鲜明地指出了join和union的区别。

SQL的join可以分为几类:

  • inner join
  • left join
  • right join
  • full outer join
  • cross join

理解join的一个关键点是,表与表之间的关系需要通过某个栏来进行表述。也就是说先从表A选一个栏m,然后从表B选一个栏n,然后把m和n进行集合运算。以inner join为例,就是找出m和n中相同的值,然后根据这些值生成相应的行,然后行中的栏是表A和表B中的栏拼接起来的。用SQL表示的话就是下面这样子:

SELECT * FROM A INNER JOIN B ON A.m=B.n;

但是和数学上的集合运算不同的是,inner join会产生重复的行。如果A中的m有一行值为2,然后B中的n有两行值的2,那么生成的结果中值为2的行数就会有两行。如果A中的m有两行值为2,而B中的n有三行值为2,那么生成的结果中值为2的行数就为六,也就是A中值为2的m的行数乘以B中值为2的n的行数。

所以主键一定要去重,避免产生重复多余的行。

理解了inner join,其实也就理解了left join,right join和full join。left join就是在inner join的结果之上,把A表中m栏没有在inner join匹配成功的行也放到结果里面;right join就是把B表中n栏没有在inner join匹配成功的行也放到结果里面。然后full outer join就是把A表中m栏没有在inner join匹配成功的行,以及B表n栏中没有在inner join匹配成功的行都放到最终的结果中。

  • left join示例:SELECT * FROM A LEFT JOIN B on A.m=B.n;
  • right join示例:SELECT * FROM A RIGHT JOIN B ON A.m=B.n;
  • full outer join示例:SELECT * FROM A FULL OUTER JOIN B ON A.m=B.n

通过指定WHERE语句,可以选出:

  • A表中m栏没有在inner join匹配成功的行:SELECT * FROM A LEFT JOIN B ON A.m=B.n WHERE B.n IS NULL
  • B表中n栏没有在inner join匹配成功的行:SELECT * FROM A RIGHT JOIN B ON A.m=B.n WHERE A.m IS NULL
  • 上面两者的合体:SELECT * FROM A FULL OUTER JOIN B ON A.m=B.n WHERE A.m IS NULL OR B.n IS NULL

join中产生的重复行可以通过GROUPBY来去重,参考Left Join without duplicate rows from left table

最后还有一种cross join操作,也就是不通过某个栏进行匹配,而直接把两个表的所有行进行联结。假设表A有M行,表B有N行,那么最后产生的结果是MxN行数据。

(本篇完)

Categories

Tags

comments powered by Disqus