SubQuerying Vs Joining

From: Awah Teh (awaht_at_digicentriq.com)
Date: 10/30/03 

Date: Wed, 29 Oct 2003 15:49:19 -0800

Which is most effective (consider the query below for an example)?
Please give me some explanation (FMI:) as to why one is faster over the
other.

--Both Queries are designed to get the users information of users that
received passes in the Year of 2003

    --** ***************
    --** QUERY 1
    --** ***************
    Select * from users where user_id in (select user_id from
users_site_passes where date_pass_issued >= 'January 1, 2003')

    --** ***************
    --** QUERY 2
    --** ***************
    Select users.* from users, users_site_passes where users.user_id =
users_site_passes.user_id and users_site_passes.date_pass_issued >= 'January
1, 2003'

Thanks In Advance
A- 

-- 
Awah Teh
Chief Executive Officer
DigicentriQ Technologies, LLC
awaht@digicentriq.com
www.digicentriq.com
877 675 4742
805 732 9421

Relevant Pages

  • Re: Autofilter in a form using query
    ... I have a query and a form that have the following fields - ... something else to automatically update the other fields ... >explanation, ... >Steve Clark, Access MVP ...
    (microsoft.public.access.queries)
  • Re: Average Sales Query.
    ... Is there an explanation ... > somewhere of exactly how it works when there are ties? ... FROM Students ... If you don't include the ORDER BY clause, the query will return an arbitrary ...
    (microsoft.public.access.queries)
  • Re: Access crashes when using a query field in report
    ... The multi-level group-by is a very common problems with reports based on a query with a subquery. ... Here's a brief explanation and some alternatives: ...
    (microsoft.public.access.reports)
  • Re: Tricky problem cross-referencing two tables
    ... but I don't understand your explanation at all. ... >> Open the Query in design view, switch to SQL view, and copy and paste the text ... >>> Thanks a million, John, for so promptly giving me the answers you did. ... >>> what I know of Excel, I can see the criteria you're suggesting should return ...
    (microsoft.public.access.queries)
  • Re: delete query will not work with tables joined in the query
    ... That error occurs when Jet cannot identify the unique record that is to be ... Post the SQL statement that produces this error, ... explanation of which table's records are to be deleted, ... Our delete query won't ...
    (microsoft.public.access.tablesdbdesign)