SQL Server中NOT IN与LIKE联合使用的查询问题

需求说明 #

需要从CUS表中筛选满足以下两个条件的邮箱：

• 该邮箱对应的订单（order_no）在CMT表中没有cmt_sql_no=3的记录
• CUS表的email字段以'Ack'开头（即LIKE 'Ack%'）

预期结果 #

根据样本数据集，仅返回 Ack:email3@email.com。原因：

• order_no=186350和186351在CMT表中无cmt_sql_no=3的记录
• 其中只有order_no=186351对应的CUS表email字段以'Ack'开头

错误的尝试SQL #

SELECT U.email
FROM CUS U inner join CMT M ON U.order_no = M.ord_no
WHERE M.cmt like 'Ack%'
AND U.order_no NOT IN (
    SELECT L.ord_no
    FROM CMT L
    WHERE L.cmt_sql_no = 3
);

问题分析 #

这段SQL存在两个核心错误：

1. 错误地将CMT表的cmt字段作为匹配对象，实际应该匹配CUS表的email字段
2. 使用INNER JOIN会强制关联CMT表的记录，逻辑上无法准确判断“无cmt_sql_no=3记录”的条件

正确的SQL方案 #

方法一：使用NOT EXISTS（推荐） #

SELECT U.email
FROM CUS U
WHERE U.email LIKE 'Ack%'
AND NOT EXISTS (
    SELECT 1
    FROM CMT M
    WHERE M.ord_no = U.order_no
      AND M.cmt_sql_no = 3
);

方法二：使用LEFT JOIN + IS NULL #

SELECT DISTINCT U.email
FROM CUS U
LEFT JOIN CMT M 
  ON U.order_no = M.ord_no 
  AND M.cmt_sql_no = 3
WHERE U.email LIKE 'Ack%'
AND M.id IS NULL;

方案说明 #

• NOT EXISTS：直接检查当前CUS订单是否在CMT中存在cmt_sql_no=3的记录，不存在则保留该行，逻辑直观且性能较好
• LEFT JOIN：关联CMT中cmt_sql_no=3的记录，若关联结果为NULL（M.id IS NULL），说明该订单无对应记录；使用DISTINCT避免因一个订单对应多条CMT记录导致的重复结果

样本数据集SQL #

-- 创建并填充CMT表
CREATE TABLE CMT (
  id INT,
  ord_type char(1),
  ord_no char(8),
  line_sql_no smallint,
  cmt_sql_no smallint,
  cmt nvarchar(4000)
);

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (1,'O','186349',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (2,'O','186349',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (3,'O','186349',0,3,'Comment 3');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (4,'O','186350',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (5,'O','186350',0,2,'Comment 2');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (6,'O','186351',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (7,'O','186351',0,2,'Comment 2');

INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (8,'O','186352',0,1,'Comment 1');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (9,'O','186352',0,2,'Comment 2');
INSERT INTO CMT (id,ord_type,ord_no,line_sql_no,cmt_sql_no,cmt) VALUES (10,'O','186352',0,3,'Comment 3');

-- 创建并填充CUS表
CREATE TABLE CUS (
  id INT,
  ord_type char(1),
  order_no nchar(10),
  status char(1),
  email nchar(50)
);

INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (1,'O','186349','4','Ack:email1@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (2,'O','186350','4','Inv:email2@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (3,'O','186351','4','Ack:email3@email.com');
INSERT INTO CUS (id,ord_type,order_no,status,email) VALUES (4,'O','186352','1','Inv:email4@email.com');

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