sqlnes (7/17/2013)
2 index scans are unavoidable?
That's correct. You can have an index scan too if you want - just up the ratio of looked-up rows to parent rows, but you're stuck with the scans. Like I said earlier, check the actual execution plans of the two alternative ways of writing the query. Run them together (run the prep part as a batch, then the two queries as a separate batch) to compare the plans one above the other. If you are unsure of what I mean by "not all scans are equal", look at the scan operators for the lookup tables in each plan.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden