Question about workflow of Joins.

[okerix]

This question doesn't involve any coding or query making it is simply a question about how Access does joins. If I have a main table(Case) with all the parameters on it and a couple of other queries (QueryA and QueryB) which are RIGHT JOINED on Case. Meaning all the rows from Case will stay and if there is a match to QueryA or QueryB that will be added to the row. Now here is the question. Does access grab the rows from Case that meet the criteria and then make a new query specifically looking for the ids in the two other querys? Or does Access run QueryA and QueryB to its full extent and compare that to Case(after it has applied the criteria). I have tried testing this out by putting the same criteria on QueryA and B as is on Case, but I didnt see any improvement. So I am thinking Access is smart makes a new query based on the IdS that Case pulls back. Anyone know for sure?
Thanks,
Shaun

 

[MSAccessRookie]

Two Observations come to my mind:

1. Case is an Access Reserved Word. Using it as a Table Name or Column Name can have unpredictable results. It should be changed to avoid potential problems (I changed it to theCases for the example in Observation #2).
2. I think your understanding of Joins may be backwards. If theCases is JOINED to Query1, (theCases JOIN Query1 On WhateverTheyJoinOn), adding LEFT or RIGHT will indicate which side uses ALL records, and which side uses MATCHING records only.
   • LEFT JOIN will mean that all records from theCases are used, and only MATCHING records from Query1 are used.
   • RIGHT JOIN will mean that and all records from Query1 are used, and only MATCHING records from theCases are used.

 

[okerix]

1. These are all made up names and I never use them in my db.
2. I understand the Joins it all depends on which side of = sign you want to keep all the records for. In my case it is querya.id = case.id therefore a RIGHT JOIN is used.

Remember this is an access inner workings question not a query writing question. Any ideas on how access handles joins on the inside?