第9章 SQL查询与子查询：连接-优快云博客

本文介绍了Oracle SQL中的连接查询，包括等值连接、自连接、笛卡尔积、内连接、外连接、反连接和半连接等。通过具体示例展示了如何使用不同类型的连接来检索来自多个表的数据。

原文地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm

连接是从两个或多个表、视图或者物化视图中检索数据的查询。当查询的FROM子句中存在多个表时执行连接操作。查询的SELECT列表可以选择这些表的任何列。如果两个表的某个列名相同，必须在列名前增加表名引用，以防止歧义。

1、连接条件

大多数连接查询至少包括一个连接条件，可以在FROM子句或者WHERE子句中。连接条件比较不同表中的两个列。连接条件中的列不需要出现在SELECT列表中。

执行三个或者更多表连接时，首先基于连接条件连接两个表，然后再将结果与另外一个表进行连接。优化器基于连接条件、表的索引以及其他可用的统计信息决定表连接的顺序。

包含连接条件的WHERE子句还可以包含引用单个表的其他条件，进一步限制查询返回的数据行。

注意：不能在包含连接条件的WHERE子句中指定LOB列。WHERE子句中的LOB还存在其他限制。更多信息参考Oracle数据库应用开发指南-大对象。

2、等值连接

等值连接是指连接条件包含等号运算符的连接。相等连接联合了指定列的值相等的数据行。根据优化器执行连接的内部算法，单表内的等值条件列的总大小限制为数据块大小减去一些其他开销。数据块大小由初始化参数DB_BLOCK_SIZE指定。

使用连接查询：示例

以下示例演示了不同的连接方式。

在第一个示例中，等值连接返回了每一位雇员的姓名和职业，以及他们所在的部门编号和部门名称。

SELECT last_name, job_id, departments.department_id, department_name
   FROM employees, departments
   WHERE employees.department_id = departments.department_id
   ORDER BY last_name;

LAST_NAME           JOB_ID     DEPARTMENT_ID DEPARTMENT_NAME
------------------- ---------- ------------- ----------------------
. . .
Sciarra             FI_ACCOUNT           100 Finance
Urman               FI_ACCOUNT           100 Finance
Popp                FI_ACCOUNT           100 Finance
. . .
必须使用连接来返回以上数据，因为雇员姓名与职业和部门名称存储在不同的表中。

以下等值连接返回所有销售经理的姓名、职业、部门编号以及部门名称。

SELECT last_name, job_id, departments.department_id, department_name
   FROM employees, departments
   WHERE employees.department_id = departments.department_id
   AND job_id = 'SA_MAN'
   ORDER BY last_name;

LAST_NAME           JOB_ID     DEPARTMENT_ID DEPARTMENT_NAME
------------------- ---------- ------------- -----------------------
Russell             SA_MAN                80 Sales
Partners            SA_MAN                80 Sales
Errazuriz           SA_MAN                80 Sales
Cambrault           SA_MAN                80 Sales
Zlotkey             SA_MAN                80 Sales
该查询与上一个示例的不同在于它使用了一个额外的WHERE子句条件，只返回job值为‘SA_MAN’的结果。

3、自连接

自连接是一个表与自身的连接。该表在FROM子句中出现两次，并且使用表别名区分连接条件中的列。

使用自连接：示例

以下查询使用自连接返回每一位雇员的姓名以及他们的经理的姓名。

SELECT e1.last_name||' works for '||e2.last_name
   "Employees and Their Managers"
   FROM employees e1, employees e2
   WHERE e1.manager_id = e2.employee_id
      AND e1.last_name LIKE 'R%';

Employees and Their Managers
-------------------------------
Rajs works for Mourgos
Raphaely works for King
Rogers works for Kaufling
Russell works for King

4、笛卡尔积

如果连接查询的两个表之间没有连接条件，Oracle返回它们的笛卡尔积。Oracle使用一个表的所有行与另一个表的所有行进行组合。笛卡尔积通常产生大量行，并且很少使用。

5、内连接

内连接，有时称为简单连接，是指仅返回满足连接条件的数据行的连接。

6、外连接

外连接扩展了简单连接的结果集。外连接返回满足连接条件的所有行以及一个表在另一个表中没有对应行的部分或者全部数据行。

