IN SELECT Query Criteria

[Guest]

I think I read somewhere that using an "IN SELECT..." type of query will
speed up your query performance in Access 2003 better than linking the two
tables by their common field. Is that correct? If so, what is the proper
syntax for creating this type of query criteria?

My scenario is that I have a very long list (1000's) of employees and a
select group of managers (about 30). I only want the employees whose manager
is in my table of selected managers. I should end up with a list of <200.

Is this a good way to use the "IN Select.." as my criteria?

 

[Allen Browne]

I think you are talking about using a subquery rather than a join?

If so, the information you have been given is incorrect.
A join will almost always execute faster than a subquery in JET.

If you want to pursue subqueries, here's a starting point:
http://allenbrowne.com/subquery-01.html

 

[Jamie Collins]

I think I read somewhere that using an "IN SELECT..." type of query will
speed up your query performance in Access 2003 better than linking the two
tables by their common field. Is that correct? If so, what is the proper
syntax for creating this type of query criteria?

My scenario is that I have a very long list (1000's) of employees and a
select group of managers (about 30). I only want the employees whose manager
is in my table of selected managers. I should end up with a list of <200.

Is this a good way to use the "IN Select.." as my criteria?

Using northwind: which employees haven't processed an order?

SELECT E1.EmployeeID
FROM Employees AS E1
WHERE NOT EXISTS (
SELECT *
FROM Orders AS O1
WHERE O1.EmployeeID = E1.EmployeeID);

SELECT E1.EmployeeID
FROM Employees AS E1
WHERE E1.EmployeeID NOT IN (
SELECT E1.EmployeeID
FROM Orders AS O1
WHERE O1.EmployeeID = E1.EmployeeID);

SELECT E1.EmployeeID
FROM Employees AS E1 LEFT JOIN Orders AS O1
ON O1.EmployeeID = E1.EmployeeID
WHERE O1.EmployeeID IS NULL;

All three queries are semantically equivalent; a smart optimizer will
recognise this and use the best plan regardless. How Jet handles each
requires detailed knowledge of its optimizer, which I do not possess.
I can tell you that the NOT EXISTS construct *could* outperform the
NOT IN one because it can 'short circuit' as soon as it finds a match
(you only need to find one for EXISTS to return true), as happens with
SQL Server (I read); what Jet *actually* does may not even be
documented. The LEFT JOIN would definitely (I think <g>) be slower in
the event, albeit unlikely, of the columns in the ON clause being
nullable.

Personally, I find the NOT EXISTS the most the intuitive and the LEFT
JOIN one the least intuitive, and that's what I would use to make my
choice. Minimising maintenance time is more important to me than run
time performance, all other things being equal.

In testing the above the LEFT JOIN ran in 11 milliseconds, the other
two in 10 milliseconds. OK, so I'm only using 10 employees and 900
orders i.e. not far off yours. My point is that performance is not a
factor here because I'm only interested in absolute performance rather
than relative performance.

Try googling the exact phase "premature optimization is the root of
all evil".

Jamie.

--

 

[Guest]

Jamie,

I think you missed the point. He wants to identify those records in table
A, where the Manager_ID is in Table B.

SELECT tbl_Employees.*
FROM tbl_Employees
INNER JOIN tbl_Managers
ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

Dale

 

[Guest]

Thank you for the information (everyone!).

I researched the links and SQL's you all provided and will use the
old-fashioned "join" instead of the subquery to gather my subset of employees
in. I just thought maybe I could come up with a better way of resolving the
query.

However.. in my searching out the answers, I did find a lot of new things to
ponder too! Thanks! cs

 

[Jamie Collins]

I think you missed the point. He wants to identify those records in table
A, where the Manager_ID is in Table B.

SELECT tbl_Employees.*
FROM tbl_Employees
INNER JOIN tbl_Managers
ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

["He"? I once knew a Cyndney with a 'C' and it was a derivative of
'Lucinda'.]

The OP asked, "I think I read somewhere that using an "IN SELECT..."
type of query will speed up your query performance in Access 2003
better than linking the two tables by their common field." I think
it's clear they were asking for a comparison of syntax constructs as
regard performance, which is what I delivered, I feel.

Are you saying that in my examples I should have used IN rather than
NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
accused of nick picking... <g>.

Seriously, in my experience I use NOT EXISTS far more often then
EXISTS, therefore found it easier to dream up a NOT EXISTS example on
the spot, plus the equivalent using a JOIN has more issues because it
requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
handling nullable columns is problematic (see http://tinyurl.com/yunof4).

Jamie.

--

 

[Dale Fye]

Jamie,

I think your examples were a great explaination of the Not Exists and Outer
Joins to select items from one data set where something doesn't exist in
another. I guess I just read the OP differently and assumed that they
wanted to select from one list where the value is found in the other list.
Your examples might have been confusing in that context.

Dale

I think you missed the point. He wants to identify those records in
table
A, where the Manager_ID is in Table B.

SELECT tbl_Employees.*
FROM tbl_Employees
INNER JOIN tbl_Managers
ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

["He"? I once knew a Cyndney with a 'C' and it was a derivative of
'Lucinda'.]

The OP asked, "I think I read somewhere that using an "IN SELECT..."
type of query will speed up your query performance in Access 2003
better than linking the two tables by their common field." I think
it's clear they were asking for a comparison of syntax constructs as
regard performance, which is what I delivered, I feel.

Are you saying that in my examples I should have used IN rather than
NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
accused of nick picking... <g>.

Seriously, in my experience I use NOT EXISTS far more often then
EXISTS, therefore found it easier to dream up a NOT EXISTS example on
the spot, plus the equivalent using a JOIN has more issues because it
requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
handling nullable columns is problematic (see http://tinyurl.com/yunof4).

Jamie.

 

[Jamie Collins]

I guess I just read the OP differently and assumed that they
wanted to select from one list where the value is found in the other list.

Fair enough

Jamie.

--

 

[Guest]

Actually.... You are both correct.
I was needing to know which syntax performed best. I think Dale responded to
that appropriately. However, I also was looking for (as Jamie indicated) the
best way to get a subset of Employees whose manager exists in my smaller list
of managers. I had thought that using the subquery IN Select... as my
criteria would help with performance. Your response that using a JOIN is as
fast if not faster, which answered my question.

I'm sorry, Jamie, but your examples were a bit confusing at first, as Dale
mentioned, but I understood the result of your findings which led me to (I
think) make the proper choice anyway.

Thanks gentlemen.
--
THX cs

Jamie,

I think your examples were a great explaination of the Not Exists and Outer
Joins to select items from one data set where something doesn't exist in
another. I guess I just read the OP differently and assumed that they
wanted to select from one list where the value is found in the other list.
Your examples might have been confusing in that context.

Dale

I think you missed the point. He wants to identify those records in
table
A, where the Manager_ID is in Table B.

SELECT tbl_Employees.*
FROM tbl_Employees
INNER JOIN tbl_Managers
ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

["He"? I once knew a Cyndney with a 'C' and it was a derivative of
'Lucinda'.]

The OP asked, "I think I read somewhere that using an "IN SELECT..."
type of query will speed up your query performance in Access 2003
better than linking the two tables by their common field." I think
it's clear they were asking for a comparison of syntax constructs as
regard performance, which is what I delivered, I feel.

Are you saying that in my examples I should have used IN rather than
NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
accused of nick picking... <g>.

Seriously, in my experience I use NOT EXISTS far more often then
EXISTS, therefore found it easier to dream up a NOT EXISTS example on
the spot, plus the equivalent using a JOIN has more issues because it
requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
handling nullable columns is problematic (see http://tinyurl.com/yunof4).

Jamie.

 

[Michel Walsh]

The reference in http://tinyurl.com/yunof4 is right about the non standard
way Jet handles outer join ON clause implying just one table (or no table),
but that is *not* a 'problem' for the outer join used as a "not in"
equivalence. Further more, NOT IN is poorly optimized in Jet, and an outer
join on the equivalent statement is preferred if runtime execution is of
some concern.



Vanderghast, Access MVP

I think you missed the point. He wants to identify those records in
table
A, where the Manager_ID is in Table B.

SELECT tbl_Employees.*
FROM tbl_Employees
INNER JOIN tbl_Managers
ON tbl_Employees.Manager_ID = tbl_Managers.Manager_ID

["He"? I once knew a Cyndney with a 'C' and it was a derivative of
'Lucinda'.]

The OP asked, "I think I read somewhere that using an "IN SELECT..."
type of query will speed up your query performance in Access 2003
better than linking the two tables by their common field." I think
it's clear they were asking for a comparison of syntax constructs as
regard performance, which is what I delivered, I feel.

Are you saying that in my examples I should have used IN rather than
NOT IN, EXISTS rather than NOT EXISTS, etc? And to think *I* get
accused of nick picking... <g>.

Seriously, in my experience I use NOT EXISTS far more often then
EXISTS, therefore found it easier to dream up a NOT EXISTS example on
the spot, plus the equivalent using a JOIN has more issues because it
requires an OUTER JOIN for which Jet is not SQL-92 compliant hence
handling nullable columns is problematic (see http://tinyurl.com/yunof4).

Jamie.