Sql NOT IN 优化 [英] Sql NOT IN optimization

问题描述

我在优化查询时遇到问题.这是我正在使用的两个示例表:

I'm having trouble optimizing a query. Here are two example tables I am working with:

Table 1:
UID
A
B

Table 2:
UID   Parent
A       2
B       2
C       3
D       2
E       3
F       2

这是我现在正在做的:

Select Table1.UID
FROM Table1 R

INNER JOIN Table2 T ON
R.UID = T.UID

INNER JOIN Table2 E ON
T.PARENT = E.PARENT
AND E.UID NOT IN (SELECT UID FROM Table1)

我试图避免使用 NOT IN 子句，因为对大量记录的性能有明显的阻碍.

I'm trying to avoid using the NOT IN clause because of obvious hindrances in performance for large numbers of records.

我知道避免 NOT IN 子句的典型方法，例如另一个表为 null 的 LEFT JOIN，但似乎无法通过所有其他连接获得我想要的结果.

I know the typical ways to avoid NOT IN clauses like the LEFT JOIN where the other table is null, but can't seem to get what I want with all of the other Joins going on.

如果我找到解决方案，我会继续工作并发布.

I will continue working and post if I find a solution.

这是我想要的结果

在第一次 Inner Join 之后我会有

After the first Inner Join I would have

A
B

在第二个内部连接之后，我会有:

AFter the second Inner join I would have:

A    D 
A    F
B    D
B    F

上面的第二列只是表示它与具有相同父级的其他 UID 匹配，但我仍然需要 As 和 B 作为 UID.

The second column above is just to represent that it is matching to the other UIDs with the same parent, but I still need the As and Bs as the UID.

RDBMS 是 SQL Server 2005、2008r2、2012

RDBMS is SQL server 2005, 2008r2, 2012

在没有索引的查询中声明了表 1

Table1 is declared in the query with no index

DECLARE @Table1 TABLE ( [UNIQUE_ID] INT PRIMARY KEY )

Table2 在唯一 ID 上有聚集索引

Table2 has a clustered index on Unique ID

推荐答案

对此的一般方法是使用 LEFT JOIN 和 where 子句，该子句仅选择非匹配 行:

The general approach to this is to use a LEFT JOIN with a where clause that only selects the non-matching rows:

Select Table1.UID
FROM Table1 R    
JOIN Table2 T ON R.UID = T.UID
JOIN Table2 E ON T.PARENT = E.PARENT
LEFT JOIN Table3 E2 ON E.UID = R.UID
WHERE E2.UID IS NULL 

这篇关于Sql NOT IN 优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！