Oracle 11g R1(11.1) Joins表连接

Oracle 11g R1 11 1 Joins 表连接表连接 本文内容本文内容 概述 本文使用的表和其数据 Oracle 11g R1 表连接 连接条件 等值连接 Equijoin 自连接 Self Joins 笛卡尔积 Cartesian Products 内连接 Inner Joins 外连接 Outer Joins 反连接 Antijoins 半连接 Semijoins 参考资料 概述概述 Oracle 8i 之前的表连接 等值连接 Equijoin 自连接 Self join 外连接 Outer join 包括左外连接和右外连接 Oracle 9 之后的表连接 等值连接 Equijoin 自连接 Self join 内连接 Inner join 和交叉连接 Cross Joins 外连接 Outer join 包括左外连接 右外连接和全外连接 以及对它们进行分区 自然连接 Natural join 还可以使用 Using 关键字和 on 关键字建立连接 本文使用的表和其数据本文使用的表和其数据 部门表 DEPT 员工表 EMP 注意 1 员工表中没有部门为 40 的员工 2 员工编号 7839 为职位最高的人 它没有上级 3 员工编号为 7654 7876 7934 没有部门 Oracle 11g R1 表连接 表连接 Joins 连接 Join 是一个查询 把两个或多个表 table 视图 view 或物化视图 materialized view 的数据行合并在一起 无论何时 当多个表出现在 FROM 子句 Oracle 数据库都会执行一个连接 select 会列出所有表的所有列 select from emp dept 如果任何两个表有同名的列 那么你必须用表名限定所有引用的列 避免歧义 select emp empno emp ename emp job emp mgr emp hiredate emp sal m emp deptno dept deptno dept dname dept loc from emp dept 连接条件连接条件 大多数连接查询至少包含一个连接条件 或是在 FROM 子句 或是 WHERE 子句 连接 条件在每一个不同的表中比较两个列 执行一个连接时 Oracle 合并每个表的每行 以便 计算连接条件为 TRUE 连接条件中的列不必出现在 select 子句中 若要执行三个 或更多个表的连接 Oracle 先根据连接条件 比较前两个表的列 连接前 两个表 再基于连接条件 把这个连接结果与另一个表连接 Oracle 将继续这一过程 直 到所有的表都被连接到结果中 优化器基于连接条件 表的索引 以及表的统计信息决定 连接的顺序 包含连接条件的 WHERE 子句也可以包含其他条件 这些条件可以进一步限制连接查询 返回的行 如表中的某列 注意 如果 WHERE 子句包含连接条件 那么不能在 WHERE 子句中指定 LOB 列 在 WHERE 子句中使用 LOB 也会受到其他限制 参阅 Oracle Database SecureFiles 和 Large Objects 开发人员指南 等值连接 等值连接 Equijoins 等值连接 是在连接条件中包含相等操作符的连接 等值连接 合并具有相等值的规定的列 的数据行 根据内部算法 优化器选择执行的连接 一个单表等值连接的列的总大小被限 制在一个数据块内 以减少开销 数据块的大小是由初始化参数 DB BLOCK SIZE 指定 的 示例示例1 演示返回所有员工的编号和名字 以及所在的部门编号和名称 演示返回所有员工的编号和名字 以及所在的部门编号和名称 select emp empno emp ename dept deptno dept dname from emp dept where emp deptno dept deptno order by emp empno asc 结果 示例示例2 演示返回职位为 演示返回职位为 经理经理 员工的编号和名字 以及所在的部门编号和名称 员工的编号和名字 以及所在的部门编号和名称 select emp empno emp ename dept deptno dept dname from emp dept where emp deptno dept deptno and emp job MANAGER order by emp empno asc 结果 自连接 自连接 Self Joins 自连接 是一个表连接自己的连接 这个表在 FROM 子句中出现两次 并且加上表别名 以便在连接条件中限定列名 若要执行一个自连接 Oracle 数据库合并 并返回满足连接 条件的数据行 示例示例3 演示返回员工的直接上级 演示返回员工的直接上级 select works for from emp e1 emp e2 where e1 empno e2 mgr order by e1 empno asc 结果 上边的连接只返回满足连接条件的数据行 但是有时还需要返回表中不满足连接条件的数 据 笛卡尔积 笛卡尔积 Cartesian Products 如果两个表连接查询没有连接条件 那么 Oracle 会返回它们的笛卡尔积 Oracle 把一个 表的每行与另一个表的每行合并 笛卡尔积总会产生很多行 也就很少使用 例如 两个 100 行的表的笛卡尔积会有 10 000 行 除非你需要笛卡尔积 否则最好有连接条件 如 果一个查询要连接三个或更多的表 并且你没有规定连接条件 那么优化器会选择一个连 接顺序 以避免生产中间结果的笛卡尔积 内连接 内连接 Inner Joins 内连接 有时也被称为 简单连接 是一个连接两个或多个表 只返回满足连接条件的那 些数据行 示例示例4 演示返回员工部门编号对应的部门信息 没有部门编号的员工 则不返回 演示返回员工部门编号对应的部门信息 没有部门编号的员工 则不返回 select emp empno emp ename dept deptno dept dname from emp inner join dept on emp deptno dept deptno order by emp empno asc 结果 外连接 外连接 Outer Joins 外连接 扩展一个简单连接 内连接 的结果 外连接 返回所有满足条件的行 也返回那 些满足一个表的数据行 而不满足其他表的连接条件的数据行 执行表 A 和 B 的外连接 左外连接 返回表 A 的所有行 执行表 A 和 B 的右连接 右外连接 返回表 B 的所有行 执行表 A 和 B 的全外连接 返回表 A 和 表 B 的所有行 无论你指定什么形式 都不能在 WHERE 子句中包含外连接的子查询里比较一个列 你可以使用外部连接填充稀疏数据 sparse data 也许类似稀疏矩阵 的差距 这种连接称 为 分区外部连接 使用 query partition clause join clause 语法 稀疏数据是一维 没有 所有可能值的行 如时间 或部门 例如 销售数据 在一个给定日期 通常只有部分售 出产品的数据行 实际情况也是 在给定日期 产品不可能都卖出 在进行数据离散复杂 分析 data sparsity complicates analytic computation 或是直接查询稀疏数据 可能会缺失 一些数据 这种情况下 填充数据的间隙将很有用 示例示例5 左外连接 演示返回所有员工对应的部门信息 左外连接 演示返回所有员工对应的部门信息 select emp dept from emp left outer join dept on emp deptno dept deptno order by emp empno asc 等价于 select emp dept from emp dept where emp deptno dept deptno order by emp empno asc 结果 说明 1 在 FROM 子句使用 LEFT OUTER JOIN 语法 或在 WHERE 子句的连接条件中 对表 B 的所有列 应用外连接运算符 2 在 A 中没有匹配 B 的那些行 对 select 表达式包含的 B 的列 Oracle 数据库返回 null 如上图 第5 11 14 行 示例示例6 右外连接 演示返回所有部门下的员工信息 右外连接 演示返回所有部门下的员工信息 select emp dept from emp right outer join dept on emp deptno dept deptno order by emp empno asc 等价于 select emp dept from emp dept where emp deptno dept deptno order by emp empno asc 结果 说明 1 在 FROM 子句使用 RIGHT OUTER JOIN 语法 或在 WHERE 子句的连接条件中 对表 A 的所有列 应用外连接运算符 2 在 B 中没有匹配 A 的那些行 对 select 表达式包含的 A 的列 Oracle 数据库返回 null 如上图 第 12 行 示例示例7 全外连接 演示返回所有员工和所有部门的对应信息 员工可能相应的部门信息 全外连接 演示返回所有员工和所有部门的对应信息 员工可能相应的部门信息 部门可能是临时组建还有加入员工 部门可能是临时组建还有加入员工 select emp dept from emp full outer join dept on emp deptno dept deptno order by emp empno asc 结果 说明 1 在 FROM 子句应用 FULL OUTER JOIN 语法 2 全外连接是左外连接和右外连接的扩展 对于不满足连接条件的行 返回 null 如图 第 5 11 14 15 行 参看 join clausefor more information about using outer joins to fill gaps in sparse data Oracle Database Data Warehousing 指南 for a complete discussion of group outer joins and filling gaps in sparse data Oracle 推荐使用 FROM 子句 OUTER JOIN 语法 而不是连接运算符 使用连接运 算符 进行外连接查询会遵循以下规则和限制 它们也不能应用于 FROM 子句的 OUTER JOIN 语法则不会 不能在查询块里 既指定连接运算符 又包含 FROM 子句的连接语法 连接运算符 只能出现在 WHERE 子句 或在 FROM 子句左相关 当规定 TABLE 子句 的环境下 并且只能应用于表或视图的某列 如果 A 和 B 通过多个连接条件进行外连接 那么 你必须在连接条件中使用连 接运算符 否则 Oracle 数据库将只返回简单连接的结果 不会显示警告或是错误信 息提示 你没有得到一个外连接的结果 如果你在外连接查询中规定一个表 而在内部查询是另一个表 那么连接运算符 不会产生一个外连接结果 不能使用连接运算符 外连接一个表跟他自己 虽然自连接是有效的 例如 下面语句是无效的 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 ORDER BY e1 employee id e1 manager id e2 employee id 连接运算符 只能应用于一个列 而不是任意表达式 然而 一个任意表达式 可以包含一个 或多个由连接运算符 标记的列 一个包含连接运算符 的 WHERE 条件不能与其他使用 OR 逻辑运算符的条 件一起使用 一个 WHERE 条件不能使用 IN 比较条件 来用表达式与由连接运算符 标 记的列进行比较 如果 WHERE 子句中包含表 B 的列与一个常量进行比较的条件 那么连接运算符 必须要应用于这个列 以便 Oracle 返回表 A 的行 这些行 对于该列 会产生 null 否 则 Oracle 只返回一个简单连接的结果 在执行多个表的外连接查询时 一个单一的表对于其他表可以生成一个空表 因此 你可 以在连接 A 和 B 的连接条件上 并且 B 和 C 的连接条件上 对 B 的列不使用连接 运算符 请参阅 SELECT 外部连接语法 反连接 反连接 Antijoins 反连接 返回谓词 predicate 左边的行 这些行不满足谓词右边 它返回不匹配 NOT IN 右边子查询的行 示例示例8 演示返回部门不在 演示返回部门不在 DALLAS 和和 CHICAGO 的员工信息 的员工信息 select from emp where emp deptno not in select dept deptno from dep