Oracle开发误区探索【六】（外连接陷阱）

收获不止数据库

已于 2025-05-07 13:13:25 修改

阅读量1.2k

点赞数 21

分类专栏：数据库拍案惊奇文章标签： oracle 数据库数据库开发数据库设计大数据外连接数据分析

于 2025-05-05 11:12:06 首次发布

本文链接：https://blog.csdn.net/weixin_39534833/article/details/147678475

版权

数据库拍案惊奇专栏收录该内容

85 篇文章

订阅专栏

梁敬彬梁敬弘兄弟出品

完整系列
ORACLE开发误区探索【一】（空格、过程与权限、DDL）
ORACLE开发误区探索【二】(insert into、or）
ORACLE开发误区探索【三】(sequence ）
ORACLE开发误区探索【四】(树形查询、保留字）
ORACLE开发误区探索【五】（包的限制）

Oracle数据库的外连接（Outer Join）是一种强大的SQL技术，允许开发者在关联查询中返回主表中没有匹配记录的行。然而，外连接的语法和逻辑容易引发误解，特别是使用Oracle传统语法(+)时，常常导致查询结果不准确或性能问题。本文将深入探讨外连接中常见的陷阱及其解决方案。

10. 外连接陷阱

外连接(Outer Join)是常用的SQL技术，但其语法和逻辑容易引发误解，导致查询结果不准确。

10.1 外连接中条件位置的陷阱

问题演示

创建测试环境：

-- 创建测试表
CREATE TABLE employee_data (emp_id INT, salary NUMBER, period_id VARCHAR2(20));
CREATE TABLE employee_info (emp_id INT, name VARCHAR2(20));

-- 插入测试数据
INSERT INTO employee_data VALUES (111, 47877, '201001');
INSERT INTO employee_data VALUES (999, 997650, '200912');

INSERT INTO employee_info VALUES (111, '张三');
INSERT INTO employee_info VALUES (112, '李四');
INSERT INTO employee_info VALUES (108, '王五');
INSERT INTO employee_info VALUES (999, '赵六');

COMMIT;

需求：查询所有员工信息，显示其在特定期间(201001)的薪资，若无记录则显示0。

错误写法

SELECT e.name, NVL(d.salary, 0) AS salary 
FROM employee_data d, employee_info e
WHERE d.emp_id(+) = e.emp_id
  AND d.period_id = '201001';  -- 没有使用(+)

结果（错误）：

NAME                 SALARY
-------------------- --------------------
张三                 47877

这个查询只返回了一行记录，丢失了其他员工信息。

正确写法

SELECT e.name, NVL(d.salary, 0) AS salary 
FROM employee_data d, employee_info e
WHERE d.emp_id(+) = e.emp_id
  AND d.period_id(+) = '201001';  -- 添加了(+)

结果（正确）：

NAME                 SALARY
-------------------- --------------------
张三                 47877
赵六                 0
李四                 0
王五                 0

原因分析

在外连接语法中，若过滤条件应用于被外连接的表（带有(+)的表），则此条件必须同样添加(+)标记，否则会将外连接转换为内连接。

10.2 替代方案

使用子查询过滤数据

WITH filtered_data AS (
  SELECT * FROM employee_data WHERE period_id = '201001'
)
SELECT e.name, NVL(d.salary, 0) AS salary 
FROM filtered_data d, employee_info e
WHERE d.emp_id(+) = e.emp_id;

使用内联视图

SELECT e.name, NVL(d.salary, 0) AS salary 
FROM employee_info e,
     (SELECT * FROM employee_data WHERE period_id = '201001') d
WHERE d.emp_id(+) = e.emp_id;

使用ANSI标准语法（推荐）

SELECT e.name, NVL(d.salary, 0) AS salary 
FROM employee_info e
LEFT JOIN employee_data d ON (e.emp_id = d.emp_id AND d.period_id = '201001');

10.3 外连接对优化器的限制

Oracle SQL中的传统外连接语法（使用(+)）会限制优化器的表连接顺序选择能力。

演示

测试使用ordered提示控制表连接顺序：

-- 传统外连接语法，尝试控制连接顺序（d,e）
SELECT /*+ ordered */ e.name, NVL(d.salary, 0) AS salary 
FROM employee_data d, employee_info e
WHERE d.emp_id(+) = e.emp_id
  AND d.period_id(+) = '201001';

执行计划：

--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     4 |   248 |     7  (15)| 00:01 |
|*  1 |  HASH JOIN OUTER   |              |     4 |   248 |     7  (15)| 00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE_INFO|     4 |   100 |     3   (0)| 00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEE_DATA|     1 |    37 |     3   (0)| 00:01 |
--------------------------------------------------------------------------------

尝试反转表顺序：

-- 尝试控制连接顺序（e,d）
SELECT /*+ ordered */ e.name, NVL(d.salary, 0) AS salary 
FROM employee_info e, employee_data d
WHERE d.emp_id(+) = e.emp_id
  AND d.period_id(+) = '201001';

执行计划（相同）：

--------------------------------------------------------------------------------
| Id  | Operation          | Name         | Rows  | Bytes | Cost (%CPU)| Time  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |              |     4 |   248 |     7  (15)| 00:01 |
|*  1 |  HASH JOIN OUTER   |              |     4 |   248 |     7  (15)| 00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE_INFO|     4 |   100 |     3   (0)| 00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEE_DATA|     1 |    37 |     3   (0)| 00:01 |
--------------------------------------------------------------------------------

对比使用内连接：

-- 内连接语法，控制连接顺序（d,e）
SELECT /*+ ordered */ e.name, NVL(d.salary, 0) AS salary 
FROM employee_data d, employee_info e
WHERE d.emp_id = e.emp_id
  AND d.period_id = '201001';

执行计划：

--------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    62 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |               |     1 |    62 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    37 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEE_INFO |     4 |   100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

反转内连接表顺序：

-- 内连接语法，控制连接顺序（e,d）
SELECT /*+ ordered */ e.name, NVL(d.salary, 0) AS salary 
FROM employee_info e, employee_data d
WHERE d.emp_id = e.emp_id
  AND d.period_id = '201001';

执行计划（顺序与上面不同）：

--------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    62 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |               |     1 |    62 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEE_INFO |     4 |   100 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMPLOYEE_DATA |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

10.4 最佳实践

使用ANSI标准连接语法：

SELECT e.name, NVL(d.salary, 0) AS salary 
FROM employee_info e
LEFT JOIN employee_data d ON (e.emp_id = d.emp_id AND d.period_id = '201001');

2. 避免在WHERE子句中混合使用过滤条件：

将表关联条件放在ON子句中
将过滤条件放在WHERE子句中

3. 传统语法使用建议：

确保在外连接表的所有条件上加上(+)
考虑使用WITH子句或内联视图预先过滤数据

4. 正确理解外连接逻辑：

外连接返回驱动表中的所有行
被外连接表中无匹配行时返回NULL值

总结

在这里插入图片描述

Oracle外连接是一个功能强大但易于误用的SQL特性。传统的(+)语法虽然简洁，但容易引发条件位置陷阱和优化限制。推荐使用更清晰、更灵活的ANSI标准语法进行外连接操作，尤其是在复杂查询场景中。

正确理解和使用外连接语法，不仅能确保查询结果准确，还能提高SQL性能和代码可维护性。在开发过程中，应当注意测试外连接查询的结果，确保其符合业务需求和预期逻辑。

在这里插入图片描述

系列回顾

“大白话人工智能” 系列
 “数据库拍案惊奇” 系列
 “世事洞明皆学问” 系列

三分钟讲述个人感悟——感恩，回馈