深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接-天涯论坛

tw4ld6 发表于 2024-8-4 10:31:33

深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接

<div style="color: black; text-align: left; margin-bottom: 10px;">
 1、内联接（典型的联接运算，运用像 = 或 <> 之类的比较运算符）。包含相等联接和自然联接。 
 内联接运用比较运算符按照每一个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。 
 2、外联接。外联接能够是左向外联接、右向外联接或完整外边联接。 
 在 FROM子句中指定外联接时，能够由下列几组关键字中的一组指定：
 1）LEFT JOIN或LEFT OUTER JOIN 
 左向外联接的结果集包含 LEFT OUTER子句中指定的左表的所有行，而不仅是联接列所匹配的行。倘若左表的某行在右表中无匹配行，则在关联联的结果集行中右表的所有选取列表列均为空值。 
 2）RIGHT JOIN 或 RIGHT OUTER JOIN
 右向外联接是左向外联接的反向联接。将返回右表的所有行。倘若右表的某行在左表中无匹配行，则将为左表返回空值。 
 3）FULL JOIN 或 FULL OUTER JOIN
 完整外边联接返回左表和右表中的所有行。当某行在另一个表中无匹配行时，则另一个表的选取列表列包括空值。倘若表之间有匹配行，则全部结果集行包括基表的数据值。
 3、交叉联接 
 交叉联接返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接亦叫作作笛卡尔积。 
 FROM 子句中的表或视图可经过内联接或完整外边联接按任意次序指定；然则，用左或右向外联接指定表或视图时，表或视图的次序很重要。相关运用左或右向外联接摆列表的更加多信息，请参见运用外联接。 
 例子： 
 -------------------------------------------------
 a表 id name b表 id job parent_id 
 1 张3 1 23 1 
 2 李四 2 34 2 
 3 王武 3 34 4 
 a.id同parent_id 存在关系
 -------------------------------------------------- 
 1）内连接 
 select a.*,b.* from a inner join b on a.id=b.parent_id 
 结果是 
 1 张3 1 23 1 
 2 李四 2 34 2 
 2）左连接
 select a.*,b.* from a left join b on a.id=b.parent_id 
 结果是 
 1 张3 1 23 1 
 2 李四 2 34 2 
 3 王武 null 
 3）右连接 
 select a.*,b.* from a right join b on a.id=b.parent_id
 结果是 
 1 张3 1 23 1 
 2 李四 2 34 2 
 null 3 34 4 
 4）完全连接 
 select a.*,b.* from a full join b on a.id=b.parent_id 
 结果是 
 1 张3 1 23 1 
 2 李四 2 34 2 
 null 3 34 4 
 3 王武 null
 --------------------------------------------------------------------------------------------
 1、交叉连接（CROSS JOIN）
 交叉连接（CROSS JOIN）：有两种，显式的和隐式的，不带ON子句，返回的是两表的乘积，亦叫笛卡尔积。
 例如：下面的语句1和语句2的结果是相同的。
 语句1：隐式的交叉连接，无CROSS JOIN。
 SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME
 FROM ORDERS O , CUSTOMERS C
 WHERE O.ID=1;
 语句2：显式的交叉连接，运用CROSS JOIN。
 SELECT O.ID,O.ORDER_NUMBER,C.ID,
 C.NAME
 FROM ORDERS O CROSS JOIN CUSTOMERS C
 WHERE O.ID=1;
 语句1和语句2的结果是相同的，查找结果如下：
 2、内连接（INNER JOIN）
 内连接（INNER JOIN）：有两种，显式的和隐式的，返回连接表中符合连接要求和查找要求的数据行。（所说的链接表便是数据库在做查找形成的中间表）。
 例如：下面的语句3和语句4的结果是相同的。
 语句3：隐式的内连接，无INNER JOIN，形成的中间表为两个表的笛卡尔积。
 SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
 FROM CUSTOMERS C,ORDERS O
 WHERE C.ID=O.CUSTOMER_ID;
 语句4：表示的内连接，通常叫作为内连接，有INNER JOIN，形成的中间表为两个表经过ON要求过滤后的笛卡尔积。
 SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME
 FROM CUSTOMERS C INNER JOIN ORDERS O ON C.ID=O.CUSTOMER_ID;
 语句3和语句4的查找结果：
 3、外连接（OUTER JOIN）：外连不仅返回符合连接和查找要求的数据行，还返回不符合要求的有些行。外连接分三类：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。
 三者的一起点是都返回符合连接要求和查找要求（即：内连接）的数据行。区别点如下：
 左外连接还返回左表中不符合连接要求单符合查找要求的数据行。
 右外连接还返回右表中不符合连接要求单符合查找要求的数据行。
 全外连接还返回左表中不符合连接要求单符合查找要求的数据行，并且还返回右表中不符合连接要求单符合查找要求的数据行。全外连接实质是上左外连接和右外连接的数学合集（去掉重复），即“全外=左外 UNION 右外”。
 说明：左表便是在“（LEFT OUTER JOIN）”关键字左边的表。右表当然便是右边的了。在三种类型的外连接中，OUTER 关键字是可省略的。
 下面举例说明：
 语句5：左外连接（LEFT OUTER JOIN）
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
 语句6：右外连接（RIGHT OUTER JOIN）
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
 重视：WHERE要求放在ON后面查找的结果是不同样的。例如：
 语句7：WHERE要求独立。
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
 WHERE O.ORDER_NUMBER<>MIKE_ORDER001;
 语句8：将语句7中的WHERE要求放到ON后面。
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID AND O.ORDER_NUMBER<>MIKE_ORDER001;
 从语句7和语句8查找的结果来看，显然是不相同的，语句8表示的结果是难以理解的。因此呢，举荐在写连接查找的时候，ON后面只跟连接要求，而对中间表限制的要求都写到WHERE子句中。
 语句9：全外连接（FULL OUTER JOIN）。
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
 重视：MySQL是不支持全外的连接的，这儿给出的写法适合Oracle和DB2。然则能够经过左外和右外求合集来获取全外连接的查找结果。下图是上面SQL在Oracle下执行的结果：
 语句10：左外和右外的合集，实质上查找结果和语句9是相同的。
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
 UNION
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
 语句9和语句10的查找结果是相同的，如下：
 4、联合连接（UNION JOIN）：这是一种很少见的连接方式。Oracle、MySQL均不支持，其功效是：找出全外连接和内连接之间差异的所有行。这在数据分析中排错中比较常用。亦能够利用数据库的集合操作来实现此功能。
 语句11：联合查找（UNION JOIN）例句，还无找到能执行的SQL环境。
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O UNION JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
 语句12：语句11在DB2下的等价实现。还不晓得DB2是不是支持语句11呢！
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
 EXCEPT
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
 语句13：语句11在Oracle下的等价实现。
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O FULL OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID
 MINUS
 SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME
 FROM ORDERS O INNER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;
 查找结果如下：
 5、自然连接（NATURAL INNER JOIN）：说真的，这种连接查找无存在的价值，既然是SQL2标准中定义的，就给出个例子瞧瞧吧。自然连接无需指定连接列，SQL会检测两个表中是不是相同名叫作的列，且假设她们在连接要求中运用，并且在连接要求中仅包括一个连接列。不准许运用ON语句，不准许指定表示列，表示列只能用*暗示（ORACLE环境下测试的）。针对每种连接类型（除了交叉连接外），均可指定NATURAL。下面给出几个例子。
 语句14：
 SELECT *
 FROM ORDERS O NATURAL INNER JOIN CUSTOMERS C;
 语句15：
 SELECT *
 FROM ORDERS O NATURAL LEFT OUTER JOIN CUSTOMERS C;
 语句16：
 SELECT *
 FROM ORDERS O NATURAL RIGHT OUTER JOIN CUSTOMERS C;
 语句17：
 SELECT *
 FROM ORDERS O NATURAL FULL OUTER JOIN CUSTOMERS C;
 6、SQL查找的基本原理：两种状况介绍。
 第1、单表查找：按照WHERE要求过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；而后按照SELECT的选取列选取相应的列进行返回最后结果。
 第2、两表连接查找：对两表求积（笛卡尔积）并用ON要求和连接连接类型进行过滤形成中间表；而后按照WHERE要求过滤中间表的记录，并按照SELECT指定的列返回查找结果。
 第3、多表连接查找：先对第1个和第二个表根据两表连接做查找，而后用查找结果和第三个表做连接查找，以此类推，直到所有的表都连接上为止，最后形成一个中间的结果表，而后按照WHERE要求过滤中间表的记录，并按照SELECT指定的列返回查找结果。
 理解SQL查找的过程是进行SQL优化的理论依据。
 7、ON后面的要求（ON要求）和WHERE要求的区别：
 ON要求：是过滤两个链接表笛卡尔积形成中间表的约束要求。
 WHERE要求：在有ON要求的SELECT语句中是过滤中间表的约束要求。在无ON的单表查找中，是限制理学表或中间查找结果返回记录的约束。在两表或多表连接中是限制连接形成最后中间表的返回结果的约束。
 从这儿能够看出，将WHERE要求移入ON后面是不恰当的。举荐的做法是：
 ON只进行连接操作，WHERE只过滤中间表的记录。
 8、总结
 连接查找是SQL查找的核心，连接查找的连接类型选取依据实质需求。倘若选取欠妥，非但不可加强查找效率，反而会带来有些规律错误或性能低下。下面总结一下两表连接查找选取方式的依据：
 1、查两表相关列相等的数据用内连接。
 2、 Col_L是Col_R的子集时用右外连接。
 3、 Col_R是Col_L的子集时用左外连接。
 4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
 5、求差操作的时候用联合查找。
 多个表查找的时候，这些区别的连接类型能够写到一块。例如：
 SELECT T1.C1,T2.CX,T3.CY
 FROM TAB1 T1
 INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)
 INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)
 LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);
 WHERE T1.X >T3.Y;
 <div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/pgc-image/06ea733452414b208cff616aec577cbe~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1723339528&x-signature=3HB4ocxxjcMbgZPdolSNDKnECjM%3D" style="width: 50%; margin-bottom: 20px;"></div>
</div>

lanbingkj 发表于 2024-8-30 15:51:45

你的努力一定会被看见，相信自己，加油。

外链专员 发表于 2024-9-2 12:07:49

我完全同意你的看法，期待我们能深入探讨这个问题。

m5k1umn 发表于 2024-10-2 23:24:49

谷歌外链发布 http://www.fok120.com/

4zhvml8 发表于 2024-10-17 17:11:24

说得好啊！我在外链论坛打滚这么多年，所谓阅人无数，就算没有见过猪走路，也总明白猪肉是啥味道的。

4zhvml8 发表于 2024-10-22 07:27:18

认真阅读了楼主的帖子，非常有益。

wrjc1hod 发表于 2024-11-11 06:41:40

请问、你好、求解、谁知道等。

页: [1]

天涯论坛's Archiver

深入理解SQL的四种连接-左外连接、右外连接、内连接、全连接