这个SQL语句看起来正确吗?

Question

我想知道我做对了什么。

select distinct 
    Departments.Department_No, Departments.Department_Name 
from 
    Departments 
join 
    Employees on Departments.Department_No = Employees.Department_No
join 
    Jobs on Jobs.Job_ID = Employees.Job_ID
where  
    Departments.Department_No not in (select distinct Department_No 
                                      from Employees 
                                      where Employees.Job_ID like '%SA_REP%');

Answer 1

• 您希望显示Department Number和Department Name的不同值
• Department Number上的Employees表与Department表合并
• Job ID上Employees和Jobs表的连接
• 通过排除整个 Employee表中具有匹配模式%SA_REP%的Job ID的Department Numbers来过滤结果

在我看来你不需要

• 与Jobs表的连接
• 连接Employees表
• 你也许可以看看其他用户的建议是否可以带来性能改进

---

SELECT DISTINCT departments.department_no, 
                departments.department_name 
FROM   departments 
WHERE  departments.department_no NOT IN (SELECT DISTINCT department_no 
                                         FROM   employees 
                                         WHERE  employees.job_id LIKE '%SA_REP%' 
                                        ); 

Answer 2

您可以简单地使用NOT EXISTS而不是使用NOT IN子查询:

SELECT DISTINCT 
     d.Department_No
    ,d.Department_Name
FROM Departments d
JOIN Employees e ON d.Department_No = e.Department_No
WHERE NOT EXISTS  
  (select 1
     from Employees e1
    where e1.Job_ID like '%SA_REP%'
    AND e1.Department_No = e.Department_No);

Answer 3

可以翻译没有"in"的where条件。你不需要从"Jobs"中获取日期——你不用它

Select distinct Departments.Department_No, Departments.Department_Name 
from Departments 
Join Employees on Departments.Department_No = Employees.Department_No
where  Employees.Job_ID not  like '%SA_REP%';