a SELECT statement that returns values that satisfy the stated search criterion.
The subquery must:
- Be enclosed in parentheses.
- Not end with a semicolon.
- Select the same number of expressions as are defined in the expression list.
- Not specify a SELECT AND CONSUME statement.