join

标签：join 连接 Oracle 子句 department 联接 id

连接是组合来自两个或多个表、视图或物化视图的行的查询。FROM只要查询的子句中出现多个表，Oracle 数据库就会执行连接。查询的选择列表可以从任何这些表中选择任何列。如果这些表中的任何两个具有共同的列名，那么您必须在整个查询中使用表名限定对这些列的所有引用以避免歧义。

Join Conditions

大多数联接查询至少包含一个联接条件，无论是在FROM子句中还是在WHERE子句中。联接条件比较两列，每列来自不同的表。为了执行联接，Oracle数据库合并成对的行，每一行包含每个表中的一行，联接条件的计算结果为TRUE。联接条件中的列也不必出现在选择列表中。

要执行三个或更多表的连接，Oracle 首先根据连接条件比较它们的列来连接两个表，然后根据包含连接表和新表的列的连接条件将结果连接到另一个表。Oracle 将继续此过程，直到所有表都连接到结果中。优化器根据连接条件、表上的索引以及表的任何可用统计信息来确定 Oracle 连接表的顺序。

包含联接条件的WHERE子句还可以包含仅引用一个表的列的其他条件。这些条件可以进一步限制联接查询返回的行。

注意：如果WHERE子句包含联接条件，则不能在WHERE语句中指定LOB列。在WHERE子句中使用LOB也受到其他限制。有关详细信息，请参阅《Oracle Database SecureFiles and Large Objects Developer's Guide》。

Equijoins等值连接

equijoin是具有包含相等运算符的联接条件的联接。equijoin组合了具有指定列的等效值的行。根据优化器选择执行联接的内部算法，单个表中等联接条件中列的总大小可能限制为数据块的大小减去一些开销。数据块的大小由初始化参数DB_block_size指定。

equijoin 返回每个员工的姓名和工作以及员工所在部门的编号和名称：

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

LAST_NAME           JOB_ID     DEPARTMENT_ID DEPARTMENT_NAME
------------------- ---------- ------------- ----------------------
Abel                 SA_REP               80 Sales
Ande                 SA_REP               80 Sales
Atkinson             ST_CLERK             50 Shipping
Austin               IT_PROG              60 IT
. . .

您必须使用联接来返回此数据，因为员工姓名和职务存储在不同于部门名称的表中。Oracle数据库根据此联接条件合并两个表的行：

employees.department_id = departments.department_id

以下equijoin返回所有销售经理的姓名、职务、部门编号和部门名称：

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
------------------- ---------- ------------- -----------------------
Cambrault           SA_MAN                80 Sales
Errazuriz           SA_MAN                80 Sales
Partners            SA_MAN                80 Sales
Russell             SA_MAN                80 Sales
Zlotkey             SA_MAN                80 Sales

此查询与前面的示例相同，只是它使用额外的where_clause条件仅返回作业值为“SA_MAN”的行。

Band Joins标注栏连接

带区联接是一种特殊类型的非平衡联接，其中一个数据集中的键值必须在第二个数据集的指定范围（“带”）内。同一个表可以用作第一个和第二个数据集。

Self Joins自连接

自联接是表与自身的联接。此表在FROM子句中出现两次，后跟表别名，用于限定联接条件中的列名。为了执行自联接，Oracle数据库合并并返回满足联接条件的表行。

以下查询使用自联接返回每个员工的姓名以及员工经理的姓名。添加WHERE子句以缩短输出。

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%'
   ORDER BY e1.last_name;

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

此查询的联接条件使用示例表employees的别名e1和e2：

e1.manager_id = e2.employee_id

Cartesian Products笛卡尔积

如果连接查询中的两个表没有连接条件，则 Oracle 数据库返回它们的笛卡尔积。Oracle 将一个表的每一行与另一个表的每一行组合在一起。笛卡尔积总是生成很多行并且很少有用。例如，两个表的笛卡尔积（每个表有 100 行）有 10,000 行。除非您特别需要笛卡尔积，否则请始终包含连接条件。如果查询连接三个或更多表，并且您没有为特定对指定连接条件，则优化器可能会选择避免产生中间笛卡尔积的连接顺序。

内连接

内连接（有时称为简单连接）是两个或多个表的连接，它只返回满足连接条件的那些行。

外连接

外连接扩展了简单连接的结果。外连接返回满足连接条件的所有行，并返回一个表中的部分或全部行，而另一个表中没有满足连接条件的行。

要编写一个查询来执行表a和B的外部联接并返回a中的所有行（左外部联接），请在from子句中使用left[outer]join语法，或在WHERE子句中的联接条件中将外部联接运算符（+）应用于B的所有列。对于A中所有在B中没有匹配行的行，Oracle数据库将为包含B列的任何选择列表表达式返回null。
要编写一个查询来执行表a和B的外部联接并返回B中的所有行（右外部联接），请在from子句中使用right[outer]join语法，或在WHERE子句的联接条件中将外部联接运算符（+）应用于a的所有列。对于B中所有在A中没有匹配行的行，Oracle将为包含A列的任何选择列表表达式返回null。
要编写执行外部联接并返回a和B中的所有行的查询，如果不满足联接条件（完全外部联接），则扩展为空，请在from子句中使用full[outer]join语法。

