Functionally, the three types of subqueries are: subqueries that return a single value, subqueries that return zero to many values, and subqueries that test existence.
Subqueries designed to return a single value are also known as scalar subqueries. You typically use scalar subqueries to obtain some value from a set of data, such as a count of all employees and their average salary. Scalar subqueries are used in Where and Having clauses, but can also be used as query fields in databases.
Subqueries that return zero to many values are used to generate lists. For example, you could create a subquery to generate a list of products not purchased by any customers in Ohio. This type of subquery is only used in Where and Having clauses.
The final type of subquery tests for existence of data. The data itself is not important, only whether the data exists. Existence subqueries are only used in Where and Having clauses. A typical use of an Existence subquery might be to discover which customers made purchases within some range of time. It does not matter how many purchases the customers made or what they bought. It an Existence subquery, it only matters whether they made at least one purchase of any kind.
Another important property of subqueries is called correlation (see Creating a Correlated Subquery). Correlation is the process of using a value returned by one query in the execution of another query. For every row returned by the first query, the second query is executed. Some of the subqueries you create will need to be correlated, and some will not (see Creating an Uncorrelated Subquery). It all depends on what you are trying to do.
You can nest subqueries to theoretically unlimited levels. In other words, a subquery may have a subquery of its own. In practice, nesting subqueries to a large number of levels is probably a good indication that you should rethink what you are trying to do. It is sometimes better to add more tables and join them effectively rather than creating subqueries.