Oracle多表连接 #

Oracle连接类型
├── 内连接（INNER JOIN）
│   └── 只返回匹配的行
├── 外连接（OUTER JOIN）
│   ├── 左外连接（LEFT JOIN）
│   ├── 右外连接（RIGHT JOIN）
│   └── 全外连接（FULL JOIN）
├── 自连接（SELF JOIN）
│   └── 表与自身连接
├── 交叉连接（CROSS JOIN）
│   └── 笛卡尔积
└── 自然连接（NATURAL JOIN）
    └── 自动匹配同名列

-- 创建示例表
CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30),
    location_id NUMBER(4)
);

CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER(4),
    manager_id NUMBER(6),
    salary NUMBER(8,2)
);

CREATE TABLE locations (
    location_id NUMBER(4) PRIMARY KEY,
    city VARCHAR2(30),
    country VARCHAR2(30)
);

-- 插入测试数据
INSERT INTO departments VALUES (10, 'IT', 1);
INSERT INTO departments VALUES (20, 'Sales', 2);
INSERT INTO departments VALUES (30, 'HR', 3);
INSERT INTO departments VALUES (40, 'Finance', NULL);

INSERT INTO locations VALUES (1, 'Beijing', 'China');
INSERT INTO locations VALUES (2, 'Shanghai', 'China');
INSERT INTO locations VALUES (3, 'Tokyo', 'Japan');

INSERT INTO employees VALUES (1, 'John', 'Doe', 10, NULL, 8000);
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 10, 1, 7000);
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 20, 1, 9000);
INSERT INTO employees VALUES (4, 'Alice', 'Williams', 20, 1, 8500);
INSERT INTO employees VALUES (5, 'Charlie', 'Brown', NULL, NULL, 6000);

-- 使用INNER JOIN语法
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 使用WHERE语法（Oracle传统语法）
SELECT 
    e.employee_id,
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 结果：只返回有部门的员工
EMPLOYEE_ID FIRST_NAME LAST_NAME DEPARTMENT_NAME
----------- ---------- --------- ---------------
          1 John       Doe       IT
          2 Jane       Smith     IT
          3 Bob        Johnson   Sales
          4 Alice      Williams  Sales

-- 连接三个表
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    l.city
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id;

-- 使用WHERE语法
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    l.city
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id
AND d.location_id = l.location_id;

-- 连接多个表
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name,
    l.city,
    m.first_name AS manager_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN locations l ON d.location_id = l.location_id
INNER JOIN employees m ON e.manager_id = m.employee_id;

-- 当连接列名相同时，可以使用USING
SELECT 
    employee_id,
    first_name,
    department_name
FROM employees
INNER JOIN departments USING (department_id);

-- 注意：使用USING时，连接列不能加表别名
-- 错误：SELECT e.employee_id, e.department_id FROM employees e JOIN departments d USING (department_id)
-- 正确：SELECT employee_id, department_id FROM employees JOIN departments USING (department_id)

-- LEFT JOIN：返回左表所有行，右表不匹配的显示NULL
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- 结果：Charlie没有部门，也会显示
EMPLOYEE_ID FIRST_NAME DEPARTMENT_NAME
----------- ---------- ---------------
          1 John       IT
          2 Jane       IT
          3 Bob        Sales
          4 Alice      Sales
          5 Charlie    NULL

-- Oracle传统语法（使用(+)）
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

-- LEFT JOIN加条件
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name IS NULL;  -- 找出没有部门的员工

-- RIGHT JOIN：返回右表所有行，左表不匹配的显示NULL
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- 结果：Finance部门没有员工，也会显示
EMPLOYEE_ID FIRST_NAME DEPARTMENT_NAME
----------- ---------- ---------------
          1 John       IT
          2 Jane       IT
          3 Bob        Sales
          4 Alice      Sales
         NULL NULL      Finance

-- Oracle传统语法
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

-- FULL JOIN：返回两表所有行
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.department_id;

-- 结果：显示所有员工和所有部门
EMPLOYEE_ID FIRST_NAME DEPARTMENT_NAME
----------- ---------- ---------------
          1 John       IT
          2 Jane       IT
          3 Bob        Sales
          4 Alice      Sales
          5 Charlie    NULL
         NULL NULL      Finance

-- Oracle传统语法模拟全外连接（使用UNION）
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
AND e.department_id IS NULL;

-- 注意：外连接的条件位置

-- 错误：条件放在WHERE中会变成内连接
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1;  -- Finance部门被过滤掉了

-- 正确：条件放在ON中
SELECT e.employee_id, e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id 
                        AND d.location_id = 1;

-- 自连接：表与自身连接
-- 查询员工及其经理
SELECT 
    e.employee_id,
    e.first_name AS employee_name,
    m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