无论指定哪种形式，都无法将列与任何外部联接的WHERE子句中的子查询进行比较。

可以使用外部联接来填充稀疏数据中的间隙。这样的连接称为分区外部连接，使用join_clause语法的query_partition_clause形成。稀疏数据是指没有包含维度所有可能值（如时间或部门）的行的数据。例如，销售数据表通常不包含在给定日期没有销售的产品的行。在数据稀疏性使分析计算复杂化或直接查询稀疏数据可能会丢失某些数据的情况下，填充数据间隙非常有用。

Oracle建议您使用FROM子句OUTER JOIN语法，而不是Oracle连接运算符。使用Oracle联接运算符（+）的外部联接查询受以下规则和限制的约束，这些规则和限制不适用于FROM子句Outer join语法：

不能在同时包含FROM子句联接语法的查询块中指定（+）运算符。
（+）运算符只能出现在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;

However, the following self join is valid:

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

（+）运算符只能应用于列，而不能应用于任意表达式。但是，任意表达式可以包含一个或多个用（+）运算符标记的列。
包含（+）运算符的WHERE条件不能与使用OR逻辑运算符的其他条件组合。
WHERE条件不能使用IN比较条件将用（+）运算符标记的列与表达式进行比较。

如果WHERE子句包含将表B中的列与常量进行比较的条件，则必须对该列应用（+）运算符，以便Oracle返回表a中为该列生成空值的行。否则，Oracle只返回简单联接的结果。

在Oracle Database的早期版本中，在执行两对以上表的外部联接的查询中，一个表可能是仅为另一个表生成的空表。从Oracle Database 12c开始，一个表可以是多个表的空生成表。例如，Oracle Database 12c中允许使用以下语句：

SELECT * FROM A, B, D
  WHERE A.c1 = B.c2(+) and D.c3 = B.c4(+);

在本例中，空生成的表B被外部联接到两个表A和D。有关外部联接的语法，请参阅SELECT。

以下示例显示分区外部联接如何填补行中的数据空白，以便于分析函数规范和可靠的报告格式。该示例首先创建一个要在联接中使用的小数据表：

SELECT d.department_id, e.last_name
   FROM departments d LEFT OUTER JOIN employees e
   ON d.department_id = e.department_id
   ORDER BY d.department_id, e.last_name;

熟悉传统Oracle数据库外部联接语法的用户将识别此表单中的相同查询：

SELECT d.department_id, e.last_name
   FROM departments d, employees e
   WHERE d.department_id = e.department_id(+)
   ORDER BY d.department_id, e.last_name;

Oracle强烈建议您使用前一示例中显示的更灵活的FROM子句连接语法。

左侧外部联接返回所有部门，包括没有任何员工的部门。带有右外部联接的同一语句返回所有员工，包括尚未分配给部门的员工：

注意：对于这些示例，employee Zeuss被添加到employers表中，它不是示例数据的一部分。

SELECT d.department_id, e.last_name
   FROM departments d RIGHT OUTER JOIN employees e
   ON d.department_id = e.department_id
   ORDER BY d.department_id, e.last_name;

DEPARTMENT_ID LAST_NAME
------------- -------------------------
. . .
          110 Gietz
          110 Higgins
              Grant
              Zeuss

从这个结果来看，还不清楚Grant和Zeuss的员工是否将department_id设置为NULL，或者他们的departmenti_id是否不在department表中。要确定这一点，需要完整的外部联接：

SELECT d.department_id as d_dept_id, e.department_id as e_dept_id,
      e.last_name
   FROM departments d FULL OUTER JOIN employees e
   ON d.department_id = e.department_id
   ORDER BY d.department_id, e.last_name;

 D_DEPT_ID  E_DEPT_ID LAST_NAME
---------- ---------- -------------------------
  . . .
       110        110 Gietz
       110        110 Higgins
  . . .
       260
       270
                  999 Zeuss
                      Grant

由于本例中的列名在联接的两个表中相同，因此也可以通过指定联接语法的USING子句来使用公共列功能。除了USING子句将两个匹配的列department_id合并为一个单列输出外，输出与上例相同：

SELECT department_id AS d_e_dept_id, e.last_name
   FROM departments d FULL OUTER JOIN employees e
   USING (department_id)
   ORDER BY department_id, e.last_name;

D_E_DEPT_ID LAST_NAME
----------- -------------------------
  . . .
        110 Higgins
        110 Gietz
  . . .
        260
        270
        999 Zeuss
            Grant

使用分区外部连接：示例

反连接

反连接从谓词左侧返回谓词右侧没有对应行的行。它返回未能匹配 ( NOT IN) 右侧子查询的行。

标签：join,连接,Oracle,子句,department,联接,id
From： https://www.cnblogs.com/wonchaofan/p/16757527.html

Join Conditions

Equijoins等值连接

Band Joins标注栏连接

Self Joins自连接

Cartesian Products笛卡尔积

内连接

外连接

反连接

相关文章

赞助商

阅读排行