chore(docs): update subquery documentation with more information

[sanderson]

Which issue does this PR close?

This doesn't close an issue, but adds more detailed subquery documentation.

Part of #7013

Rationale for this change

Provides more information about how to use subqueries and what types of subqueries can be used.

What changes are included in this PR?

It just updates the subquery documentation.

Are these changes tested?

All examples in the proposed changes have been tested.

Are there any user-facing changes?

Nothing in the product itself. Just documentation.