PL/SQL 知识量：16 - 57 - 244
在SELECT语句的FROM子句中，通过指定不同类型的JOIN关键词可以实现不同的表的连接方式，而在ON关键词后指定连接条件。基本连接语法如下：
SELECT column FROM join_table JOIN_TYPE join_table ON (join_condition)
简要说明：
join_table指出参与连接操作的表名。
JOIN_TYPE为连接类型，可分为3种：内部连接、外部连接和交叉连接。其中，内部连接JOIN_TYPE的形式为[INNER] JOIN；外部连接又分为左外连接，JOIN_TYPE的形式为LEFT OUTER JOIN或LEFT JOIN；右外连接，JOIN_TYPE的形式为RIGHT OUTER JOIN或RIGHT JOIN；全外连接，JOIN_ TYPE的形式为FULL OUTER JOIN或FULL JOIN。交叉连接中JOIN_TYPE的形式为CROSS JOIN。
ON (join_condition) 子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。
所谓内部连接也可以称为简单连接，它连接两个或多个表，只返回那些符合连接条件的行。其语法可简单表示如下：
SELECT 列 FROM 表1 [INNER] JION 表2 ON 表1.列=表2.列
也可以在FROM子句或WHERE子句中指定内部连接。其语法可简单表示如下：
SELECT 列 FROM 表1,表2 WHERE表1.列=表2.列
这正是简单的多表连接方式。在WHERE子句中指定的内部连接称为旧式内部连接。
由于内部连接和普通的多表连接查询效果一样，所以它在Oracle数据库中使用得相对较少。该连接的默认写法是INNER JOIN，在写内部连接时可以省略INNER关键词。
内部连接包括等值连接、不等值连接和自连接。
1、等值连接示例：
SQL> select p.name,age,b.name,price from people p inner join books b on p.name=b.owner; NAME AGE NAME PRICE ---------- ---- ---------- ---------- Susan 19 CSS 32 Bob 25 Java_1 38 Bob 25 Java_2 40 Tom 29 SQL 25 Lucy 29 Python 55
2、不等值连接就是指连接条件中使用“＞”、“＞=”、“＜=”、“＜”、“!=”、“ ＜＞”、“BETWEEN...AND”、“IN”等连接两个条件列表。示例：
SQL> select p.name,age,b.name,price from people p inner join books b on p.age < b.price/2; NAME AGE NAME PRICE ---------- ---- ---------- ---------- Bob 25 Python 55 Jeff 27 Python 55 Susan 19 Java_2 40 Susan 19 Python 55
3、自连接示例：
SQL> select p1.* from people p1 inner join people p2 on p2.name='Bob' and p1.height>p2.height; ID NAME AGE SEX HEIGHT WEIGHT ---- ---------- ---- ------ ------ ------ 2 Jeff 27 male 176 82 5 Tom 29 male 188 95
自然连接是一种比较特殊的等值连接，与等值连接不同的是，它将表中具有相同名称的列自动进行记录匹配，而不必指定任何同等连接条件。
自然连接自动判断相同名称的列，而后形成匹配。虽然可以指定查询结果包括哪些列，但不能人为地指定哪些列被匹配。连接后两个表中相同的列只保留一个。
SQL> select * from books; ID NAME PRICE OWNER ---------- ---------- ---------- ----- 1 CSS 32 Susan 2 Java_1 38 Bob 3 Java_2 40 Bob 4 SQL 25 Tom 5 Python 55 Lucy SQL> select * from sale; NAME NUM ---------- ------ CSS 2234 Java_1 256 Java_2 651 SQL 85 SQL> select * from books natural join sale; NAME ID PRICE OWNER NUM ---------- ---------- ---------- ----- ------ CSS 1 32 Susan 2234 Java_1 2 38 Bob 256 Java_2 3 40 Bob 651 SQL 4 25 Tom 85
NATURAL JOIN是自然连接的关键词。
仅当两个表中都至少有一行符合连接条件时，内部连接才返回行。内部连接消除了与另一个表中的行不匹配的行。而外部连接会返回FROM子句中提到的至少一个表或视图中的所有行，只要这些行符合任何WHERE或HAVING搜索条件。将检索通过左外连接引用的左表中的所有行，以及通过右外连接引用的右表中的所有行。在全外连接中，将返回两个表的所有行。
外部连接分为左外连接、右外连接和全外连接：
左外连接：LEFT OUTER JOIN或LEFT JOIN，被称为左向外连接。使用左外连接的查询，包含了左边表中的全部记录，右边表则只返回符合连接条件的记录。如果左边表的某行记录在右边表中没有匹配项，则在返回结果中右边表的对应处为空。
右外连接：RIGHT OUTER JOIN或RIGHT JOIN，被称为右向外连接。它与左外连接相反，将右边表中所有的数据与左边表进行匹配，返回的结果包含了匹配成功的记录，也包含了右边表中未匹配成功的记录，并在其左边表对应的列处补空值。
全外连接：FULL OUTER JOIN或FULL JOIN，返回所有匹配成功的记录，除了返回左边表未匹配成功的记录外，也返回右边表未匹配成功的记录。
与内部连接相比，左外连接除了包含两个表的匹配行外，还包含了FROM子句中JOIN关键词左边表的不匹配行。左外连接实际上可以表示如下：
左外连接 = 内部连接 + 左边表中失配的元组
示例：
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select * from sale; NAME NUM ---------- ------ CSS 2234 Java_1 256 Java_2 651 SQL 85 C++ 8455 C 1208 6 rows selected SQL> select * from books left join sale on books.name=sale.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 3 CSS 50 CSS 2234 1 SQL 50 SQL 85 5 JavaScript 4 Java 2 MySQL 32 6 Python 32 6 rows selected
缺少的右边表中的列值用NULL表示。
与内部连接相比，右外连接除了包含两个表的匹配行外，还包含了FROM子句中JOIN关键词右边表的不匹配行。右外连接实际上可以表示如下：
右外连接 = 内部连接 + 右边表中失配的元组
示例：
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select * from sale; NAME NUM ---------- ------ CSS 2234 Java_1 256 Java_2 651 SQL 85 C++ 8455 C 1208 6 rows selected SQL> select * from books right join sale on books.name=sale.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 1 SQL 50 SQL 85 3 CSS 50 CSS 2234 C 1208 Java_2 651 Java_1 256 C++ 8455 6 rows selected
缺少的左边表中的列值用NULL表示。
与内部连接相比，全外连接除了包含两个表的匹配行外，还包含了FROM子句中JOIN关键词左边表和右边表的不匹配行。全外连接实际上可以表示如下：
全外连接= 内部连接 + 左边表中失配的元组 + 右边表中失配的元组
示例：
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select * from sale; NAME NUM ---------- ------ CSS 2234 Java_1 256 Java_2 651 SQL 85 C++ 8455 C 1208 6 rows selected SQL> select * from books full join sale on books.name=sale.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 3 CSS 50 CSS 2234 Java_1 256 Java_2 651 1 SQL 50 SQL 85 C++ 8455 C 1208 5 JavaScript 4 Java 2 MySQL 32 6 Python 32 10 rows selected
没有WHERE子句的交叉连接将产生连接所涉及的表的笛卡儿积。笛卡儿积结果集的大小为第1个表的行数乘以第2个表的行数。交叉连接使用关键字CROSS JOIN，且没有ON子句指明连接条件，但可以使用WHERE子句定义连接条件。下面两个表达式是完全等价的：
SELECT ＊ FROM table1, table2 SELECT ＊ FROM table1 CROSS JOIN table2
示例：
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select * from sale; NAME NUM ---------- ------ CSS 2234 Java_1 256 Java_2 651 SQL 85 C++ 8455 C 1208 6 rows selected SQL> select * from books cross join sale where books.name=sale.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 3 CSS 50 CSS 2234 1 SQL 50 SQL 85
在进行多表连接时，如果表中要连接的列含有空值（NULL），则它们被认为是相互不匹配的。因此，如果其中一个连接表的列中出现空值，则只能通过外部连接返回这些空值（除非WHERE子句不包括空值）。
示例：
SQL> select * from books; ID NAME PRICE --- ---------- ------ 1 SQL 50 2 MySQL 32 3 CSS 50 4 Java 5 JavaScript 6 Python 32 6 rows selected SQL> select * from sale; NAME NUM ---------- ------ CSS 2234 Java_1 256 Java_2 651 SQL 85 C++ 8455 C 1208 6 rows selected SQL> select * from books inner join sale on books.name=sale.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 3 CSS 50 CSS 2234 1 SQL 50 SQL 85 SQL> select * from books left join sale on books.name=sale.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 3 CSS 50 CSS 2234 1 SQL 50 SQL 85 5 JavaScript 4 Java 2 MySQL 32 6 Python 32 6 rows selected
另外，从结果中很难区分数据中的NULL值和表示连接不匹配记录的NULL值。因此，如果连接的数据有空值，最好用常规连接方式从结果中删除这些空值。
在连接后的表中也可以使用聚合函数对数据进行聚合分析。
以下示例以price分组查询信息条数和销售（num）合计数：
SQL> select * from books b left join sale s on b.name=s.name; ID NAME PRICE NAME NUM --- ---------- ------ ---------- ------ 3 CSS 50 CSS 2234 1 SQL 50 SQL 85 5 JavaScript 4 Java 2 MySQL 32 6 Python 32 6 rows selected SQL> select price,count(*),sum(num) from books b left join sale s on b.name=s.name group by b.price; PRICE COUNT(*) SUM(NUM) ------ ---------- ---------- 50 2 2319 2 32 2
连接多个表时，可以认为它们被连成了一个表。尽管没有创建一个物理表，但SQL引擎创建了很多虚拟表，当连接表时，可以在每个表中选择任何一列。
Copyright © 2017-Now pnotes.cn. All Rights Reserved.
编程学习笔记 保留所有权利
MARK:3.0.0.20240214.P35
From 2017.2.6
