Oracle子查询 #

子查询分类
├── 按返回结果
│   ├── 单行子查询：返回一行
│   ├── 多行子查询：返回多行
│   └── 多列子查询：返回多列
├── 按与外查询关系
│   ├── 非相关子查询：独立执行
│   └── 相关子查询：依赖外查询
└── 按位置
    ├── WHERE子句
    ├── FROM子句（内联视图）
    ├── SELECT子句
    └── HAVING子句

-- 创建示例表
CREATE TABLE employees (
    employee_id NUMBER(6) PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    department_id NUMBER(4),
    salary NUMBER(8,2),
    manager_id NUMBER(6),
    hire_date DATE
);

CREATE TABLE departments (
    department_id NUMBER(4) PRIMARY KEY,
    department_name VARCHAR2(30),
    location_id NUMBER(4)
);

-- 插入测试数据
INSERT INTO employees VALUES (1, 'John', 'Doe', 10, 8000, NULL, TO_DATE('2020-01-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, 'Jane', 'Smith', 10, 9000, 1, TO_DATE('2019-03-20', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, 'Bob', 'Johnson', 20, 7000, 1, TO_DATE('2021-06-10', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, 'Alice', 'Williams', 20, 12000, 1, TO_DATE('2018-11-05', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, 'Charlie', 'Brown', 30, 6500, 2, TO_DATE('2022-02-28', 'YYYY-MM-DD'));

INSERT INTO departments VALUES (10, 'IT', 1);
INSERT INTO departments VALUES (20, 'Sales', 2);
INSERT INTO departments VALUES (30, 'HR', 3);

-- 单行子查询：返回一行结果
-- 使用单行比较运算符：=, >, <, >=, <=, <>

-- 查询薪资高于平均薪资的员工
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) FROM employees
);

-- 查询与John同部门的员工
SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id = (
    SELECT department_id 
    FROM employees 
    WHERE first_name = 'John'
);

-- 查询薪资最高的员工
SELECT employee_id, first_name, salary
FROM employees
WHERE salary = (
    SELECT MAX(salary) FROM employees
);

-- 在HAVING中使用单行子查询
-- 查询平均薪资高于公司平均薪资的部门
SELECT 
    department_id,
    AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (
    SELECT AVG(salary) FROM employees
);

-- 查询员工数最多的部门
SELECT 
    department_id,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) = (
    SELECT MAX(cnt) 
    FROM (
        SELECT COUNT(*) AS cnt
        FROM employees
        GROUP BY department_id
    )
);

-- 在SELECT中使用单行子查询
SELECT 
    employee_id,
    first_name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary,
    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- 查询部门名称
SELECT 
    e.employee_id,
    e.first_name,
    (SELECT d.department_name 
     FROM departments d 
     WHERE d.department_id = e.department_id) AS department_name
FROM employees e;

-- 多行子查询：返回多行结果
-- 使用多行运算符：IN, ANY, ALL

-- IN：匹配列表中的任一值
SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id IN (1, 2)
);

-- NOT IN：不匹配列表中的任何值
SELECT employee_id, first_name
FROM employees
WHERE department_id NOT IN (
    SELECT department_id 
    FROM departments 
    WHERE location_id = 1
);

-- 注意：NOT IN与NULL
-- 如果子查询返回NULL，NOT IN不会返回任何结果
SELECT employee_id, first_name
FROM employees
WHERE department_id NOT IN (
    SELECT department_id 
    FROM departments 
    WHERE department_id IS NOT NULL  -- 过滤NULL
);

-- ANY：与子查询返回的任一值比较
-- > ANY：大于最小值
-- < ANY：小于最大值
-- = ANY：等价于IN

-- 查询薪资高于IT部门任一员工的员工
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > ANY (
    SELECT salary 
    FROM employees 
    WHERE department_id = 10
);

-- 等价于
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (
    SELECT MIN(salary) 
    FROM employees 
    WHERE department_id = 10
);

