Can we replace nested sub-queries with joins - SQL

Question

Currently we are using the following nested queries to get specific result. The query gives me the correct result, but the problem is that it is taking more time to execute(around 2 seconds to fetch 9 records). Records in the table:

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

Following is my query:

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'))))

I think Joins will give better performance(correct me if I'm wrong), hence replaced the nested queries with joins but It doesn't give me the expected result. Joined query like the following:

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'

Can someone tell me what's wrong with the construction of Joins in my query.

Answer 1

try this one out

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')