SQL中的Join机制

最新推荐文章于 2021-08-13 16:26:48 发布
最新推荐文章于 2021-08-13 16:26:48 发布
阅读量722 收藏 2
点赞数 1
分类专栏： 数据库
原文链接：https://www.jianshu.com/p/9e1d3793cba6
版权

在关系型数据库中，JOIN本质上是基于涉及的两个或者多个表进行结合重构的过程点。其创造的结果可以被保存为一个表(table)或者是作为一个表来使用。这个结合的过程的基础，或者说联系点，是存在于两个表之间的共通的列。一般来说，ANSI标准的SQL定义了如下这些JOIN操作类型：

  • INNER
  • LEFT OUTER
  • RIGHT OUTER
  • FULL OUTER
  • CROSS

例表

关系型数据库通常数据关系的抽象来减少数据冗余。例如，一个Department会同多个Employ关联。

Employ表如下

LastNameDepartmentID
Rafferty31
Jones33
Heisenberg33
Robinson34
Smith34
WilliamsNull

Department表如下

DepartmentIDDepartmentName
31Sales
33Engineering
34Clerical
35Marketing

其中Employ表中的DepartmentID是Department表中的主键。将不同的表JOIN起来就是以一定的规则（例如开头我们提到的五种），将两者的信息结合起来构成一张大表。（毕竟Join过程需要耗费时间，有时候为了提高查询性能，会直接将这两个表的Join结果作为一个大表存储在数据库中，不过这会耗费更多的存储空间）。

下面是创建这些两个表的SQL指令

CREATE TABLE department
(
 DepartmentID INT,
 DepartmentName VARCHAR(20)
);

CREATE TABLE employee
(
 LastName VARCHAR(20),
 DepartmentID INT
);

INSERT INTO department VALUES(31, 'Sales');
INSERT INTO department VALUES(33, 'Engineering');
INSERT INTO department VALUES(34, 'Clerical');
INSERT INTO department VALUES(35, 'Marketing');

INSERT INTO employee VALUES('Rafferty', 31);
INSERT INTO employee VALUES('Jones', 33);
INSERT INTO employee VALUES('Heisenberg', 33);
INSERT INTO employee VALUES('Robinson', 34);
INSERT INTO employee VALUES('Smith', 34);
INSERT INTO employee VALUES('Williams', NULL);

CROSS JOIN

CROSE JOIN返回两张表的笛卡尔积，也就是不指定结合规则，让两表中的元素直接两两组合。

SELECT *
FROM employee CROSS JOIN department;

下面这个SQL指令本质是隐式的CROSE JOIN:

SELECT *
FROM employee, department;

以开头的例表为例，执行结果为：

CROSE JOIN

CROSE JOIN

CROSE JOIN没有应用任何筛选条件来控制返回的结果。当然其结果可以进一步通过WHERE来控制，从而产生等效于INNER JOIN的结果。通过这个语句用来检验数据库性能。

INNER JOIN

INNER JOIN要求在两个待JOIN的表（A和B）拥有有匹配的记录。这是一个非常用的JOIN方法，但是并非在每种情况下都是最优选择。和CROSE JOIN不同，INNER JOIN引入了predicate来指明结合的规则。查询指令最终对A中的每一行逐一和B中的各行进行比对，找到所有符合筛选条件的组合。这些组合被用来构造输出的新表。

上述过程也可以理解为对两表的笛卡尔积进行筛选后得到的结果。但是这样无疑性能会比较差，占用内存较多。实际的数据库实现中一般会采用其他的方法，如hash joins或者sorted-merge joins。

和CROSE JOIN类似，INNER JOIN也有显式和隐式两种写法。不过1992年隐式写法被deprecated掉了，也许现在有一些数据处于兼容性的考虑仍然支持这种写法。

显式的INNER JOIN会使用JOIN关键字，前面可以加INNER来指名JOIN种类，不加也可以。为了指明进行JOIN的规则，需要使用ON这个关键字。如下面这个例子:

SELECT *
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID;

隐式写法没有用到JOIN关键字，而是采用的对CROSE JOIN的结果进行筛选的方式。下面是隐式写法的一个例子：

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

上面两种写法的效果是一样的。上面例子中的查询，数据库使用两表中的Department列来进行join。来自两表的记录的DepartmentID吻合时，数据库会将LastName, DepartmentID以及DepartmentName 等两个表的属性结合起来构造出新表中的一行。如果DepartmentID不吻合，则不会产生新行。

上面的SQL语句的执行结果如下：

INNER JOIN

INNER JOIN

注意到名为William的雇员和名为Marketing的部门没有出现在上面的查询结果中。William没有关联的部门，Marketing没有关联的雇员。有时候我们不希望派车这些记录，此时就要使用OUTER JOIN了。+

注意：程序们在JOIN包含有NULL值的表时要特别注意，NULL不会和任何值匹配，包括自身（也就是NULL!=NULL)，除非显示的在筛选条中记性控制（加入对NULL的判断）。在有些数据库中，强制启用了referential integrity，保证了不会出现NULL值，此时INNER JOIN可以安全的使用。由于数据库的实现细节各有不同，依靠数据库本身特性来避免NULL的问题不是很可靠，推荐的方法是尽量避免设置NULL，而是定义一个无意义的空值来代替。如字符串用""来代表NULL。

