mysql SELECT NOT IN () -- 不相交集？

Question

我在查询工作时遇到问题，我认为这应该有效。它的形式是

SELECT DISTINCT a, b, c FROM t1 WHERE NOT IN ( SELECT DISTINCT a,b,c FROM t2 ) AS alias

但是 mysql 在“IN (”开始的地方被阻塞。mysql 支持这种语法吗？如果不支持，我怎样才能得到这些结果？我想在表 1 中找到 (a,b,c) 的不同元组表2中不存在。

Answer 1

您应该使用 not contains：

SELECT DISTINCT a, b, c FROM t1 WHERE NOT EXISTS (SELECT NULL FROM t2 WHERE t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)

即使您只检查一个键，使用 NOT IN 也不是执行此操作的最佳方法。原因是，如果您使用 NOT EXISTS，DBMS 只需检查所需列是否存在索引，而对于 NOT IN，则必须读取实际数据并创建随后需要检查的完整结果集。

使用 LEFT JOIN 然后检查 NULL 也是一个坏主意，当表很大时，它会非常慢，因为查询需要进行整个连接，完全读取两个表，然后丢弃大量表。此外，如果列允许 NULL 值，检查 NULL 将会报告误报。

Answer 2

即使提供了答案，我也无法找出执行此查询的正确方法；然后我找到了我需要的 MySQL 文档参考：

SELECT DISTINCT store_type
FROM stores 
WHERE NOT EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type);

我必须绞尽脑汁的技巧是使用子查询内第一个查询中对“stores”表的引用。希望这会有所帮助（或帮助其他人，因为这是一个旧线程。）

来自 http://dev.mysql.com/doc/refman/5.0/en/exists-and-not-exists-subqueries.html

Answer 3

SELECT DISTINCT t1.* FROM t1 LEFT JOIN t2 ON (t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c) WHERE t2.a 为 NULL

Answer 4

据我所知，NOT IN 一次只能用于 1 个字段。并且该字段必须在“WHERE”和“NOT IN”之间指定。

（编辑：）
尝试使用 NOT EXISTS：

SELECT a, b, c 
FROM t1 
WHERE NOT EXISTS 
   (SELECT * 
   FROM t2 
   WHERE t1.a = t2.a AND t1.b = t2.b AND t1.c = t2.c)

此外，a、b 和 c 相等的内部联接应该为您提供所有非 DISTINCT 元组，而带有 WHERE IS NULL 子句的 LEFT JOIN 应该为您提供 DISTINCT 元组，正如 Charles 提到的以下。

Answer 5

好吧，尽管其他人给出了很多很好的建议，我还是要回答我自己的问题。

这是我想要做的事情的正确语法。

SELECT DISTINCT a, b, c FROM t1 WHERE (a,b,c) NOT IN ( SELECT DISTINCT a,b,c FROM t2 )

不能保证它的效率，但我隐含提出的更广泛的问题是“如何用 SQL 表达这个想法”，而不是“如何获得特定的结果集”。我知道这对所有被刺伤的人不公平，对不起！

Answer 6

需要在 WHERE 子句后添加列列表并删除别名。

我用类似的表对此进行了测试，它正在工作。

SELECT DISTINCT a, b, c 
FROM t1 WHERE (a,b,c)
NOT IN (SELECT DISTINCT a,b,c FROM t2)

使用 mysql world 数据库：

-- dont include city 1, 2
SELECT DISTINCT id, name FROM city 
WHERE (id, name) 
NOT IN (SELECT id, name FROM city  WHERE ID IN (1,2))