嵌套SQL查询问题：班次筛选条件未生效，无法正确查询护士薪资

用户需求 #

查询早班护士中薪资低于或高于所有护士的人员名单，尝试过调整班次及薪资条件后，发现子查询的班次筛选未生效，结果不符合预期。

原始SQL #

select Fname,Lname,salary,ssn
from Employee
where joptype='nurse' AND salary <= ALL (select salary
from Employee E,Nurses N
where E.Ssn=N.Ssn AND E.joptype='nurse' AND N.shift='morning')

问题根源 #

1. 未限定主查询的班次：主查询仅筛选了护士身份，但未关联Nurses表锁定早班，导致结果包含所有班次的护士，而非目标早班群体。
2. 子查询逻辑偏离需求：原SQL子查询仅取早班护士的薪资，用来判断所有护士的薪资是否小于等于该集合，这和“早班护士对比所有护士薪资”的需求不匹配。
3. 隐式连接易出错：用逗号连接Employee和Nurses属于隐式连接，逻辑不清晰，容易引发关联错误。

修正后的SQL #

场景1：早班护士中薪资低于所有护士最小值的人员

SELECT e.Fname, e.Lname, e.salary, e.ssn
FROM Employee e
JOIN Nurses n ON e.Ssn = n.Ssn
WHERE e.joptype = 'nurse' 
  AND n.shift = 'morning'
  AND e.salary <= (SELECT MIN(salary) FROM Employee WHERE joptype='nurse')

场景2：早班护士中薪资高于所有护士最大值的人员

SELECT e.Fname, e.Lname, e.salary, e.ssn
FROM Employee e
JOIN Nurses n ON e.Ssn = n.Ssn
WHERE e.joptype = 'nurse' 
  AND n.shift = 'morning'
  AND e.salary >= (SELECT MAX(salary) FROM Employee WHERE joptype='nurse')

场景3：合并两个条件（低于最小值或高于最大值）

SELECT e.Fname, e.Lname, e.salary, e.ssn
FROM Employee e
JOIN Nurses n ON e.Ssn = n.Ssn
WHERE e.joptype = 'nurse' 
  AND n.shift = 'morning'
  AND (
    e.salary <= (SELECT MIN(salary) FROM Employee WHERE joptype='nurse')
    OR e.salary >= (SELECT MAX(salary) FROM Employee WHERE joptype='nurse')
  )

可选调整：对比早班内部薪资 #

如果实际需求是找早班护士中薪资最低或最高的人员（即对比早班内部的薪资），可以用以下SQL：

SELECT e.Fname, e.Lname, e.salary, e.ssn
FROM Employee e
JOIN Nurses n ON e.Ssn = n.Ssn
WHERE e.joptype = 'nurse' 
  AND n.shift = 'morning'
  AND (
    e.salary = (SELECT MIN(e2.salary) FROM Employee e2 JOIN Nurses n2 ON e2.Ssn = n2.Ssn WHERE e2.joptype='nurse' AND n2.shift='morning')
    OR e.salary = (SELECT MAX(e2.salary) FROM Employee e2 JOIN Nurses n2 ON e2.Ssn = n2.Ssn WHERE e2.joptype='nurse' AND n2.shift='morning')
  )

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