Oracle 11g R1（11.1）连接子句（join_clause）

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2080416

 

本文的重点是“连接子句（join_clause）”，而不是表连接本身。

 

本文内容

• 本文使用的表和其数据
• Oracle 11g R1(11.1) 连接子句（join_clause）
•     内连接（Inner Joins）
•     交叉连接（Cross Joins）
•     外连接（Outer Joins）
•     自然连接（Natural Joins）
• 参考资料

 

本文使用的表和其数据

• 部门表DEPT

• 员工表 EMP

 

Oracle 11g R1(11.1) 连接子句（join_clause）

使用适当的连接子句（join_clause）可以获得连接数据的一部分。

使用 inner_cross_join_clause 语法可以指定一个内连接（inner joins）或交叉连接（cross joins）；使用 outer_join_clause 语法可以指定一个外连接。

当要连接更多的表时，可以使用括号来覆盖默认的优先级，如下面语法：

SELECT ... FROM a JOIN (b JOIN c) ...

说明：该 SQL 会先连接 b 和 c，然后，将结果再与 a 连接。

 

内连接（Inner Joins）

“内连接”只返回满足连接条件的行。

INNER 关键字 显示指定一个内连接。

JOIN  关键字 显示说明将执行一个连接。使用该语法可以代替 FROM 子句连接语法，即在 FROM 子句用逗号分隔“表”的表达式，而在 WHERE 子句写连接条件。

ON 条件 使用 ON 子句指定连接条件。该连接条件独立于任何检索，或是 WHERE 子句中的过滤条件。即若要参照非同名的列进行等值连接，或想设置任意的连接条件，可以使用 ON 子句。

示例1：

select emp.empno, emp.ename, dept.dname, dept.loc
  from emp
 inner join dept on (emp.deptno = dept.deptno)
 order by emp.empno

执行结果：

说明：

• EMP 表共有14个员工，其中3个员工没有部门。因此，结果只包含有所属部门的员工，没有无部门的员工。

USING (字段名)  当你要指定一个列的等值连接，且这个列是两个表共有的，那么，可以在 USING column 子句指定该列。只有两个表具有相同列时，才能使用这个子句。该子句，当内连接中的列时，不会用表名或表别名验证列名。

示例2：

select emp.empno, emp.ename, dept.dname, dept.loc
  from emp
 inner join dept
 using (deptno)
 order by emp.empno

结果与示例 1 相同。

 

交叉连接（Cross Joins）

“CROSS 关键字”指示将执行一个交叉连接。交叉连接会产生两个表的笛卡儿积。本质上，与在 FROM 子句中用逗号分隔的记法一样。该示例略。

 

外连接（Outer Joins）

“外连接”返回所有满足连接条件的行，以及所有（或部分）满足一个表，却不满足另一个表的那些行。

你可以指定两种类型的外连接：

1. 使用常见的外连接，即在连接条件的两边使用 table_reference 语法；
2. 也可以使用分区外连接，即在连接的一边或另一边使用 query_partition_clause 语法。“分区外连接”，除了发生在“外表”，类似于常见的外连接，还会发生在“内表”的每个区。这种类型的连接会使离散数据更密集，便于统计分析。

outer_join_type  指示将要执行外连接的类型：

• 规定 RIGHT 指示右外连接。
• 规定 LEFT 指示左外连接。
• 规定 FULL 指示全外连接。 OUTER 关键字是可选的。

query_partition_clause 该子句定义一个分区外连接。

query_partition_clause 子句可以定义一个“分区外连接”。这个连接通过在“外连接”上应用查询返回的分区，来扩展常见的“外连接”。Oracle 数据库为每个指定 PARTITION BY 子句的表达式创建分区的行。对于 PARTITION BY 表达式，在每个查询的分区行都具有相同的值。

query_partition_clause 子句可以在“外连接”的任何一边。分区外连接的结果是合并，在分区结果中的每个分区的外连接，以及连接另一边的表。这个类型的外连接对于填充离散数据，简化统计分析很有用。