要编写一个执行表A与表B外连接，并返回A中所有行（左外连接）的查询，可以在FROM子句中使用LEFT [OUTER] JOIN语法，或者在WHERE子句的连接条件中将外连接运算符（+）应用到表B的列上。对于所有在B中没有对应行的A中的行，Oracle为包含B中列的SELECT列表返回NULL。
要编写一个执行表A与表B外连接，并返回B中所有行（右外连接）的查询，可以在FROM子句中使用RIGHT [OUTER] JOIN语法，或者在WHERE子句的连接条件中将外连接运算符（+）应用到表A的列上。对于所有在A中没有对应行的B中的行，Oracle为包含A中列的SELECT列表返回NULL。
要编写一个执行表A与表B外连接，并返回A与B中所有行（全外连接）的查询，可以在FROM子句中使用FULL [OUTER] JOIN语法。

可以使用外连接进行稀疏数据的填充。这种连接被称为分区外连接，使用join_clause的query_partition_clause语法。稀疏数据是指某个维（例如时间或部门）中不是所有可能的值都有对应行的数据。例如，销售数据表通常对于在给定日期没有销售额的产品对应的数据。填充数据间隔对于数据稀疏型的复杂分析计算或者直接查询时稀疏数据缺少的情况非常有用。

更多关于使用外连接填充稀疏数据间隔的信息，参考SELECT语句的join_clause。

关于分组外连接与稀疏数据间隔填充的完整讨论，参见Oracle数据仓库指南。

Oracle推荐使用FROM子句OUTER JOIN语法替代连接运算符。使用连接运算符（+）的外连接查询遵循以下规则与限制。

不能在包含FROM子句JOIN语法的查询块中指定（+）运算符。
（+）运算符只能出现在WHERE子句中，或者FROM子句的左关联中（即指定TABLE子句时），并且只能应用与一个表或视图的一个列上。
如果A与B通过多个条件进行连接，必须在这些条件上都应用（+）运算符。否则，Oracle按照简单连接返回数据，而不显示警告或者错误信息。
如果在外层查询中指定一个表，并且在内层查询中指定另一个表，（+）运算符不会产生一个外部连接。
不能使用（+）运算符将表与自身进行外连接。例如以下语句是无效的。

-- The following statement is not valid:
SELECT employee_id, manager_id
FROM employees
WHERE employees.manager_id(+) = employees.employee_id;

但是，以下自连接是有效的。

SELECT e1.employee_id, e1.manager_id, e2.employee_id
FROM employees e1, employees e2
WHERE e1.manager_id(+) = e2.employee_id;

（+）运算符仅可以应用于列，而不能是任意表达式。但是，任意表达式可以包含一个或多个带有（+）运算符的列。
包含（+）运算符的WHERE条件不能与其他条件通过OR逻辑运算符进行组合。
WHERE条件中，不能使用IN将带有（+）运算符的列与表达式进行比较。
WHERE条件中，不能将任何带有（+）运算符的列与子查询进行比较。

如果WHERE子句包含一个将表B的列与常量进行比较的条件，必须将（+）运算符应用于该列，以便Oracle从表A中返回列为NULL的行。否则进行简单连接。

如果外连接的表多于两个，一个表只能外连接到一个另外的表。因此，不能同时在A和B的连接条件与B和C的连接条件中在B的列上应用（+）运算符。

7、反连接

反连接返回谓词左边数据行中在右边不存在对应数据的行。也就是说，它返回不匹配（NOT IN）右边子查询的行。

使用反连接：示例

以下示例返回不在一些特定部门的雇员列表。

SELECT * FROM employees
   WHERE department_id NOT IN
   (SELECT department_id FROM departments
       WHERE location_id = 1700)
   ORDER BY last_name;

8、半连接

半连接返回匹配EXISTS子查询的数据行，当谓词右边存在满足子查询的多行时，只返回左边的非重复行。

当子查询位于WHERE子句的OR分支时，不能进行半连接与反连接的转换。

使用半连接：示例

在下例中，针对每一个department_id，即使在employees表中可能存在匹配子查询的多行数据，departments表只需要返回一行。如果employees表的salary列上没有索引，半连接可以用于改善查询性能。

SELECT * FROM departments
   WHERE EXISTS
   (SELECT * FROM employees
       WHERE departments.department_id = employees.department_id
       AND employees.salary > 2500)
   ORDER BY department_name;