-- 结果
EMPLOYEE_ID EMPLOYEE_NAME MANAGER_NAME
----------- ------------- ------------
          1 John          NULL
          2 Jane          John
          3 Bob           John
          4 Alice         John
          5 Charlie       NULL

-- 使用WHERE语法
SELECT 
    e.employee_id,
    e.first_name AS employee_name,
    m.first_name AS manager_name
FROM employees e, employees m
WHERE e.manager_id = m.employee_id(+);

-- 多级自连接：查询员工、经理、上级经理
SELECT 
    e.first_name AS employee,
    m1.first_name AS manager,
    m2.first_name AS senior_manager
FROM employees e
LEFT JOIN employees m1 ON e.manager_id = m1.employee_id
LEFT JOIN employees m2 ON m1.manager_id = m2.employee_id;

-- CROSS JOIN：返回两表的笛卡尔积
SELECT 
    e.first_name,
    d.department_name
FROM employees e
CROSS JOIN departments d;

-- 结果：每个员工与每个部门组合
-- 5个员工 × 4个部门 = 20行

-- 使用WHERE语法
SELECT 
    e.first_name,
    d.department_name
FROM employees e, departments d;
-- 没有WHERE条件时产生笛卡尔积

-- 注意：避免无意的笛卡尔积
-- 这通常是查询错误导致的

-- 生成所有组合
-- 例如：生成所有员工与所有月份的组合
SELECT 
    e.employee_id,
    e.first_name,
    m.month_name
FROM employees e
CROSS JOIN (
    SELECT 'January' AS month_name FROM DUAL UNION ALL
    SELECT 'February' FROM DUAL UNION ALL
    SELECT 'March' FROM DUAL
) m;

-- NATURAL JOIN：自动匹配同名列
SELECT 
    employee_id,
    first_name,
    department_name
FROM employees
NATURAL JOIN departments;

-- 等价于
SELECT 
    employee_id,
    first_name,
    department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- 注意：自然连接基于所有同名列
-- 如果有多个同名列，可能导致意外结果
-- 不推荐在生产环境使用

-- NATURAL LEFT JOIN
SELECT employee_id, first_name, department_name
FROM employees
NATURAL LEFT JOIN departments;

-- NATURAL RIGHT JOIN
SELECT employee_id, first_name, department_name
FROM employees
NATURAL RIGHT JOIN departments;

-- Oracle优化器会自动选择最优连接顺序
-- 但可以提供提示

-- 使用ORDERED提示
SELECT /*+ ORDERED */ 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 使用LEADING提示
SELECT /*+ LEADING(e d) */ 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- Oracle连接方法
-- 1. Nested Loop Join：适合小表驱动大表
-- 2. Hash Join：适合大表连接
-- 3. Sort Merge Join：适合已排序数据

-- 使用提示指定连接方法
SELECT /*+ USE_NL(e d) */ 
    e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT /*+ USE_HASH(e d) */ 
    e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT /*+ USE_MERGE(e d) */ 
    e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 在连接列上创建索引
CREATE INDEX idx_emp_dept ON employees(department_id);
CREATE INDEX idx_dept_loc ON departments(location_id);

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 复杂的多表连接
SELECT 
    e.employee_id,
    e.first_name || ' ' || e.last_name AS employee_name,
    d.department_name,
    l.city,
    m.first_name AS manager_name,
    j.job_title
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN locations l ON d.location_id = l.location_id
LEFT JOIN employees m ON e.manager_id = m.employee_id
LEFT JOIN jobs j ON e.job_id = j.job_id
WHERE e.salary > 5000
ORDER BY d.department_name, e.salary DESC;

-- 连接与聚合函数
SELECT 
    d.department_name,
    COUNT(e.employee_id) AS employee_count,
    NVL(AVG(e.salary), 0) AS avg_salary,
    NVL(SUM(e.salary), 0) AS total_salary
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY employee_count DESC;

-- 连接与子查询
SELECT 
    e.employee_id,
    e.first_name,
    e.salary,
    d.department_name,
    dept_avg.avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

-- 问题：忘记连接条件
SELECT e.first_name, d.department_name
FROM employees e, departments d;
-- 结果：5 × 4 = 20行（笛卡尔积）

-- 解决：添加连接条件
SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id;

-- 问题：SELECT * 显示重复列
SELECT *
FROM employees e
JOIN departments d ON e.department_id = d.department_id;
-- department_id出现两次

-- 解决：明确指定列
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

-- 问题：外连接的NULL值
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
-- Charlie的department_name为NULL

-- 解决：使用NVL或COALESCE
SELECT 
    e.first_name,
    NVL(d.department_name, 'No Department') AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;