Scope of Names in Queries and Sub-queries #msaccess #subqueries

I have been running into some subtle little problems with queries and sub-queries that suggest to me that I don’t have an adequate understanding of the scope of names within SQL. To that end, I created the following very trivial queries to test against both SQLite and MS Access.

--  Test query #1
SELECT Z.PersonID FROM
  (
   SELECT P.PersonID
     FROM PersonTable AS P
     WHERE P.PersonID <= 5
  ) AS Z
 
--  Test query #2
SELECT P.PersonID FROM
  (
   SELECT P.PersonID
     FROM PersonTable AS P
     WHERE P.PersonID <= 5
  ) AS Z
 
  --  Test query #3
SELECT PersonID FROM
  (
   SELECT P.PersonID
     FROM PersonTable AS P
     WHERE P.PersonID <= 5
  ) AS Z

All three queries use the identical sub-query. The only thing the sub-query does is to create a virtual table containing one column and up to five rows, where the column is the PersonID from the PersonTable and the rows are the PersonID values from 1 to 5. In my database, all five rows exist. The five rows may or may not all exist in any other database depending on whether or not the first five people that were entered into the database have been deleted or merged since they were first entered, or indeed whether as many as five people were ever entered into the database.

Query #1 works in both SQLite and MS Access (except that you have to delete the comment for MS Access). This is the expected behavior. It seems to be the case that SQL is always happy with one alias for a table in the sub-query (the alias is P in this case) and another alias for the same table outside the sub-query (the alias is Z in this case).

Query #2 fails in SQLite and works in MS Access (except that you have to delete the comment for MS Access). I think the query should fail in both cases, and I find the fact that it works in MS Access to be extremely disappointing. It appears that SQLite is treating the alias P as local to the sub-query but that MS Access is treating the alias P as global to the entire query, both inside and outside the sub-query. That seems to explain some rather strange looking errors I have seen from MS Access when I used the same alias both inside and outside a sub-query or when I used the same alias in two different and (I thought) independent sub-queries. It’s beginning to seem to me that to use MS Access safely, any table that is aliased multiple times in the same query will have to have a distinct alias each time, even though would seem that sensible scope rules would not really make distinct aliases necessary. Which is to say, it sounds like you can’t say that aliases in MS Access have to be “unique within the same scope but don’t have to be unique when they appear in different scopes” because MS Access seems to have only one scope – namely the whole query. I may not be completely correct about MS Access’s scope rules, but treating all scopes as global and programming accordingly seems to be the only thing that always works safely for me in the MS Access environment.

Query #3 works works in both SQLite and MS Access (except that you have to delete the comment for MS Access). I suppose that’s ok, but it makes me uncomfortable to write my code that way where the table that goes with a column is implicit, even though the table in question is a virtual table created with a sub-query. So I will write my query/sub-query combinations in the style of Query #1 rather than in the style of Query #3.

Jerry