若去掉这个子句，则数据库会把整个表的表达式——table_reference 指定的每个表，当作单独一个分区来对待，这就变成了一个常见的“外连接”。

(To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses) or a partitioned outer join (in the outer_join_clause), use the lower branch of the syntax (with parentheses).)

示例3：比如有两个表，分别是产品表 inventory 和时间表 times。

 

SELECT times.time_id, product, quantity
  FROM inventory PARTITION BY(product)
 RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id)
 WHERE times.time_id BETWEEN
       TO_DATE('2012-04-01 00:00:00', 'YYYY-MM-DD hh24:mi:ss') AND
       TO_DATE('2012-04-06 00:00:00', 'YYYY-MM-DD hh24:mi:ss')
 ORDER BY 2, 1;

执行结果：

SELECT time_id,
       product,
       LAST_VALUE(quantity IGNORE NULLS) OVER(PARTITION BY product ORDER BY quantity) quantity
  FROM (SELECT times.time_id, product, quantity
          FROM inventory PARTITION BY(product)
         RIGHT OUTER JOIN times ON (times.time_id = inventory.time_id)
         WHERE times.time_id BETWEEN TO_DATE('2012-04-01', 'YY-MM-DD') AND
               TO_DATE('2012-04-06', 'YY-MM-DD'))
 ORDER BY 2, 1;

执行结果：

说明：

从以上两个示例可以看出，虽然相应的那天没有数据，但是，用“分区外连接”可以将离散稀疏的数据填充，便于统计分析。因为，在数据库中，NULL 或空值总是不好处理。

“分区外连接”的限制：

• 可以在连接的左边或右边指定 query_partition_clause ，但不能同时指定。
• 不能指定一个 “全分区外连接（FULL partitioned outer join）”。
• 若在一个带 ON 子句的外连接内指定 query_partition_clause，则不能在 ON condition 指定一个子查询。

ON condition 同“内连接”。

示例4：

select *
  from emp
  join dept on (emp.deptno = dept.deptno)
 order by emp.empno

ON condition 子句的限制：

• 不能在“自然外连接（NATURAL outer join）”上使用该子句。

USING column 在一个使用 USING 子句的“外连接”中，查询返回单独一个列，该列合成（coalesce）连接中两个匹配的列。coalesce 函数如下：

COALESCE (a, b) = a if a NOT NULL, else b.

因此：

• “左外连接”从 FROM 子句左边的表，返回所有公共列的值。
• “右外连接”从 FROM 子句右边的表，返回所有公共列的值。
• “全外连接”从两个连接的表，返回所有公共列的值。

示例5：

select * from emp join dept using (deptno) order by emp.empno

USING column 子句的限制：

• 不用表名或表别名验证列名。
• 不能对 LOB 列或集合列使用。
• 不能在“自然外连接（NATURAL outer join）”中使用。

 

 

自然连接（Natural Join）

NATURAL 关键字指示执行一个“自然连接”。自然连接基于在两个表中具有同名的所有列。它从两个表选择相关列具有相同值的行。当在“自然连接”中指定涉及的列时，不用表名或表别名验证列名。

有时，在“自然连接”或“交叉连接”的表的对可能存在歧义。如下所示：

a NATURAL LEFT JOIN b LEFT JOIN c ON b.c1 = c.c1

这个例子可以解释为下面两种方式：

a NATURAL LEFT JOIN (b LEFT JOIN c ON b.c1 = c.c1)
或
(a NATURAL LEFT JOIN b) LEFT JOIN c ON b.c1 = c.c1

为了避免歧义，应该使用括号。默认情况下，数据库从左到有连接。

示例6：

select * from emp natural join dept order by emp.empno asc

自然连接的限制：

• 不能在LOB 列、ANTYPE、ANYDATA 或 ANYDATASET 列，或是集合列上使用“自然连接”。

 

参考资料

Oracle 11g R1 Language Reference join_clause http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_10002.htm#i2080416