旧的 IN 与 Exists 与 Left Join（其中 ___ 为或不为空）；表现
我发现自己陷入了困境。我的表只有一列（抑制或包含列表），或多或少是 varchar(25) 但问题是在主查询中使用它们之前我没有时间对它们进行索引，并且取决于它的重要性，我不知道每个表中有多少行。所有这一切的核心基表大约有 140 万行和大约 50 列。
我的假设如下：
IN 不应在返回大量值（行）的情况下使用，因为它会按顺序查看值，对吗？ （子查询上的 IN 不直接传递值）
连接（INNER 用于包含，LEFT 以及在抑制时检查 Null）对于大型数据集（超过 1k 行左右）来说是最好的，
EXISTS 一直让我担心，因为它似乎正在为每一行（全部 140 万行？哎呀。）
我的直觉说，如果可行的话，获取抑制表的计数并使用 IN（对于低于 1k 行）和 INNER/LEFT Join（对于上面的抑制表） 1k 行）注意，我要抑制的字段将是大基表中的索引，但抑制表不会。想法？
预先感谢您的任何和所有评论和/或建议。
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.
My assumptions are as follows:
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)
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 has always concerned me because it seems to be doing a subquery for every row (all 1.4 million? Yikes.)
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
假设 TSQL 表示 SQL Server，您是否看过有关 NOT IN、NOT EXISTS 和 LEFT JOIN IS NULL 比较的链接？综上所述，只要比较的列不能为NULL，
NOT IN和NOT EXISTS比LEFT JOIN/IS NULL效率更高...关于 IN 和 EXISTS 之间的区别需要记住的一点 - EXISTS 是一个布尔运算符，并且在第一次满足条件时返回 true。尽管您在语法中看到了相关子查询，但 EXISTS 的性能比 IN 更好...
此外，IN 和 EXISTS 仅检查值比较是否存在。这意味着不会像加入时那样发现重复的记录...
这实际上取决于，因此，如果您真的想找到性能最好的记录，则必须进行测试和测试。比较查询计划正在做什么......
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 INandNOT EXISTSare more efficient thanLEFT JOIN/IS NULL...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...
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...
无论您使用什么技术，如果您应用过滤器或联接的表上没有索引，系统都会执行表扫描。
RE: Exists
系统不一定会对所有 140 万行执行子查询。 SQL Server 足够智能，可以执行内部 Exists 查询，然后根据主查询对其进行评估。在某些情况下，Exists 的性能可以等于或优于 Join。
It won't matter what technique you use, if there is no index on the table on which you apply a filter or join, the system will do a table scan.
RE: Exists
It is not necessarily the case that the system will do a subquery for all 1.4 million rows. SQL Server is smart enough to do the inner Exists query and then evaluate that against the main query. In some cases, Exists can perform equal to or better than a Join.