Predicates using subqueries

Since a subquery is just an expression that appears in the WHERE or HAVING clauses, the search conditions on subqueries may look familiar.

They include:

Comparison predicates using subqueries Compares the value of an expression to a single value produced by the subquery for each record in the table(s) in the main query. Comparison tests use the operators (=, <>, <. <=, >, >=) provided with the subquery.
Quantified comparison test Compares the value of an expression to each of the set of values produced by a subquery.
Subquery set membership test Checks if the value of an expression matches one of the set of values produced by a subquery.
Existence test Checks if the subquery produces any rows.