【数据库内核】Join的种类和用法

目录

概述

测试的表数据

Join的种类

一、Inner Join

二、Left Join

三、Right Join

四、Full Join

五、LEFT JOIN EXCLUDING INNER JOIN

六、RIGHT JOIN EXCLUDING INNER JOIN

七、FULL OUTER JOIN EXCLUDING INNER JOIN

八、CROSS JOIN

九、SEMI JOIN

十、ANTI JOIN

十一、ANTI NA JOIN

结论

---

概述

​

JOIN是我们这些整天与数据打交道的人绕不开的一个词，不管是在传统的关系型数据库，还是在大数据领域的数据仓库/数据湖中，JOIN都是常客。特别是对于OLAP业务而言，几乎每个查询都需要用JOIN来建立表之间的关系，地位非常之重要。本文就来简单讲解一些主要的join种类和含义。

 

测试的表数据

 

drop table left_tablecreate table left_table(id int, name varchar(20))insert into left_table values(1, 'Dog')insert into left_table values(2, 'Cat')insert into left_table values(3, 'Pig')insert into left_table values(null,null)​drop table right_tablecreate table right_table(id int, name varchar(20))insert into right_table values(1, 'Squirrel')insert into right_table values(2, 'Rabbit')insert into right_table values(4, 'Whale')insert into right_table values(null,null)

 

左表的数据

 

右表的数据

 

 

Join的种类

 

一、Inner Join

SQL语义:

INNER JOIN 一般被译作内连接。内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  INNER JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID

 

Oracle计划示意图:

 

结果显示如下图:

 

二、Left Join

SQL语义:

LEFT JOIN 一般被译作左连接，也写作 LEFT OUTER JOIN。左连接查询会返回左表（表 A）中所有记录，不管右表（表 B）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  LEFT JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID

 

Oracle计划示意图:

 

结果显示如下图:

 

三、Right Join

SQL语义:

RIGHT JOIN 一般被译作右连接，也写作 RIGHT OUTER JOIN。右连接查询会返回右表（表 B）中所有记录，不管左表（表 A）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  RIGHT JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID

 

Oracle计划示意图:

 

结果显示如下图:

 

四、Full Join

SQL语义:

Full Join又叫FULL OUTER JOIN 一般被译作外连接、全连接，实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  FULL JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID

 

Oracle计划示意图:

 

结果显示如下图:

 

五、LEFT JOIN EXCLUDING INNER JOIN

SQL语义:

返回左表有但右表没有关联数据的记录集。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  LEFT JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID WHERE  T2.ID IS NULL

 

Oracle计划示意图:

 

结果显示如下图:

 

六、RIGHT JOIN EXCLUDING INNER JOIN

SQL语义:

返回右表有但左表没有关联数据的记录集。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  RIGHT JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID WHERE  T1.ID IS NULL

 

Oracle计划示意图:

 

结果显示如下图:

 

七、FULL OUTER JOIN EXCLUDING INNER JOIN

SQL语义:

返回左表和右表里没有相互关联的记录集。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1  FULL JOIN RIGHT_TABLE T2 ON T1.ID = T2.ID WHERE  T1.ID IS NULL OR T2.ID IS NULL

 

Oracle计划示意图:

 

结果显示如下图:

 

八、CROSS JOIN

SQL语义:

返回左表与右表之间符合条件的记录的迪卡尔集。

 

示意图:

 

SQL示意:

 

SELECT  * FROM  LEFT_TABLE T1  CROSS JOIN RIGHT_TABLE T2

 

Oracle计划示意图:

 

结果显示如下图:

 

九、SEMI JOIN

SQL语义:

左表中的一行数据，在右表中只要找到1次就直接返回输出。不继续往后对比。节省开销。通常使用IN 或 EXISTS 作为连接条件。

 

文氏图:

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1 WHERE  T1.ID IN (  SELECT    T2.ID   FROM    RIGHT_TABLE T2   WHERE  T1.ID = T2.ID)

 

Oracle计划示意图:

 

结果显示如下图:

 

十、ANTI JOIN

SQL语义:

左表中的一行数据，在右表中没有找到匹配列，则输出。

 

文氏图:

 

 

SQL示意:

SELECT  * FROM  LEFT_TABLE T1 WHERE  NOT EXISTS (  SELECT    T2.ID   FROM    RIGHT_TABLE T2   WHERE  T1.ID = T2.ID)

 

Oracle计划示意图:

 

结果显示如下图:

 

 

十一、ANTI NA JOIN

SQL语义:

倘若子查询返回一条null值，则整个not in都变为false，即不返回任何值。

 

SQL示意:

 

SELECT  * FROM  LEFT_TABLE T1 WHERE  T1.ID NOT IN ( SELECT T2.ID FROM RIGHT_TABLE T2 )

 

Oracle计划示意图:

 

结果显示如下图:

 

 

结论

 

以上用法基本上可以覆盖各种 JOIN 查询了。顺带张贴一下 C.L. Moffatt 带 SQL 语句的图片，配合学习。

 

Join是数据库非常重要的核心技术，作为数据库研发工作者或者数据库使用者，了解Join和学习Join还是有一定好处的。后续文章会提如何让优化器选择更好的Join。让数据库的计算引擎能更快的计算结果。还有Join的三种实现算法例如(Nested Loop Join, Hash Join, Sort Merge Join)。

 

分享大数据行业的一些前沿技术和手撕一些开源库的源代码
微信公众号名称：技术茶馆
微信公众号ID    :    Night_ZW