mysql SELECT NOT IN()-脱节集? [英] mysql SELECT NOT IN () -- disjoint set?

问题描述

我在使查询正常工作时遇到问题，我认为这应该工作.格式为

I'm having a problem getting a query to work, which I think should work. It's in the form

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中不存在.

But mysql chokes where "IN (" starts. Does mysql support this syntax? If not, how can I go about getting these results? I want to find distinct tuples of (a,b,c) in table 1 that don't exist in table 2.

推荐答案

您应使用不存在的

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，DBMS则必须读取实际数据并创建完整的结果集，随后需要检查该结果集.

Using NOT IN is not the best method to do this, even if you check only one key. The reason is that if you use NOT EXISTS the DBMS will only have to check indices if indices exist for the needed columns, where as for NOT IN it will have to read the actual data and create a full result set that subsequently needs to be checked.

使用LEFT JOIN然后检查NULL也是一个坏主意，当表很大时，它将很痛苦地缓慢，因为查询需要进行整个联接，完全读取两个表并随后丢弃很多表.另外，如果这些列允许使用NULL值，则检查NULL将报告误报.

Using a LEFT JOIN and then checking for NULL is also a bad idea, it will be painfully slow when the tables are big since the query needs to make the whole join, reading both tables fully and subsequently throw away a lot of it. Also, if the columns allow for NULL values checking for NULL will report false positives.

这篇关于mysql SELECT NOT IN()-脱节集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！