带AND逻辑的SQL运算符替代IN用法及全承运商匹配查询实现

原查询的逻辑偏差 #

你当前编写的SQL逻辑是只要员工处理过任意一个存在过的承运商，就会被筛选出来，和你要的「员工必须覆盖全部所有可用承运商」的需求完全不匹配。这是SQL中非常经典的关系除法场景：要找出某类实体（这里是员工），和目标属性集合（这里是全部可用承运商）的关联是全量覆盖的。
针对你问的“是否存在AND逻辑的运算符替代IN”：SQL标准中没有专门的“匹配所有值”的内置替代运算符，你要的AND逻辑本质是「对所有可能的目标取值，关联条件都成立」，和IN对应的「匹配任意一个目标取值就成立」是集合层面完全相反的两种匹配逻辑，需要通过固定模式的SQL写法实现，不存在可以直接单行替换IN的运算符。

无需硬编码承运商值的实现方案 #

两种通用、可直接适配你表结构的写法，不需要手动写死ship_via的具体取值，后续新增承运商时代码也不需要修改：

方案1：分组计数匹配（最易理解） #

核心逻辑：

• 先统计整个系统里一共有多少种不重复的可用承运商
• 按员工分组，统计每个员工自己处理过的不重复承运商数量
• 当员工的覆盖数等于系统总承运商数时，说明他覆盖了所有可用承运商

SELECT e.first_name, e.last_name
FROM employees e
INNER JOIN (
    -- 按员工维度统计其覆盖的不重复承运商数量
    SELECT 
        employee_id,
        COUNT(DISTINCT ship_via) AS covered_carrier_cnt
    FROM orders
    GROUP BY employee_id
) emp_stat ON e.employee_id = emp_stat.employee_id
WHERE 
    -- 员工覆盖的承运商数量 = 系统内全量可用承运商总数
    emp_stat.covered_carrier_cnt = (
        SELECT COUNT(DISTINCT ship_via) FROM orders
    );

注意：统计员工覆盖的承运商时必须加DISTINCT，避免同一员工多次处理同一承运商时被重复计数，最终出现数量匹配但实际未覆盖全承运商的误判。

按照你给出的示例数据，系统内共有ship_via=1、2、3三类承运商，只有employee_id=1的John Fuller覆盖了全部3类，查询结果会和你的预期完全一致。

方案2：双重NOT EXISTS实现（性能更优） #

核心逻辑：不存在任何一个系统里的可用承运商，是这个员工从来没处理过的——只要这个命题成立，就说明员工覆盖了所有承运商。

SELECT e.first_name, e.last_name
FROM employees e
WHERE NOT EXISTS (
    -- 取出全系统存在的所有可用承运商
    SELECT DISTINCT ship_via
    FROM orders all_carrier
    WHERE NOT EXISTS (
        -- 校验：是否存在某个承运商是当前员工从未处理过的
        SELECT 1
        FROM orders o
        WHERE o.employee_id = e.employee_id
          AND o.ship_via = all_carrier.ship_via
    )
);

需要重点掌握的相关知识点方向 #

• 关系代数中的集合匹配逻辑：明确区分「任意值匹配（对应IN/ANY语义）」和「全量值匹配（对应ALL语义）」的差异，理解关系除法的应用场景
• 分组聚合的统计逻辑：掌握COUNT(DISTINCT 字段)做覆盖度校验的通用思路，注意统计重复值带来的计数偏差问题
• 关联子查询的执行规则：重点理解EXISTS/NOT EXISTS的执行逻辑，以及双重NOT EXISTS实现全量覆盖校验的经典模式
• 多表内连接的关联匹配规则，基于统计中间表做最终筛选的查询写法

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