You can use EXPLAIN and similar keywords to see the execution plans in common SQL database engines. In practice you don't really care about the actual intermediate data so it doesn't show it, usually it's enough to learn whether indices are used at every step.

But you could in many cases easily infer from the execution plan what a query would look like and fetch an intermediate set separately.