DB / Oracle / SQL 开发

Oracle SQL 连接方式（JOIN）全解

2026.09.06

Oracle SQL 连接方式（JOIN）全解

适用版本：Oracle Database 9i / 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

Oracle SQL 支持多种 JOIN 方式[1]：

JOIN 类型说明
INNER JOIN内连接
LEFT JOIN左外连接
RIGHT JOIN右外连接
FULL JOIN全外连接
CROSS JOIN交叉连接
NATURAL JOIN自然连接
SELF JOIN自连接

2. INNER JOIN（内连接）

2.1 语法

-- ANSI 语法
SELECT e.id, e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- Oracle 语法
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

2.2 结果

仅返回匹配的行。

3. LEFT OUTER JOIN（左外连接）

3.1 语法

-- ANSI 语法
SELECT e.id, e.name, d.dept_name
FROM employees e
LEFT OUTER JOIN departments d ON e.dept_id = d.id;

-- Oracle 语法
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id(+);

3.2 结果

返回左表所有行，右表无匹配则 NULL。

4. RIGHT OUTER JOIN（右外连接）

4.1 语法

-- ANSI 语法
SELECT e.id, e.name, d.dept_name
FROM employees e
RIGHT OUTER JOIN departments d ON e.dept_id = d.id;

-- Oracle 语法
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id(+) = d.id;

4.2 结果

返回右表所有行，左表无匹配则 NULL。

5. FULL OUTER JOIN（全外连接）

5.1 语法

-- ANSI 语法
SELECT e.id, e.name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

-- Oracle 旧语法不支持，需用 UNION
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id(+)
UNION
SELECT e.id, e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id(+) = d.id;

5.2 结果

返回两表所有行，无匹配则 NULL。

6. CROSS JOIN（交叉连接）

6.1 语法

-- ANSI 语法
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

-- Oracle 语法
SELECT e.name, d.dept_name
FROM employees e, departments d;

6.2 结果

笛卡尔积，行数 = 左表行数 × 右表行数。

7. NATURAL JOIN（自然连接）

7.1 语法

SELECT id, name, dept_name
FROM employees
NATURAL JOIN departments;

7.2 结果

自动按同名列连接。

7.3 注意

  • 不推荐使用
  • 同名列必须类型一致
  • 易出错

8. USING 子句

8.1 语法

SELECT e.id, e.name, d.dept_name
FROM employees e
JOIN departments d USING (dept_id);

8.2 说明

  • 指定连接列
  • 列不能加表前缀
  • 列名必须一致

9. SELF JOIN（自连接）

9.1 语法

-- 员工与经理
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

9.2 应用

  • 层级关系
  • 树形结构
  • 同表对比

10. 多表连接

10.1 多表 INNER JOIN

SELECT e.name, d.dept_name, j.job_title
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
INNER JOIN jobs j ON e.job_id = j.id;

10.2 混合 JOIN

SELECT e.name, d.dept_name, j.job_title, l.location
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
LEFT JOIN jobs j ON e.job_id = j.id
LEFT JOIN locations l ON d.location_id = l.id;

11. JOIN 执行机制

11.1 连接算法

算法说明适用
Nested Loop嵌套循环小表驱动大表
Hash Join哈希连接大表等值连接
Sort Merge排序合并排序数据
Cartesian笛卡尔积无条件

11.2 Nested Loop Join

for r1 in outer_table loop
  for r2 in inner_table loop
    if match(r1, r2) then output
  end loop
end loop

11.3 Hash Join

1. 构建阶段：扫描小表，构建 hash 表
2. 探测阶段：扫描大表，hash 查找匹配

详细内容见：Oracle 执行计划与 JOIN 算法。

12. JOIN 优化

12.1 索引使用

-- 连接列加索引
CREATE INDEX idx_emp_dept ON employees(dept_id);
CREATE INDEX idx_dept_id ON departments(id);

12.2 Hints

-- 强制使用索引
SELECT /*+ INDEX(e idx_emp_dept) */ e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- 强制 Hash Join
SELECT /*+ USE_HASH(e d) */ e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- 强制 Nested Loop
SELECT /*+ USE_NL(e d) */ e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

12.3 驱动表选择

-- 小表驱动大表
SELECT /*+ LEADING(d) USE_NL(e) */ e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

13. 常见坑与排错

13.1 笛卡尔积

现象：结果行数异常多。

修复：

-- 检查 WHERE 条件
SELECT e.name, d.dept_name
FROM employees e, departments d;
-- 缺少 WHERE e.dept_id = d.id

13.2 外连接方向错误

修复：

-- (+) 在哪边，哪边可为 NULL
WHERE e.dept_id(+) = d.id  -- 左外连接（员工为空）
WHERE e.dept_id = d.id(+)  -- 右外连接（部门为空）

13.3 性能差

修复：

-- 1. 检查执行计划
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 2. 添加索引
-- 3. 使用 Hints
-- 4. 优化 JOIN 顺序

13.4 NULL 值处理

-- NULL 不等于 NULL
SELECT * FROM t1 JOIN t2 ON t1.id = t2.id;
-- 不会匹配 id 为 NULL 的行

-- 处理 NULL
SELECT * FROM t1 JOIN t2 ON NVL(t1.id, -1) = NVL(t2.id, -1);

14. 最佳实践

  1. 使用 ANSI JOIN 语法：清晰易读
  2. 连接列加索引：提升性能
  3. 小表驱动大表：减少 I/O
  4. **避免 SELECT ***：明确列名
  5. 检查执行计划：验证 JOIN 算法
  6. 合理使用 Hints：优化执行
  7. 外连接注意方向：避免错误
  8. NULL 处理：NVL/COALESCE
  9. 避免 NATURAL JOIN：易出错
  10. 测试大数据量：验证性能

15. 参考资料

[1] Oracle Database SQL Language Reference 19c, “SELECT” https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html

[2] Oracle Database Performance Tuning Guide 19c, “Joins” https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/