-- = ANY 等价于 IN
SELECT employee_id, first_name
FROM employees
WHERE department_id = ANY (
    SELECT department_id FROM departments WHERE location_id = 1
);

-- ALL：与子查询返回的所有值比较
-- > ALL：大于最大值
-- < ALL：小于最小值

-- 查询薪资高于IT部门所有员工的员工
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > ALL (
    SELECT salary 
    FROM employees 
    WHERE department_id = 10
);

-- 等价于
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > (
    SELECT MAX(salary) 
    FROM employees 
    WHERE department_id = 10
);

-- 查询薪资低于IT部门所有员工的员工
SELECT employee_id, first_name, salary
FROM employees
WHERE salary < ALL (
    SELECT salary 
    FROM employees 
    WHERE department_id = 10
);

-- 多列子查询：返回多列结果

-- 成对比较：多列同时匹配
SELECT employee_id, first_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (
    SELECT department_id, MAX(salary)
    FROM employees
    GROUP BY department_id
);

-- 查询与John同部门且薪资相同的员工
SELECT employee_id, first_name, department_id, salary
FROM employees
WHERE (department_id, salary) = (
    SELECT department_id, salary
    FROM employees
    WHERE first_name = 'John'
);

-- 非成对比较：各列独立比较
SELECT employee_id, first_name, department_id, salary
FROM employees
WHERE department_id IN (
    SELECT department_id FROM employees WHERE first_name = 'John'
)
AND salary IN (
    SELECT salary FROM employees WHERE first_name = 'John'
);

-- 相关子查询：引用外查询的列

-- 查询薪资高于本部门平均薪资的员工
SELECT e.employee_id, e.first_name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

-- 查询每个部门薪资最高的员工
SELECT e.employee_id, e.first_name, e.department_id, e.salary
FROM employees e
WHERE e.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

-- EXISTS：检查子查询是否返回行
-- 只要子查询返回至少一行，EXISTS就返回TRUE

-- 查询有员工的部门
SELECT d.department_id, d.department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

-- NOT EXISTS：检查子查询是否不返回行
-- 查询没有员工的部门
SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
);

-- EXISTS vs IN
-- EXISTS：适合子查询结果集大的情况
-- IN：适合子查询结果集小的情况

-- 使用EXISTS查询有下属的员工
SELECT e.employee_id, e.first_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM employees e2
    WHERE e2.manager_id = e.employee_id
);

-- 相关更新
UPDATE employees e
SET salary = (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
)
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.department_name = 'IT'
);

-- 相关删除
DELETE FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
);

-- FROM子句中的子查询称为内联视图

-- 查询各部门平均薪资
SELECT e.department_id, e.avg_salary, d.department_name
FROM (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) e
JOIN departments d ON e.department_id = d.department_id;

-- 分页查询
SELECT *
FROM (
    SELECT 
        e.*,
        ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
    FROM employees e
)
WHERE rn BETWEEN 3 AND 5;

-- 复杂的内联视图
SELECT 
    dept_stats.department_id,
    dept_stats.employee_count,
    dept_stats.avg_salary,
    d.department_name
FROM (
    SELECT 
        department_id,
        COUNT(*) AS employee_count,
        AVG(salary) AS avg_salary,
        MAX(salary) AS max_salary,
        MIN(salary) AS min_salary
    FROM employees
    GROUP BY department_id
) dept_stats
JOIN departments d ON dept_stats.department_id = d.department_id
WHERE dept_stats.avg_salary > 8000;

-- WITH子句：定义公用表表达式（CTE）

