代码之家 › 专栏 › 技术社区 › sujith karivelil

我们能用join-sql替换嵌套的子查询吗?

sqlperformance subquery join sql-server sql

-1

sujith karivelil · 技术社区 · 7 年前

目前,我们使用以下嵌套查询来获取特定的结果。查询给出了正确的结果,但问题是执行起来需要更多的时间(大约2秒来获取9条记录)。表中记录:

Table      |  Number of Records
-------------------------------
Account          284
AccountUser       34
AccountCustomer  256
AccountGroup      96

以下是我的问题:

SELECT * FROM Account  where (SomeID = 'XXXXX-XXXXX-XXXXX') and  AccountID  IN  
(SELECT AccountID   FROM AccountUser WHERE SomeID = 'XXXXX-XXXXX-XXXXX' AND AccountID IN  
(SELECT AccountID   FROM AccountCustomer WHERE SomeID = 'XXXXX-XXXXX-XXXXX' AND isDeleted = 0  AND someOtherID IN 
(SELECT someOtherID FROM AccountGroup   WHERE AccountGroupID = 'YYYY-YYYYY-YYYY' AND AccountGroup.SomeID = 'XXXXX-XXXXX-XXXXX' AND AccountID NOT IN  
(SELECT AccountID   FROM AccountGroup WHERE AccountGroupID = 'YYYY-YYYYY-YYYY' AND AccountGroup.SomeID = 'XXXXX-XXXXX-XXXXX'))))

我认为加入会有更好的表现( 如果我错了就纠正我 ,因此将嵌套查询替换为联接,但它没有给出预期的结果。联接查询如下:

SELECT a.* FROM Account a
Inner join AccountUser b on a.AccountID = b.AccountID
Inner join AccountCustomer c on c.AccountID = a.AccountID 
Inner join AccountGroup d on d.AccountID = a.AccountID 
Inner join AccountGroup e on e.AccountID = a.AccountID 
WHERE a.SomeID = 'XXXXX-XXXXX-XXXXX' and 
      b.SomeID = 'XXXXX-XXXXX-XXXXX' and 
      c.SomeID = 'XXXXX-XXXXX-XXXXX' and 
      c.isDeleted = 0 and d.AccountGroupID = 'YYYY-YYYYY-YYYY' and
      d.SomeID = 'XXXXX-XXXXX-XXXXX' and
      e.AccountGroupID ='YYYY-YYYYY-YYYY'

有人能告诉我查询中连接的构造有什么问题吗?

4 回复 | 直到 7 年前

sujith karivelil 7 年前

试试这个

SELECT distinct a.* FROM Account a
Inner join AccountUser b on a.AccountID = b.AccountID
Inner join AccountCustomer c on c.AccountID = a.AccountID 
Inner join AccountGroup d on d.someOtherID = c.someOtherID 
WHERE a.SomeID = 'XXXXX-XXXXX-XXXXX' and 
      b.SomeID = 'XXXXX-XXXXX-XXXXX' and 
      c.SomeID = 'XXXXX-XXXXX-XXXXX' and 
      c.isDeleted = 0 and d.AccountGroupID = 'YYYY-YYYYY-YYYY' and
      d.SomeID = 'XXXXX-XXXXX-XXXXX' and
      d.AccountID  not in 
(SELECT AccountID   FROM AccountGroup WHERE AccountGroupID = 'YYYY-YYYYY-YYYY' AND AccountGroup.SomeID = 'XXXXX-XXXXX-XXXXX')

Parthasarathi Adhikary 7 年前

我能发现两个问题。

列名称错误

IsDeleted=0更改为C.IsRelated=1
连接d和e的错误条款

账户ID不在

Adam 7 年前

Inner join AccountGroup d on d.AccountID = a.AccountID

应该是

Inner join AccountGroup d on d.someOtherID = a.AccountID

Tom 7 年前

除了使用连接之外,您还可以研究使用Where exists或Where not exists,而不是语句中的丑陋部分。联接有时返回的行数可能比用IN语句检索的多。从查询中,我看到您只需要account表中的数据,所以exists似乎是更合理的方法。