似乎诀窍是将and not exists (select 0 from ships where name = o.ship)添加到原始查询中，不包括Ships表中不存在的船舶，当然，Country列的值不应该为空，因为会询问相关国家。因此，请考虑使用：

select country
  from Classes c
  left join Ships s
    on c.class = s.class 
 right join Outcomes o
    on c.class = ship
   and not exists (select 0 from ships where name = o.ship)
   and o.result = 'sunk'
 where c.country is not null
 group by country;

• 顺便说一句，使用ship in (select ship from outcomes where result = 'sunk')是多余的，and o.result = 'sunk'就足够了。*

我会使用having子句：

select c.country
from classes c join
     Ships s
     on c.class = s.class left join
     outcomes o
     on o.ship = s.name
group by c.country
having count(distinct s.name) = count(distinct case when o.result = 'sunk' then s.name end);

我比较了每个国家的船只和每个国家沉没的船只，我对它们进行了过滤，只显示了两个表中计数相同的国家

WITH ALL_SHIPS AS (
 SELECT ship, country from outcomes o
 JOIN classes c ON (c.class = o.ship)
 UNION
 SELECT name, country FROM ships s
 JOIN classes c ON (c.class = s.class)
),
SHIPS_PER_COUNTRY AS (
 SELECT country, COUNT(ship) count FROM ALL_SHIPS
 GROUP BY country
),
SHIPS_SUNK AS (
 SELECT ship, result FROM outcomes
 WHERE result = 'sunk'
),
SHIPS_SUNKED_PER_COUNTRY AS (
 SELECT aships.country, COUNT(ssunk.result) count FROM ALL_SHIPS aships
 JOIN SHIPS_SUNK ssunk ON (ssunk.ship = aships.ship)
 GROUP BY aships.country
)
SELECT spc.country FROM SHIPS_PER_COUNTRY spc
JOIN SHIPS_SUNKED_PER_COUNTRY sspc ON (sspc.country = spc.country)
WHERE spc.count = sspc.count