在MySQL中实现类SQL Server EXCEPT的反向结果查询（DISPLAY=YES且REMARK=ITEM）

问题描述 #

现有table1表结构及数据如下：

CREATE TABLE `table1` (
  `SERIAL` bigint(20) UNSIGNED NOT NULL,
  `DISPLAY` varchar(20) DEFAULT NULL,
  `REMARK` varchar(20) DEFAULT NULL,
  `A` varchar(20) DEFAULT NULL,
  `B` varchar(20) DEFAULT NULL,
  `C` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

ALTER TABLE `table1`
  ADD PRIMARY KEY (`SERIAL`),
  ADD UNIQUE KEY `SERIAL` (`SERIAL`);

ALTER TABLE `table1`
  MODIFY `SERIAL` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

INSERT INTO `table1` (`SERIAL`, `DISPLAY`, `REMARK`, `A`, `B`, `C`) VALUES
(1, 'YES', 'ITEM', 'APPLE', 'BATTLE', 'CAT'),
(2, 'YES', 'ITEM', 'APPLE', 'BALLON', 'CATTLE'),
(3, 'YES', 'ITEM', 'AROPLANE', 'BALL', 'CANDLE'),
(4, 'NO', 'ITEM', 'APPLE', 'BALL', 'CAT'),
(5, 'YES', 'COLOR', 'APPLE', 'BALL', 'CAT'),
(6, 'YES', 'ITEM', NULL, NULL, NULL),
(7, 'YES', 'ITEM', 'APPLE', 'BALL', 'CAT');

原查询获取了符合特定条件的行：

SELECT SERIAL,DISPLAY, REMARK,A,B,C 
FROM table1 
WHERE DISPLAY="YES" 
  AND REMARK ="ITEM" AND (
    A in('APPLE')
    AND C in('CAT')
    OR  B in('BALL') 
  )

结果为：

(1, 'YES', 'ITEM', 'APPLE', 'BATTLE', 'CAT')
(3, 'YES', 'ITEM', 'AROPLANE', 'BALL', 'CANDLE'),
(7, 'YES', 'ITEM', 'APPLE', 'BALL', 'CAT');

现在需要获取满足DISPLAY='YES'且REMARK='ITEM'但排除上述结果的反向数据集，目标输出：

(2, 'YES', 'ITEM', 'APPLE', 'BALLON', 'CATTLE'),
(6, 'YES', 'ITEM', NULL, NULL, NULL)

解决方案 #

方法1：直接对原条件取反（兼容NULL值） #

利用德摩根定律对原查询的过滤条件取反，这种写法会自动兼容NULL值的判断（因为原条件对全NULL行不成立，取反后会被纳入结果）：

SELECT SERIAL, DISPLAY, REMARK, A, B, C
FROM table1
WHERE DISPLAY = 'YES'
  AND REMARK = 'ITEM'
  AND NOT (
    (A = 'APPLE' AND C = 'CAT')
    OR B = 'BALL'
  );

方法2：使用NOT IN（基于主键SERIAL） #

因为SERIAL是唯一非空主键，直接用原查询返回的主键值作为排除列表，写法简洁高效：

SELECT SERIAL, DISPLAY, REMARK, A, B, C
FROM table1
WHERE DISPLAY = 'YES'
  AND REMARK = 'ITEM'
  AND SERIAL NOT IN (
    SELECT SERIAL
    FROM table1 
    WHERE DISPLAY="YES" 
      AND REMARK ="ITEM" AND (
        A in('APPLE')
        AND C in('CAT')
        OR  B in('BALL') 
      )
  );

方法3：使用NOT EXISTS（关联子查询） #

通过关联子查询判断当前行是否不在原查询结果中，这种方式在复杂场景下更灵活，性能表现稳定：

SELECT t1.SERIAL, t1.DISPLAY, t1.REMARK, t1.A, t1.B, t1.C
FROM table1 t1
WHERE t1.DISPLAY = 'YES'
  AND t1.REMARK = 'ITEM'
  AND NOT EXISTS (
    SELECT 1
    FROM table1 t2
    WHERE t2.SERIAL = t1.SERIAL
      AND t2.DISPLAY="YES" 
      AND t2.REMARK ="ITEM" AND (
        t2.A in('APPLE')
        AND t2.C in('CAT')
        OR  t2.B in('BALL') 
      )
  );

验证结果 #

上述三种方法均可得到目标输出：

(2, 'YES', 'ITEM', 'APPLE', 'BALLON', 'CATTLE'),
(6, 'YES', 'ITEM', NULL, NULL, NULL)

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