Subquery

Variants:

1. ( select )

2. ALL ( select )

3. ANY ( select )

4. SOME ( select )

Effect

A subquery is a SELECT statement select that occurs within a

• SELECT,
  
• OPEN CURSOR,
  
• UPDATE, or
  
• DELETE statement
  

in the WHERE clause or HAVING clause, to check whether data from database tables or views meets certain criteria.

Subqueries have a restricted syntax in comparison to the normal SELECT statement:

SELECT result FROM source [WHERE where] [GROUP BY fields]       [HAVING having].

Variant 1

( select )

If the subquery returns a single value (scalar subquery), you
can use any relational operator except LIKE and BETWEEN.

Example

Selecting the flights with the most passengers:

DATA: WA TYPE SFLIGHT.

SELECT * FROM SFLIGHT
    INTO WA
    WHERE SEATSOCC = ( SELECT MAX( SEATSOCC ) FROM SFLIGHT ).
  WRITE: / WA-CARRID, WA-CONNID, WA-FLDATE.
ENDSELECT.

Variant 2

ALL ( select )

If the subquery returns several lines, each containing one value, you
specify that the comparison should apply for all of the values it returns.

Example

This example shows how to use ALL. It displays a list of the customer IDs of the customer (or customers) who have made the most bookings:

DATA: ID TYPE SBOOK-CUSTOMID, CNT TYPE I.

SELECT CUSTOMID COUNT( * ) AS C FROM SBOOK
    INTO (ID, CNT)
    GROUP BY CUSTOMID
    HAVING COUNT( * ) >=
      ALL ( SELECT COUNT( * ) FROM SBOOK GROUP BY CUSTOMID ).
  WRITE: / ID, CNT.
ENDSELECT.

Variant 3

ANY ( select )

Variant 4

SOME ( select )



If the subquery returns several lines each containing one value, this variant specifies that the comparison should apply to at least one of the values returned. The IN operator is the same as the combination = ANY.

Notes

1. If you use a subquery with the EXISTS operator, the expression is true if the subquery selects at least one line. You can use * in the SELECT clause of subqueries that use EXISTS.
   
   

If you use a subquery with a relational operator instead of EXISTS, you may only specify one column in the SELECT clause. This can be either a field from the database table or an aggregate expression.

1. Subqueries are not supported for pool and cluster tables. This means that you can use subqueries only in statements in which only transparent tables and views occur.
   
   

Example

Selecting all flights from Frankfurt to New York between 1.1.1999 and 31.3.1999 that are not yet full:

DATA: WA_SFLIGHT TYPE SFLIGHT.

SELECT * FROM SFLIGHT AS F INTO WA_SFLIGHT
    WHERE SEATSOCC < F~SEATSMAX
      AND EXISTS ( SELECT * FROM SPFLI
                     WHERE CARRID = F~CARRID
                       AND CONNID = F~CONNID
                       AND CITYFROM = 'FRANKFURT'
                       AND CITYTO = 'NEW YORK' )
      AND FLDATE BETWEEN '19990101' AND '19990331'.
  WRITE: / WA_SFLIGHT-CARRID, WA_SFLIGHT-CONNID,
           WA_SFLIGHT-FLDATE.
ENDSELECT.

Subqueries such as the one in this example, in which the WHERE clause uses fields from the main query, are known as correlated subqueries. Subqueries can be nested, and a given subquery may contain any fields from other, hierarchically-superior subqueries.

In a correlated subquery, the subquery is executed for each line r returned by the main query. In the above example, the main query finds all flights in table SFLIGHT that are not full and that have a date that meets the selection criterion. The statement then performs the subquery for each of the records returned by the main query, using the corresponding values of CARRID and CONNID, to check whether the relevant flight operates between Frankfurt and New York.

Additional help

Subqueries