OUTER JOIN

使用OUTER JOIN时可以保证指定表的每条记录都出现----即使没有匹配。OUER JOIN又可以分为LEFT OUTER JOIN, RIGHT OUTER JOIN 和 FULL OUTER JOIN。取决于你想要保留的表是哪一个。

OUTER JOIN不存在隐式表达法。

LEFT OUTER JOIN

表A和表B的LEFT OUTER JOIN结果中总会保留左侧的A表的所有记录，即便A中的一些行没有B中的行与之对应。此时构造的新行中，所有的原B中的列的值会被填充为NULL。

如下例（OUTER关键字可以省略）：

SELECT *
FROM employee 
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

筛选结果如下：

LEFT OUTER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

这个就好理解了，和LEFT OUTER JOIN是反着来的。如下面的例子。

SELECT *
FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

 

查询的结果如下图：

RIGH OUTER JOIN

RIGH OUTER JOIN

FULL OUTER JOIN

FULL OUTER JOIN同时保留两个表的列.

SELECT *
FROM employee FULL OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

FULL OUTER JOIN

有些数据库不支持FULL OUTER JOIN，不过你可以通过INNER JOIN加上UNION ALL来实现同样的功能。下面给出了一个例子：

SELECT employee.LastName, employee.DepartmentID,
       department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID,
       cast(NULL as varchar(20)), cast(NULL as integer)
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

UNION ALL

SELECT cast(NULL as varchar(20)), cast(NULL as integer),
       department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
    SELECT * FROM employee
             WHERE employee.DepartmentID = department.DepartmentID)

 

Self-JOIN

一个特殊的情况是要JOIN的表其实是同一个表。例如，考虑下面这个表：

Self JOIN Table

Self JOIN Table

我们想要查找出来有哪些用户是来自同一个国家。也就是要讲上面这个表和它自身以Coutry这一列为基础进行JOIN。下面是一个例子：

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

查询结果如下：

Self JOIN Result

Self JOIN Result

