如何用更规范的SQL查询所有产品均跨国发货的公司

现有MySQL数据库中的sales表数据如下：

company     manufactured    shipped
Mercedes    Germany         United States
Mercedes    Germany         Germany
Mercedes    Germany         United States
Toyota      Japan           Canada
Toyota      Japan           England
Audi        Germany         United States
Audi        Germany         France
Audi        Germany         Canada
Tesla       United States   Mexico
Tesla       United States   Canada
Tesla       United States   United States

需求：筛选出所有产品均为跨国发货的公司，即该公司的每一条记录中manufactured列与shipped列的值都不相同。

期望结果：

company
Toyota
Audi

原实现方案 #

用户当前的可行但不够优雅的实现代码：

WITH temp_table AS (
    SELECT
      s.company AS company
      , SUM(CASE
               WHEN s.manufactured != s.shipped THEN 1
               ELSE 0
            END
           ) AS count_international
      , COUNT(s.company) AS total_within_company
    FROM
      sales s
    GROUP BY
      s.company
)
SELECT
   company
FROM
   temp_table
WHERE count_international = total_within_company

更简洁规范的优化方案 #

以下两种方案均符合SQL规范，逻辑直观且无需额外临时表：

方案1：使用HAVING子句直接过滤

通过统计同国发货的记录数，要求该数值为0即可：

SELECT company
FROM sales
GROUP BY company
HAVING COUNT(CASE WHEN manufactured = shipped THEN 1 END) = 0;

MySQL中可进一步简化（布尔表达式会返回1/0，直接求和判断）：

SELECT company
FROM sales
GROUP BY company
HAVING SUM(manufactured = shipped) = 0;

方案2：使用NOT EXISTS子查询

从公司维度出发，排除存在同国发货记录的公司：

SELECT DISTINCT s1.company
FROM sales s1
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s2
    WHERE s2.company = s1.company
      AND s2.manufactured = s2.shipped
);

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