Skip to content

ABAP Keyword Documentation →  ABAP − Reference →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Reads →  SELECT 

SELECT - mainquery_clauses

Other versions: 7.31 | 7.40 | 7.54

Syntax


... [SINGLE [FOR UPDATE]] 

    { FROM source 

      FIELDS select_clause } 

  | { select_clause 

      FROM source } 

    [[FOR ALL ENTRIES IN itab] 

      WHERE sql_cond] 

    [GROUP BY group] [
HAVING group_cond] 
  
 [ORDER BY sort_key] 

    [db_hints]  ...

Effect

Possible clauses and additions of a main query represented by a standalone SELECT statement. The clauses and additions define the results set of the SELECT statement:

  • Rows of the results set
  • Multirow Results Set

    If SINGLE is not specified, and if aggregate expressions are not exclusively specified in the SELECT list of the SELECT clause select_clause, the results set is multirow or tabular by default. All database rows that are selected by the remaining additions of the SELECT statement are included in the results set. If the addition ORDER BY is not used, the order of the rows in the results set is not defined and, if the same statement SELECT is executed multiple times, the order may be different each time. A data object specified after INTO can be an internal table and the addition APPENDING can be used. If no internal table is specified after INTO or APPENDING, the statement SELECT in multirow results sets triggers a loop that must be closed using ENDSELECT.
  • Single Row Results Set

    The optional addition SINGLE defines a single-row results set that can be protected against parallel changes by another program. When using SINGLE, it is not possible to specify an internal table as target object, and the addition SINGLE cannot be combined with all additions of the SELECT statement.
  • Columns of the Results Set
The SELECT clause select_clause defines the structure of the results set. It consists mainly of a SELECT list that defines the columns of the results set. Duplicate rows can be excluded (this is optional).
  • Data Sources
The FROM clause specifies which data sources source are read. These can either be data sources in the database accessed by the current query or they can be internal tables. Data sources in the database must be defined in ABAP Dictionary.
  • Conditions
The results set can be restricted using the WHERE clause. The optional addition FOR ALL ENTRIES can be used to compare the content of a column in the database with a component with all rows of a structured internal table itab.
  • Grouping
The GROUP BY clause merges multiple database rows into one row of the results set. The HAVING clause restricts the merged rows.
  • Database Notes
db_hints can be used to specify database hints.
  • Sorting
The ORDER BY clause sorts the results set. If ORDER BY is not specified, the order of the rows in the results set is undefined.

The SELECT clause select_clause can be specified before or after the FROM clause. After the FROM clause, the SELECT clause must be prefixed with the addition FIELDS.


Notes

  • Although the WHERE condition is optional, for performance reasons, it should always be specified, and the results set should not be restricted in AS ABAP.
  • The arrangement of the FROM clause before a SELECT clause with FIELDS supports tools such as code completion in the ABAP Editor.

  • If a query is used to access a CDS entity associated with a CDS role and for which CDS access control is not disabled using the value #NOT_ALLOWED for the annotation @AccessControl.authorizationCheck or using the addition WITH PRIVILEGED ACCESS in the FROM clause, only that data is read implicitly that matches the access condition defined in the CDS role. If data cannot be read, ABAP programs cannot distinguish whether this is due to the conditions of the SELECT statement, the conditions of the CDS entity, or an associated CDS role. If the CDS database view database view of a CDS view is accessed, no access control takes place.


Example

SELECT statement with all possible clauses.

SELECT FROM sflight 
       FIELDS carrid, 
              connid, 
              SUM( seatsocc ) AS seatsocc 
       WHERE carrid = 'LH' 
       GROUP BY carrid, connid 
       HAVING SUM( seatsocc ) > 1000 
       ORDER BY carrid, connid 
       INTO TABLE @DATA(result).