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 (=, !=, > , > =, <, <=)

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, SAP ASE 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