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

Question

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.

score 2 · Accepted Answer · edited Oct 14 '14 at 09:03

2

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

edited Oct 14 '14 at 09:03

WhatEvil

481
5
18

answered Oct 14 '14 at 08:12

Gordon Linoff

1,242,037
58
646
786

Should be `jd.ChildAccountID = anc.AccountID` instead of `jd.ChildAccountID = a.AccountID` in the second subquery. – SylvainL Oct 14 '14 at 08:16
Now fixed with an edit as per SylvainL's suggestion. This does work and provide the same results as my original query. Unfortunately Access doesn't really provide the query analysis tools that you get with MySQL or SQLServer so it's difficult to know which is faster. At the moment this is only testing against about 10 records anyway. I was mostly hoping to gain a better understanding of how to construct queries with EXISTS by example, and this has helped a lot. – WhatEvil Oct 14 '14 at 10:40
Oh, also I think that Access 2013 now uses the same database engine as SQL server, rather than its own "JET Engine" that it used in previous versions, so I would think that the performance differences in Access are likely to be the same as in SQL Server? – WhatEvil Oct 22 '14 at 09:34
1

Starting with the 2007 version, Access uses the ACE engine, which is based on Jet with adaptations to support features introduced in the ACCDB database format. ACE is absolutely not the same as the SQL Server database engine. – HansUp Jan 07 '16 at 19:37

score 1 · Answer 2 · edited Jan 07 '16 at 19:29

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

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

2 Answers2