You can use Where and Having clauses to create nested subqueries that return zero to many values. A subquery in a Where or Having clause is always part of a larger conditional statement. The syntax for using subqueries in Where and Having clauses can be broken down into five basic forms:
<expression> <comparison operator> <subquery> <expression> <comparison operator> [ ANY | SOME ] <subquery> <expressions> <comparison operator> ALL <subquery> <expression> [NOT] IN <subquery> [NOT] EXISTS <subquery>
The query that contains the Where or Having clause is called the outer query, which makes the subquery in the Where or Having clause the inner query.
Subqueries in Where or Having clauses generally produce a result set that consists of a single column of zero or more rows. For this reason, the select list of the subquery can only include a single expression or column name. In addition, the database column or expression in the select list must also be join-compatible with the value on the left side of the Where or Having clause in the outer query.