CODEWITHSUNDEEP
sqlms-access
WhatEvil
WhatEvil

Reputation: 481

Would it be better to rewrite this query using EXISTS instead of IN? How would I do that?

I currently have the following, and I've read that it's generally better to avoid "IN" and use "EXISTS" instead (1, 2). Though I've read that EXISTS is faster and more consistent, I don't think I've grasped entirely why that is, or how I would go about rewriting this to use EXISTS instead.

SELECT qryAccountNamesConcat.AccountID, qryAccountNamesConcat.AccountName, qryAccountNamesConcat.JobTitle
FROM qryAccountNamesConcat
WHERE (((qryAccountNamesConcat.AccountID) In (
    SELECT AccountID
    FROM tblAccount
    WHERE AccountTypeID IN (1, 2)) 
Or 
qryAccountNamesConcat.AccountID In (
    SELECT ChildAccountID 
    FROM qryAccJunctionDetails
    WHERE ParentAccountTypeID IN (1, 2))
));

Basically, Where AccountTypeID = 1 or 2 this is a trade or private customer account, so I am looking for accounts which are, or which are children of (usually employees of) customer accounts.

Upvotes: 0

Views: 698

Answers (2)

SylvainL
SylvainL

Reputation: 3938

Depending on the data, either one or the other can be the fastest; so you have to try it with your own data to know which one will be the fastest for your query.

As for the inconsistency for IN, there are problems when there is the possibility of having a Null value. For example, while the following first query will return a row; the other two won't return anything:

Select 1 where 1 in (1, Null);
Select 1 where 2 Not in (1, Null);
Select 1 where Null in (1, Null);

This example is for SQL Server. For MS Access, I think that you have to specify a FROM MyTable statement to try it. (And of course, the number of rows returned by the first query will be equal to the total number of rows in the table MyTable.)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I do not know if exists is better than in for MS Access (although exists often performs better than in in other databases). However, you would write it as:

SELECT anc.AccountID, anc.AccountName, anc.JobTitle
FROM qryAccountNamesConcat as anc
WHERE EXISTS (SELECT 1
              FROM tblAccount as a
              WHERE a.AccountTypeId in (1, 2) and anc.AccountID = a.AccountID
             ) OR
      EXISTS (SELECT 1
              FROM qryAccJunctionDetails as jd
              WHERE jd.ParentAccountTypeID in (1, 2) and jd.ChildAccountID = anc.AccountID
             );

For best performance, you will want an index on tblAccount(AccountId, AccountTypeId) and qryAccJunctionDetails(ChildAccountID, ParentAccountTypeID).

Upvotes: 2

Related Questions