DB / Oracle / SQL 开发

Oracle SQL 复杂连接详解

2026.09.06

Oracle SQL 复杂连接详解

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

1. 概述

复杂 JOIN 场景汇总[1]：

详细见：Oracle JOIN 连接方式。

2. JOIN 类型

2.1 INNER

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

2.2 LEFT OUTER

SELECT e.name, d.dept_name
FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.id;
-- 所有员工，部门可能 NULL

2.3 RIGHT OUTER

SELECT e.name, d.dept_name
FROM employees e
  RIGHT JOIN departments d ON e.dept_id = d.id;
-- 所有部门，员工可能 NULL

2.4 FULL OUTER

SELECT e.name, d.dept_name
FROM employees e
  FULL JOIN departments d ON e.dept_id = d.id;
-- 所有员工 + 所有部门

2.5 CROSS

SELECT e.name, d.dept_name
FROM employees e
  CROSS JOIN departments d;
-- 笛卡尔积

3. 多表 JOIN

SELECT e.name, d.dept_name, p.project_name
FROM employees e
  INNER JOIN departments d ON e.dept_id = d.id
  INNER JOIN emp_projects ep ON e.id = ep.emp_id
  INNER JOIN projects p ON ep.project_id = p.id
WHERE e.status = 'ACTIVE';

4. 自连接

4.1 经理

SELECT e.name AS employee, m.name AS manager
FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id;

4.2 层级

SELECT e.name, LEVEL,
  SYS_CONNECT_BY_PATH(name, '/') AS path
FROM employees e
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

详细见：Oracle 层次查询。

5. JOIN 与 USING

-- USING（列名相同）
SELECT *
FROM employees e
  JOIN departments d USING (dept_id);
-- dept_id 自动去重

5.1 NATURAL

-- 自然连接（同名列自动）
SELECT *
FROM employees
  NATURAL JOIN departments;
-- 不推荐，不明确

6. JOIN 算法

6.1 Nested Loop

- 小表驱动大表
- 索引利用
- OLTP

6.2 Hash Join

- 大表
- 等值
- 内存
- 仓库

6.3 Sort Merge

- 已排序
- 不等值
- 大数据

6.4 Hint

SELECT /*+ USE_NL(a b) */ ...
SELECT /*+ USE_HASH(a b) */ ...
SELECT /*+ USE_MERGE(a b) */ ...
SELECT /*+ LEADING(a b) */ ...

详细见：Oracle SQL Hint 详解。

7. OUTER JOIN 应用

7.1 找未匹配

-- 没有部门的员工
SELECT e.name
FROM employees e
  LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

7.2 找孤儿

-- 没有员工的部门
SELECT d.dept_name
FROM departments d
  LEFT JOIN employees e ON d.id = e.dept_id
WHERE e.id IS NULL;

7.3 比对

-- 找出表 A 有但表 B 没有
SELECT a.id
FROM table_a a
  LEFT JOIN table_b b ON a.id = b.id
WHERE b.id IS NULL;

8. 子查询 JOIN

8.1 派生表

SELECT e.name, d.dept_name, s.avg_sal
FROM employees e
  JOIN departments d ON e.dept_id = d.id
  JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) s
    ON e.dept_id = s.dept_id
WHERE e.salary > s.avg_sal;

8.2 CTE

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_sal
FROM employees e
  JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_sal;

详细见：Oracle CTE 与递归查询。

9. EXISTS

9.1 替代 JOIN

-- JOIN
SELECT DISTINCT d.*
FROM departments d
  JOIN employees e ON d.id = e.dept_id;

-- EXISTS
SELECT *
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

9.2 NOT EXISTS

SELECT *
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);

详细见：Oracle 子查询与 EXISTS。

10. 复杂场景

10.1 多维 JOIN

SELECT 
  e.name,
  d.dept_name,
  l.location,
  c.country_name
FROM employees e
  JOIN departments d ON e.dept_id = d.id
  JOIN locations l ON d.location_id = l.id
  JOIN countries c ON l.country_id = c.country_id
WHERE c.region_id = 1;

10.2 自连接 + JOIN

SELECT e.name, m.name AS manager, d.dept_name
FROM employees e
  LEFT JOIN employees m ON e.manager_id = m.id
  JOIN departments d ON e.dept_id = d.id;

10.3 聚合 JOIN

SELECT d.dept_name, e.name, e.salary, s.dept_avg
FROM employees e
  JOIN departments d ON e.dept_id = d.id
  JOIN (
    SELECT dept_id, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY dept_id
  ) s ON e.dept_id = s.dept_id
WHERE e.salary > s.dept_avg;

11. 性能

11.1 JOIN 顺序

- 小表驱动大表
- 索引利用
- LEADING Hint

11.2 索引

- JOIN 列索引
- 复合索引
- 覆盖

详细见：Oracle 索引优化策略详解。

11.3 执行计划

EXPLAIN PLAN FOR ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

详细见：Oracle 执行计划详解。

12. 老式语法

12.1 避免使用

-- 老式（避免）
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id;

-- 外连接（避免）
SELECT e.name, d.dept_name
FROM employees e, departments d
WHERE e.dept_id = d.id(+);

12.2 推荐

-- 现代 JOIN
SELECT e.name, d.dept_name
FROM employees e
  JOIN departments d ON e.dept_id = d.id;

13. 常见坑与排错

13.1 笛卡尔积

- 忘 JOIN 条件
- 性能灾难
- 检查

13.2 NULL 处理

- OUTER JOIN
- IS NULL

13.3 列歧义

-- 列名歧义
SELECT id FROM a JOIN b ON a.id = b.id;  -- ERROR

-- 限定
SELECT a.id FROM a JOIN b ON a.id = b.id;

14. 最佳实践

  1. 现代 JOIN：清晰
  2. 别名：简洁
  3. ON 条件：明确
  4. OUTER 谨慎：性能
  5. 避免 CROSS：意外
  6. 索引 JOIN 列：性能
  7. CTE：复杂
  8. EXISTS：替代
  9. 执行计划：验证
  10. 测试：完整

15. 参考资料

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