I "liked" your post,
@ebs17, but I'll also add that your point about large queries is EXTREMELY significant.
People don't always take this into account, but there is an order to the steps of query execution.
Understanding query order can help you diagnose why a query won't run, and will help you optimize your queries to run faster.
www.sisense.com
First, SQL evaluations the FROM clause or clauses. In practice this means SQL makes a list of tables. This list has to go into the workspace, which is usually associated with the front end. This list isn't that big.
Second, the WHERE clauses. This means SQL has to make a list of records not filtered out by the WHERE clauses. Depending on the exclusivity of the WHERE clause, this could be a short list or a VERY long list. And that ALSO goes into the workspace. When you are dealing with "weak" WHERE clauses and a big table, this list could get very long. Folks sometimes observe "bloat" in a front-end file and wonder how it originates because all of their tables are in the back-end. Well, this is how it happens. For small tables or highly restrictive WHERE clauses, the "record list" isn't so big. For larger data sets such as are described in this case, that list won't be trivial either.
The rest of the steps deal with grouping and ordering, which doesn't change the list size that much. By the third step, the damage is done.