寻求查询无员工部门的第三种SQL解决方案（已实现两种）

我已经完成课堂作业要求的两种查询无员工部门的SQL方案，现在想获取额外学分，想知道使用MINUS函数是否可行？以下是我的表结构和已实现的两种方案：

表结构创建语句 #

CREATE TABLE departments
(
    department_id, 
    department_name
) AS
SELECT 1, 'IT' FROM DUAL UNION ALL
SELECT 3, 'Sales' FROM DUAL UNION ALL
SELECT 2, 'DBA' FROM DUAL;


CREATE TABLE employees 
(
    employee_id, 
    first_name, last_name, 
    hire_date, 
    salary,  
    department_id
) AS
SELECT 1, 'Lisa', 'Saladino', DATE '2001-04-03', 100000, 1 FROM DUAL UNION ALL
SELECT 2, 'Abby', 'Abbott', DATE '2001-04-04', 50000, 1 FROM DUAL UNION ALL
SELECT 3, 'Beth', 'Cooper', DATE '2001-04-05', 60000, 1 FROM DUAL UNION ALL
SELECT 4, 'Carol', 'Orr', DATE '2001-04-06', 70000,1 FROM DUAL UNION ALL
SELECT 5, 'Vicky', 'Palazzo', DATE '2001-04-07', 88000,2 FROM DUAL UNION ALL
SELECT 6, 'Cheryl', 'Ford', DATE '2001-04-08', 110000,1 FROM DUAL UNION ALL
SELECT 7, 'Leslee', 'Altman', DATE '2001-04-10', 66666, 1 FROM DUAL UNION ALL
SELECT 8, 'Jill', 'Coralnick', DATE '2001-04-11', 190000, 2 FROM DUAL UNION ALL
SELECT 9, 'Faith', 'Aaron', DATE '2001-04-17', 122000,2 FROM DUAL;

已实现的两种有效方案 #

方案一：右连接+分组统计 #

select 
    d.department_id,
    d.department_name
from 
    employees e 
right join 
    departments d on e.department_id = d.department_id
group by 
    d.department_id, d.department_name
having 
    count(e.employee_id) = 0;

输出：

DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
      3          Sales

方案二：NOT EXISTS子查询 #

SELECT 
    d.department_id,
    d.department_name 
FROM 
    departments d
WHERE
    NOT EXISTS (SELECT * FROM employees e 
                WHERE d.department_id = e.department_id)

输出：

DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
      3          Sales

关于MINUS方案的可行性解答 #

完全可以使用MINUS来实现需求，MINUS会返回第一个查询结果中不存在于第二个查询结果的记录，刚好能筛选出没有对应员工记录的部门：

方案三：使用MINUS集合操作 #

SELECT department_id, department_name
FROM departments
MINUS
SELECT d.department_id, d.department_name
FROM departments d
JOIN employees e ON d.department_id = e.department_id;

或者更简洁的写法，直接从部门表中减去有员工关联的部门记录：

SELECT department_id, department_name
FROM departments
MINUS
SELECT DISTINCT e.department_id, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

两种写法都会返回正确结果：

DEPARTMENT_ID    DEPARTMENT_NAME
--------------------------------
      3          Sales

另外，还可以补充一种NOT IN方案作为额外选项：

方案四：NOT IN子查询 #

SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (
    SELECT DISTINCT department_id 
    FROM employees 
    WHERE department_id IS NOT NULL
);

注意要加上WHERE department_id IS NOT NULL，避免员工表中存在NULL部门ID导致NOT IN返回空结果。

内容的提问来源于stack exchange，提问作者Pugzly