Types of Subqueries

There are two basic types of subqueries: expression and quantified predicate.

Expression subqueries are introduced with an unmodified comparison operator, must return a single value, and can be used almost anywhere an expression is allowed in SQL.
Quantified predicate subqueries operate on lists that are introduced with in or with a comparison operator that is modified by any or all. Quantified predicate subqueries return zero or more values. This type is also used as an existence test (which checks whether a subquery produces any rows), introduced with exists.

Subqueries of either type are either noncorrelated or correlated (repeating).

A noncorrelated subquery can be evaluated as if it were an independent query. Conceptually, the results of the subquery are substituted in the main statement, or outer query. This is not how SAP ASE actually processes statements with subqueries. Noncorrelated subqueries can alternatively be stated as joins and are processed as joins by SAP ASE.
A correlated subquery cannot be evaluated as an independent query, but can reference columns in a table listed in the from list of the outer query.

Expression Subqueries
Expression subqueries include subqueries in a select list (introduced with in) and in a where or having clause connected by a comparison operator (=, !=, > , > =, <, <=)
Quantified Predicate Subqueries
Quantified predicate subqueries, which return a list of zero or more values, are subqueries in a where or having clause that are connected by any, all, in, or exists. The any or all subquery operators modify comparison operators.
Subqueries Used with in
Subqueries that are introduced with the keyword in return a list of zero or more results.
Subqueries Used with not in
Subqueries that are introduced with the keyword phrase not in also return a list of values that are zero (0) and greater.
Subqueries Using not in with NULL
A subquery that uses not in returns a set of values for each row in the outer query.
Subqueries Used with exists
Use the exists keyword with a subquery to test for the existence of some result from the subquery.
Subqueries Used with not exists
not exists is just like exists except that the where clause in which it is used is satisfied when no rows are returned by the subquery.
Find Intersection and Difference with exists
You can use subqueries that are introduced with exists and not exists for two set theory operations: intersection and difference.
Subqueries Using SQL Derived Tables
You can use SQL derived tables in subquery from clauses.

Parent topic: Subqueries: Queries Within Other Queries