独L无二
关注
专栏目录
Flink Sql双流join-回撤机制导致数据重复
mtj66的博客，交流WX:SpringBreeze1104
10-12 3016
数据源：kafka topic数据类型为json 业务场景：解析json,由于数据层级比较深，而且包含数组，此处键值可能为空 所以数据就变成了双流join，甚至是多流join。 问题原因：采用left join导致数据回撤，然后同一个主键数据产生重复 是采用inner join 还是 outer join，具体区别见Flink Sql教程（5）_狄杰的博客-CSDN博客_connector.startup-mode 由于此处关联可能存在键值为空匹配不到的情况，采用了很多left join，..
Spark中的JOIN机制
最新发布
Java/Python大数据成长之路
07-16 1365
Apache Spark共提供了五种JOIN机制，其中常用的有三种：Shuffle Hash Join、Broadcast Hash Join及Sort Merge Join，它们都基于Hash Join，只不过需要在Hash Join前后进行Shuffle、Broadcast或Sort实际上，Hash Join算法来自于传统数据库，而Shuffle、Broadcast和Sort是大数据（数据仓库）在分布式场景下两者结合的产物。因此，我们也说大数据（数据仓库）是由传统数据库发展而来的。
数据库left join相关的问题
LLLLEE 的博客
02-06 893
前言 记录三个东西， 第一是left join的作用， 第二是left join中on和where的区别， 第三是left join，right join，inner join中的小表驱动大表的疑问。 left join的作用 作用：返回包括左表中的所有记录和右表中联结字段相等的记录。 下面通过例子来说明。 假设表1为： +----+------+ | id | name
left join 和 left outer join 的区别 ，full outer join
.　　　 于思索中前行
12-09 2994
通俗的讲：     A   left   join   B   的连接的记录数与A表的记录数同     A   right   join   B   的连接的记录数与B表的记录数同       A   left   join   B   等价B   right   join   A             table   A:   Field_K,   Field_A     1
sql查询过程表述
cijiasui8676的博客
04-07 1193
对性能优化比较具有参考价值[@more@]一、交叉连接（CROSS JOIN）交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，也叫笛卡尔积。例如：下面的语句1和语句2的结果是相同的。语...
数据库学习笔记：A、B两表join详解
qq_33033815的博客
11-12 1118
在笛卡尔乘积的基础上去理解。 转载自： https://www.cnblogs.com/fudashi/p/7491039.html
mysql中，A表 left join B表时，B表可能为NULL，没有对应数据，又想要筛选数据怎么办
festone000的专栏
07-07 1万+
1.直接上结论 办法： sql语句中的筛选条件不要放在where中而是放在on条件中，否则数据会被where筛选掉(适当搭配聚合函数)。 2.场景复现 以下给个示例： A表: B表: 如上图，两个表，分别记录student和各自喜欢的运行项目的表，结构简单，一目了然。 现在由于业务原因想要搜索出全班所有同学的感兴趣项目(没有的话也应该出现在结果的某行里，而不是不出现)。 即如下的展示形式: 那要怎么办呢？ 3.思路演化 3.1 先查查试试看？ 看出这两图的差别了吗？ 第一个显然直接被排除，完全无法
深入SQL的JOIN操作：构建复杂查询的桥梁
06-24
SQL的主要功能包括查询、更新、插入和删除数据库中的数据。 SQL的关键特点包括： 1. **声明性**：SQL使用声明性语法，指定要执行的操作，而不是如何执行。 2. **数据操作**：SQL提供了数据查询功能，通过`SELECT`...
SQL-JOIN的语法结构与查询结果分析.pdf
09-19
SQL JOIN 是数据库操作中用于合并两个或更多表中的数据的关键技术。它允许用户基于这些表之间的相关列来组合数据。在SQL-JOIN的语法结构与查询结果分析中，主要探讨了外连接（Outer Join）的操作，包括左外连接...
T-SQL: JOIN的几种关系
hery2002的专栏
04-23 1063
SQL Server提供了多种JOIN机制，在联机丛书FROM (Transact-SQL)中列举了四种JOIN操作.1.INNER JOIN（交集） INNER JOIN在数学上是指两个集合的交集，数学符号∩。如下图所示，图中红色部分则为INNER JOIN的结果。 例如： {1, 2} ∩ {1, 2} = {1, 2}. {1, 2} ∩ {2,
图解SQL的各种连接join
wolenski的专栏
09-14 1380
对于SQL的Join，在学习起来可能是比较乱的。我们知道，SQL的Join语法有很多inner的，有outer的，有left的，有时候，对于Select出来的结果集是什么样子有点不是很清楚。Coding Horror上有一篇文章（实在不清楚为什么Coding Horror也被墙）通过 文氏图 Venn diagrams解释了SQL的Join。我觉得清楚易懂，转过来。 假设我们有两张表，Table
MySQL的两张表的七种Join查询
热门推荐
诗水人间
04-12 13万+
SQL的语法格式如下 SELECT DISTINCT < select_list > FROM < left_table > < join_type > JOIN < right_table > ON <join_condition> WHERE < where_condition > GROUP BY &lt...
mysql中左连接的练习（同学见面会）：（A left join B on 条件）中是A表为准，B表中循环与A表进行匹配。
qq_41051690的博客
08-20 558
A left join B on 条件中是A表为准，B表中根据某个判断语句进行循环与A表进行匹配，若语句判断语句为真则拿出那一行。 （1）同学见面会 主持人：所有的男士站到舞台上，带上自己的配偶（没有的拿着写null牌子）。 思考：屌丝是否登上舞台？ 答：上，屌丝没有对应的行，用NULL表示。 结果如下： mysql中进行实践： 输出与之前分析的结果一直。 这种情况就是： 男生 left j...
SQL的四种连接-左外连接、右外连接、内连接、全连接
民族风的专栏
03-08 744
联接条件可在FROM或WHERE子句中指定，建议在FROM子句中指定联接条件。WHERE和HAVING子句也可以包含搜索条件，以进一步筛选联接条件所选的行。     联接可分为以下几类：     1、内联接（典型的联接运算，使用像 =  或 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。   
hive left [outer] join、full [outer] join注意事项
Zero小猿的博客
08-13 948
  在两表关联时候(加不加outer对结果无影响)，关联时候需要注意on和where两个关键字后面的条件，这里针对left [outer] join和full [outer] join做下说明,希望能帮到您。 文章目录left [outer] join时候的on与wherefull [outer] join时候的on与where left [outer] join时候的on与where   左表的独立条件（例如：日期分区）要放在where中，右表的独立条件（例如：日期分区）可以放在on中 -- 左表独立条件
数据库中的LEFT JOIN 个人理解
魏大大
06-20 469
首先要明白LEFT JOIN 的作用 TABLE A LEFT JOIN TABLE B 的意思指，将表A和表B拼接起来，就是按照一定的规则(自己制定)把A和B的行组合起来。 比如有以上两个表，现在有一个语句： SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders O...
数据库表的连接(Left join , Right Join, Inner Join)用法详解
u014285517的专栏
12-13 1万+
Left Join, Inner Join 的相关内容，非常实用，对于理解原理和具体应用都很有帮助！ left join 是left outer join的简写，left join默认是outer属性的。  Inner Join  Inner Join 逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表是一样的效果，所以很少用到；  ou
多表联合查询（join、union等）
学无止境
04-11 6万+
<br /><br /> <br />内部连接 inner join 两表都满足的组合<br />full outer--全连.两表相同的组合在一起，A表有，B表没有的数据（显示为null）,同样B表有<br />A表没有的显示为(null)<br />A表 left join B表--左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null<br />A表 right join B表--右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null<br /> <br />查询分析器中执行
SQLSERVER内部机制与高性能T-SQL优化指南
"高性能T-SQL文档探讨了SQL...高性能的T-SQL涉及多个层面，包括对数据库内部机制的理解、索引的有效利用、SQL语句的编写技巧，以及整体系统环境的优化。理解和掌握这些知识点对于开发高效、稳定的数据库应用至关重要。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值