What is going on behind the scenes when I create a query that works with other queries in access?

If I have a couple of queries in Micr开发者_如何学运维osoft Access that I then use in another new query, does access independently run each of the contained queries and then use those results as the new tables in the new query, or does it try to merge all of the subqueries into a new single query?

For example, say I have a query that returns a teacherID and a teacherName, then I have another query that returns a studentID, teacherID, and a studentName. Then I try to use both of these queries together in a new query that does something like

SELECT qryTeacher.teacherName, qryStudent.studentName 
FROM qryTeacher INNER JOIN qryStudent 
WHERE qryStudent.teacherID=qryTeacher.teacherID;

Would this execute the qryTeacher query and the qryStudent query and then run this query on the results, or would it try to build some new query using all three queries?

Also, does it matter if the source is a linked SQL table vs an access .mdb source?

If you want to know the details of how the database engine handles your queries, search the net for "Jet ShowPlan".

It all gets quite complex "under the hood". And even the same query may not be executed the same way twice in a row. For example, with new updated index statistics, the query planner may identify a more efficient route to retrieve the requested data.

More directly to the point of your question ... if you create a query which pulls data from two other queries, do not assume those two are executed separately before being combined or fed to the third. It could happen that way. But the optimizer will make judgments about what it considers to be the most efficient way to retrieve the data, and will re-organize the query plan as it sees fit.