旧的IN vs. Exists vs. Left Join(___为空或不为null的位置)；表现 [英] The old IN vs. Exists vs. Left Join (Where ___ Is or Is Not Null); Performance
问题描述
我发现自己的内心很不自在.我只有或多或少是varchar(25)的一列表(压缩列表或包含列表)，但问题是在主查询中使用它们之前我没有时间对其进行索引，并且取决于它的重要性，我不知道每个表中有多少行.所有这些的核心基础表是大约140万行和大约50列.
I have found my self in quite a pickle. I have tables of only one column (supression or inclusion lists) that are more or less varchar(25) but the thing is I won't have time to index them before using them in the main query and, depending how inportant it is, I won't know how many rows are in each table. The base table at the heart of all this is some 1.4 million rows and some 50 columns.
我的假设如下:
在返回很多值(行)的情况下，不应使用IN，因为它会顺序查看这些值，对吗? (在子查询上的IN不能直接传递值)
IN shouln't be used in cases with a lot of values (rows) returned because it looks though the values serially, right? (IN on a subquery not passed the values directly)
联接(INNER用于包含和左移，并在压缩时检查Null)最适合大型数据集(超过1k行左右)
Joins (INNER for inclusion and LEFT and checking for Nulls when supression) are the best for large sets of data (over 1k rows or so to mach to)
EXISTS一直很关注我，因为它似乎对每一行都进行子查询(全部为140万个查询).
EXISTS has always concerned me because it seems to be doing a subquery for every row (all 1.4 million? Yikes.)
我的直觉说，如果可行，请获取抑制表的计数，并使用IN(用于子1k行)和INNER/LEFT Join(用于1k行以上的抑制表)注意，我要禁止的字段将是大基表中的索引，但压缩表则不会.有想法吗?
My gut say, if feasable get the count of the supression table and use either IN (for sub 1k rows) and INNER/LEFT Join (for suppression tables above 1k rows) Note, and field I will be supressing on will be index in the big base table but the supression table won't be. Thoughts?
在此先感谢您提供任何和所有评论和/或建议.
Thanks in advance for any and all comments and/or advice.
推荐答案
Assuming TSQL to mean SQL Server, have you seen this link regarding a comparison of NOT IN, NOT EXISTS, and LEFT JOIN IS NULL? In summary, as long as the columns being compared can not be NULL, NOT IN and NOT EXISTS are more efficient than LEFT JOIN/IS NULL...
需要注意IN和EXISTS之间的区别-EXISTS是一个布尔运算符，在第一次满足条件时返回true.尽管您在语法上看到一个相关的子查询，但是EXISTS的性能要优于IN ...
Something to keep in mind about the difference between IN and EXISTS - EXISTS is a boolean operator, and returns true on the first time the criteria is satisfied. Though you see a correlated subquery in syntax, EXISTS has performed better than IN...
此外，IN和EXISTS仅检查值比较的存在.这意味着没有重复的记录，就像您在加入...时发现的一样.
Also, IN and EXISTS only check for the existence of the value comparison. This means there's no duplication of records like you find when JOINing...
这确实取决于实际情况，因此，如果您真的想找到最有效的方法，则必须进行测试和测试.比较查询计划在做什么...
It really depends, so if you're really out to find what performs best you'll have to test & compare what the query plans are doing...
这篇关于旧的IN vs. Exists vs. Left Join(___为空或不为null的位置)；表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
