您可以使用EXISTS查找早班，在Oracle 12中，您可以使用ORDER BY salary ASC FETCH FIRST ROW WITH TIES查找工资最低的匹配员工：

SELECT Fname,
       Lname,
       salary,
       ssn
FROM   Employee e
WHERE  jobtype='nurse'
AND    EXISTS( SELECT 1
               FROM   Nurses N
               WHERE  E.Ssn=N.Ssn
               AND    N.shift='morning' )
ORDER BY salary ASC
FETCH FIRST ROW WITH TIES;

或者，如果要检查所有护士并查找最低工资，然后按早班筛选：

SELECT *
FROM   (
  SELECT Fname,
         Lname,
         salary,
         ssn
  FROM   Employee
  WHERE  jobtype='nurse'
  ORDER BY salary ASC
  FETCH FIRST ROW WITH TIES
) e
WHERE  EXISTS( SELECT 1
               FROM   Nurses N
               WHERE  E.Ssn=N.Ssn
               AND    N.shift='morning' )

如果要使用JOIN而不是EXISTS，则可以用途：

SELECT e.Fname,
       e.Lname,
       e.salary,
       e.ssn
FROM   (
         SELECT Fname,
                Lname,
                salary,
                ssn
         FROM   Employee
         WHERE  jobtype='nurse'
         ORDER BY salary ASC
         FETCH FIRST ROW WITH TIES
       ) e
       INNER JOIN Nurses N
       ON E.Ssn=N.Ssn
WHERE  N.shift='morning'

（但是，如果Employee在Nurses中有多个条目，则使用JOIN将获得重复条目，而使用EXISTS则不会获得重复条目）
或者对于您的代码，您似乎在错误的位置设置了移位过滤器：

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

fiddle

我是这样理解的你说的话
她们的工资比所有护士都低
可能意味着 * 他们的平均工资 *，所以我在子查询中使用了它。

select e.fname, e.lname, e.salary, e.ssn
from employee e join nurse n on e.ssn = n.ssn
where e.joptype = 'nurse'
  and n.shift = 'morning'
  and salary < (select avg(salary)         --> average salary of all nurses
                from employee
                where joptype = 'nurse'
               );

(Just a备注：真的是jop类型吗？不是joB吗？）

也许你可以试试这样：

select 
    Fname,  Lname,  salary,  ssn
from 
    Employee
where 
    joptype='nurse' AND 
    salary <=   (   Select  MIN(salary)
                    From    Employee E 
                    Inner join      Nurses N ON(E.Ssn=N.Ssn)
                    Where   E.joptype = 'nurse' AND 
                            N.shift = 'morning'
                )

...这将为您提供工资低于或等于早班护士最低工资的护士列表。