Expression subqueries include:
Subqueries in a select list (introduced with in)
Subqueries in a where or having clause connected by a comparison operator (=, !=, >, >=, <, <=)
This is the general form of expression subqueries:
[Start of select, insert, update, delete statement or subquery]
where expression comparison_operator (subquery)
[End of select, insert, update, delete statement or subquery]
An expression consists of a subquery or any combination of column names, constants, and functions connected by arithmetic or bitwise operators.
The comparison_operator is one of:
Operator |
Meaning |
|---|---|
= |
Equal to |
> |
Greater than |
< |
Less than |
>= |
Greater than or equal to |
<= |
Less than or equal to |
!= |
Not equal to |
<> |
Not equal to |
!> |
Not greater than |
!< |
Not less than |
If you use a column name in the where or having clause of the outer statement, make sure a column name in the subquery_select_list is join-compatible with it.
A subquery that is introduced with an unmodified comparison operator (that is, a comparison operator that is not followed by any or all) must resolve to a single value. If such a subquery returns more than one value, Adaptive Server returns an error message.
For example, suppose that each publisher is located in only one city. To find the names of authors who live in the city where Algodata Infosystems is located, write a statement with a subquery that is introduced with the comparison operator =:
select au_lname, au_fname from authors where city = (select city from publishers where pub_name = "Algodata Infosystems")
au_lname au_fname -------------- -------------- Carson Cheryl Bennet Abraham
