OK, next is JOIN vs WHERE
The difference between JOINs and using a WHERE clause is trickier in one way, but it is again related to finding records to be acted upon. BOTH methods ultimately relate to finding the correct records within the set of all records in your data sources.
One trivial difference is that a WHERE clause can be used in almost any case you can name including a single-table search. A JOIN is always used when you are looking at two sources of records (or more). So let's not worry about the single-table case, it leads to a boringly quick discussion.
I'll focus on the two-source case for simplicity. It can be two different tables or it can be two views of the same table (informally called a self-JOIN because the ANSI SQL standard doesn't call out anything special about that case.) You have to also remember this: Access and every other SQL engine in the world that follows the ANSI standard is based on
set theory. Set theory is all about combinations and permutations.
Access cannot proceed with any other action until it makes a list of all of the records to be affected by that action, even including the simple case of building a datasheet for display purposes. This becomes the set of records of interest. The syntax that identifies tables is used to limit the scope of the set of interest. The more tables (or queries) you name as sources, the bigger the scope. Then you get to syntax related to reducing the size of that set so that it won't take forever.
In the JOIN case, you have
SELECT bunch-of-fields FROM A INNER JOIN B on A.field = B.other-field WHERE.... and the idea is that you are taking two
sets of records to create a
single virtual record formed by combining those two sources. The JOIN clause tells Access how to quickly put together the two souces - in this case by finding equality between specific fields in the two different sources. The JOIN syntax tells Access to consider the joined virtual record as a unit for the purposes of what you are trying to do. It alleviates the need for you to write code that does OpenRecordset on the two sources and to make a programmatic double-loop scan to find matching records.
But there is another way to do this same thing. You can write
SELECT bunch-of-fields FROM A, B WHERE ( A.field = B.other-field ) AND ... with the expressed purpose that again you will
eventually get the same exact set of records as the JOIN. Nobody said that there was only one way to do a given job in Access. Sometimes there IS only one way. This isn't one of those times. But there IS a danger here. If you chose to use the WHERE method and forgot the
(A.field = B.other-field) portion, you invite something called a Cartesian JOIN. This nomenclature reveals that in fact the two methods (JOIN vs. WHERE) are related.
A Cartesian JOIN (also called a permutation JOIN) occurs because this is done via SET theory. (Remember that comment from above?) The way that the syntax works, that
FROM A,B clause says take EVERY record from set A and combine each record in A with EVERY record from set B. So you get records A1B1, A1B2, A1B3, A1B4, ... A2B1, A2B2, A2B3, A2B4, ... AnB1, AnB2, ... AnBn... EVERY COMBINATION in the sets of records A and B. Then the WHERE clause gets to filter out non-matching cases, which is what WHERE clauses do.
The power of the JOIN is that it allows you to tell Access how to more quickly reduce the set of records. And this is important because it turns out that order of operation matters - a LOT. This article might help.
SQLBolt provides a set of interactive lessons and exercises to help you learn SQL
sqlbolt.com
In it, you learn that SQL performs gathering actions first, and that
specifically includes JOINs as a way of determining the records in question. The filtration implied by a WHERE clause comes SECOND in the list of internal SQL actions. Indeed, you CAN have both JOIN and WHERE clauses in a query. They just aren't implemented at the same time.
The WHERE clause in a JOIN (because you can have both elements) has less data to consider because the JOIN syntax allowed earlier elimination of record combinations that didn't match the JOIN criteria. And the key there is that if you have fewer records on which to work, everything is faster.
Now, getting back to the other part of the original question, if you have indexed the fields on which the JOIN is to occur, Access has the ability to make its JOIN evaluation based on index scans rather than linear table scans. Which is where indexes on fields to be JOINed become so valuable. We can easily say that either syntax (JOIN vs. WHERE) will eventually reduce the data set to whatever was intended. But how long will that take? The answer depends on (a) having indexes on the fields being used for selection criteria and (b) having JOINs in play to more rapidly reduce the size of the set of records to be considered for subsequent operations.
If you look at the linked article, you realize we still haven't touched on things like GROUP BY clauses, HAVING clauses, ORDER BY clauses, and the DISTINCT keyword.
In summary, what good is an indexed JOIN? It gets you more quickly to the part where your query actually starts working on the correct list of data.