-- 基本用法
WITH dept_avg AS (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT 
    e.employee_id,
    e.first_name,
    e.salary,
    d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

-- 多个CTE
WITH 
dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
high_salary AS (
    SELECT employee_id, first_name, salary, department_id
    FROM employees
    WHERE salary > 10000
)
SELECT 
    h.employee_id,
    h.first_name,
    h.salary,
    d.avg_salary
FROM high_salary h
JOIN dept_avg d ON h.department_id = d.department_id;

-- 递归CTE（Oracle 11g R2+）

-- 查询员工层级结构
WITH emp_hierarchy AS (
    -- 基础查询：顶级员工
    SELECT 
        employee_id,
        first_name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 递归查询：下属员工
    SELECT 
        e.employee_id,
        e.first_name,
        e.manager_id,
        h.level + 1 AS level
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.employee_id
)
SELECT 
    employee_id,
    first_name,
    manager_id,
    level,
    LPAD(' ', (level - 1) * 2, ' ') || first_name AS hierarchy
FROM emp_hierarchy
ORDER BY level, employee_id;

-- 生成数字序列
WITH nums AS (
    SELECT 1 AS n FROM DUAL
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

-- 标量子查询：返回单个值（一行一列）

-- 在SELECT中使用
SELECT 
    employee_id,
    first_name,
    salary,
    (SELECT department_name 
     FROM departments 
     WHERE department_id = e.department_id) AS department_name
FROM employees e;

-- 在ORDER BY中使用
SELECT employee_id, first_name, salary
FROM employees e
ORDER BY (SELECT department_name 
          FROM departments 
          WHERE department_id = e.department_id);

-- 在CASE中使用
SELECT 
    employee_id,
    first_name,
    salary,
    CASE 
        WHEN salary > (SELECT AVG(salary) FROM employees) THEN 'High'
        ELSE 'Low'
    END AS salary_level
FROM employees;

-- 标量子查询可能导致性能问题
-- 每行都会执行一次子查询

-- 优化：使用JOIN替代
-- 原查询
SELECT 
    e.employee_id,
    e.first_name,
    (SELECT d.department_name 
     FROM departments d 
     WHERE d.department_id = e.department_id) AS department_name
FROM employees e;

-- 优化后
SELECT 
    e.employee_id,
    e.first_name,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Oracle优化器会尝试展开子查询

-- 原查询
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

-- 优化器可能转换为JOIN
SELECT e.* 
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1;

-- 查看执行计划
EXPLAIN PLAN FOR
SELECT * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 使用提示控制子查询处理

-- NO_UNNEST：禁止子查询展开
SELECT /*+ NO_UNNEST */ * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

-- UNNEST：鼓励子查询展开
SELECT /*+ UNNEST */ * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

-- PUSH_SUBQ：尽早执行子查询
SELECT /*+ PUSH_SUBQ */ * FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1);

-- 错误：单行子查询返回多行
-- ORA-01427: single-row subquery returns more than one row

SELECT employee_id, first_name
FROM employees
WHERE department_id = (
    SELECT department_id FROM employees  -- 返回多行
);

-- 解决：使用IN或限制返回一行
SELECT employee_id, first_name
FROM employees
WHERE department_id IN (
    SELECT department_id FROM employees
);

-- 或
SELECT employee_id, first_name
FROM employees
WHERE department_id = (
    SELECT department_id FROM employees WHERE ROWNUM = 1
);

-- 子查询返回NULL时的问题

-- NOT IN与NULL
SELECT employee_id, first_name
FROM employees
WHERE department_id NOT IN (SELECT NULL FROM DUAL);
-- 结果：无数据返回

-- 解决：过滤NULL
SELECT employee_id, first_name
FROM employees
WHERE department_id NOT IN (
    SELECT department_id FROM departments WHERE department_id IS NOT NULL
);

-- 或使用NOT EXISTS
SELECT e.employee_id, e.first_name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM departments d WHERE d.department_id = e.department_id
);

-- 相关子查询可能导致性能问题
-- 每行都执行一次子查询

-- 原查询
SELECT e.employee_id, e.first_name
FROM employees e
WHERE EXISTS (
    SELECT 1 FROM departments d 
    WHERE d.department_id = e.department_id 
    AND d.location_id = 1
);

-- 优化：使用JOIN
SELECT DISTINCT e.employee_id, e.first_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id = 1;