... ( SELECT result
FROM source
[WHERE sql_cond]
[GROUP BY
group] [HAVING group_cond] ) ...
A subquery is a SELECT statement in parenthesis that can be used for a subquery in special logical expressions sql_cond of the WHERE condition. The additions INTO and ORDER BY from the normal SELECT statement cannot be used.
A subquery with a single-column resulting set, in other words, the specification of the columns in the expression result of the subquery is limited to one column or to an aggregate expression, is known as a scalar subquery.
Subqueries can be nested by using subqueries in the WHERE conditions of a subquery. In nested subqueries, the columns specified in the WHERE conditions are searched inside out, though the columns of inner subqueries conceal columns with the same name in outer subqueries.
A subquery that uses columns from the surrounding SELECT statement in its WHERE condition, is known as a correlated subquery. It must be possible to assign the column uniquely to a database table or view in the surrounding SELECT statement. A correlated subquery is evaluated for each individual line of the resulting set in the surrounding SELECT statement.
A maximum of 10 SELECT statements are permitted within one ABAP statement, in other words the SELECT itself and a maximum of 9 other subqueries. This restriction depends on whether the subqueries are nested or occur in different logical expressions of the WHERE condition.
Two special logical expressions sql_cond are available for evaluating subqueries in the WHERE condition.
... [NOT] EXISTS subquery ...
This expression is true if the resulting set of the subquery subquery contains at least one line (not).
... col [NOT] IN subquery ...
This expression is true if the value of the col column is contained in the resulting set of the scalar subquery subquery (not).
Scalar subqueries can also be used as operands in comparisons with relational operators. If the resulting set of the subquery only contains one line, the sql_cond comparison can be specified as usual:
... col operator subquery ...
This expression is true if the relevant comparison of the value of col with the result of the scalar subquery subquery returns "true". Whether the resulting set only contains one line is checked when the statement is executed and does not depend on the addition SINGLE after the SELECT statement in the subquery. If the resulting set contains more than one line, an error occurs that cannot be handled.
If the resulting set of the subquery contains more than one line, the comparison sql_cond must be specified as follows:
... col operator {ALL|ANY|SOME} subquery ...
When using ALL, the expression is true if the comparison is true for all lines in the resulting set of the scalar subquery subquery. With the addition ANY or SOME, the expression is true if it is true for at least one line in the resulting set of the subquery.
The matching operator, (= or EQ) together with ANY or SOME, functions like the previous IN operator.
Exporting the geographical longitude and latitude of a city from the database table SGEOCITY, which is the departure city of a flight in the database table SPFLI.
PARAMETERS: carr_id TYPE spfli-carrid,
conn_id TYPE spfli-connid.
DATA: city TYPE sgeocity-city,
lati TYPE p DECIMALS 2,
longi TYPE p DECIMALS 2.
SELECT SINGLE city latitude longitude
INTO (city, lati, longi)
FROM sgeocity
WHERE city IN ( SELECT cityfrom
FROM spfli
WHERE carrid = carr_id AND
connid = conn_id ).