I am trying to understand when sub-queries would be used in the FROM clause of a SELECT statement. Are there circumstances under which you have to use one? I would appreciate any guidance on this subject. Any examples would be a bonus.
There are many circumstances under which you would use a sub-query in a FROM clause. And a sub-query can take numerous forms which is evident when you look at the t-sql reference for FROM. One typical usage is called a "derived table". Those can be used to greatly reduce the number of rows considered before joining to another table. Another reason could be that you want to aggregate some values to narrow down to a group of rows to correlate to another table.
As for examples probably a good thing to do is look through a bunch of sqlteam.com posts. I bet you won't have to look far before seeing some examples.
Thanks for your reply TG. I hadn't go found much info on this subject until you mentioned derived tables, a term I had forgotten about. Now I have read a number of articles on this and related topics, such the difference between correlated and non-correlated sub-queries.