如何使用"NOT IN“
如何使用"NOT IN“
提问于 2019-09-08 07:12:12
我想从promo_id = 1中找到promo_id = 2中不存在的所有代码
表名：promo_codes
列: id、promo_id、代码
SELECT code FROM promo_codes where promo_id=1 NOT IN
(SELECT code FROM promo_codes where promo_id=2);我总是返回0行(S)，尽管promo_id = 1中有promo_id = 2中不存在的代码。
回答 4
Database Administration用户
发布于 2019-09-08 10:06:09
您想要签入的是code是NOT IN，这是代码列表。你的语法需要修正。具体来说，要使用AND code NOT IN (...)：
SELECT code
FROM promo_codes
WHERE promo_id = 1
AND code NOT IN
(SELECT code FROM promo_codes WHERE promo_id = 2) ;如果code为空，则应在子查询中再添加一次检查，以避免NOT IN出现NULL问题：
SELECT code
FROM promo_codes
WHERE promo_id = 1
AND code NOT IN
(SELECT code
FROM promo_codes
WHERE promo_id = 2
AND code IS NOT NULL) ;或者，您可以使用NOT EXISTS或LEFT JOIN / IS NULL检查。它们通常比NOT IN (subquery)更高效，特别是在较旧的MySQL版本中：
-- using NOT EXISTS
SELECT p1.code
FROM promo_codes AS p1
WHERE p1.promo_id = 1
AND NOT EXISTS
(SELECT 1
FROM promo_codes AS p2
WHERE p2.promo_id = 2
AND p2.code = p1.code) ;
-- using LEFT JOIN / IS NULL
SELECT p1.code
FROM promo_codes AS p1
LEFT JOIN promo_codes AS p2
ON p2.promo_id = 2
AND p2.code = p1.code
WHERE p1.promo_id = 1
AND p2.promo_id IS NULL ;为了提高效率，我建议您在(promo_id, code)上添加一个复合索引，独立于您选择的查询(并测试它们以找出在您的情况下哪个运行得更快)。
Database Administration用户
发布于 2019-09-08 07:48:54
如果非在列表中包含任何空值，则not总是返回null。这样你就不会有麻烦了。
SELECT code FROM promo_codes where promo_id=1 and code NOT IN
(SELECT code FROM promo_codes where promo_id=2 and code is not null);您也可以使用“不存在”或“除了”。
Database Administration用户
发布于 2019-09-08 08:00:14
NOT IN (<Select .. subquery>)在MySQL中通常是低效的。您还可以使用使用Group By和Having子句的基于条件聚合的筛选来解决此问题：
Select code
From promo_codes
Where promo_id IN (1,2)
Group By code
Having Sum(promo_id = 1)
And Not Sum(promo_id = 2)页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/247280
复制相关文章
相似问题
腾讯云开发者
