Did some research in SQL92. Below is a good definition of subqueries
and their general usage (source: SQL : Instant Reference - Martin Gruber)

"Subqueies are defined as queries that produce data that is not the
final output of the statement, but will further processed within
statement. Subqueries can be used in the predicates of other
queries, of DELETE or UPDATE statememnt or of constraints, and may be
used as well in the FROM clauses of queries, in row value
constructors, or in value expressions. Wherever used, they are
enclosed in parentheses. There are three types of subqueries:

* subqueires that can produce any number of rows are called "table
subqueries"

* subqueries that can produce no more than one row, but may contain
more than one value in that row are called "row subqueries"

* subqueries that can produce no more than one value are called
"scalar subqueries"

Scalar subqueries are used in comparison predicates that are not
quantified (in other words, do not use ANY, ALL, or SOME), and, in
SQL92, may be used anywhere value expressions may be unless explicitly
excluded. Row subqueries may be used in row value constructors, and
table subqueries are used in FROM clauses and in those predicates
other than simple comparison that uses subqueries."

It is an error to use a query that returns more than one row or more
than one column as a scalar subquery. (But if, during a particular
execution, the subquery returns no rows, there is no error; the scalar
result is taken to be NULL.)

Sometimes also called an expression subquery.

Kurgbe