ABAP Keyword Documentation →  ABAP − Short Reference 

SELECT - Short Reference

Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax

SELECT { { SINGLE [FOR UPDATE] } 
       | { [DISTINCT] { }      } } 

       { * 
       | { ..., data_source~*, ..., 

           col|
aggregate( [DISTINCT] col )|sql_exp} [AS alias], ... } 

       | (column_syntax) 
       FROM { { {data_source [AS tabalias]} 

              | { [(] {data_source [AS tabalias]}|join 

                    {[INNER] JOIN}|{LEFT|RIGHT [OUTER] JOIN} 

                       {data_source [AS tabalias]}|join ON join_cond [)] } 

              | (source_syntax) [AS tabalias] } 

              [ {USING CLIENT clnt} 

              | {CLIENT SPECIFIED [entity1~clnt] [, entity2~clnt] ...} ] } 

       [[FOR ALL ENTRIES IN itab] WHERE sql_cond] 

       [GROUP BY {col1|
sql_exp1, col2|sql_exp2, ...}|(column_syntax)] 

       [HAVING sql_cond] 

       [ORDER BY { {PRIMARY KEY} 
      
          |{{{col1|a1} [ASCENDING|DESCENDING],} 
 
                 {{col2|a2} [ASCENDING|DESCENDING],} 

                   ... } 

                 | (column_syntax) }]. 

       { { INTO {[CORRESPONDING FIELDS OF] wa}|(dobj1, dobj2, ...) } 
       | { INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE itab 

                          [PACKAGE SIZE n] } 

       | { INTO @DATA(wa) } 
       | { INTO TABLE @DATA(itab) [PACKAGE SIZE n] } } 

       [ CREATING {  READER|LOCATOR FOR { COLUMNS col1 col2 ... } 

                 
               | { ALL [OTHER] [BLOB|CLOB] COLUMNS } 

                    [READER|LOCATOR FOR ...] } 

                | {  (crea_syntax) } ] 

       [UP TO n ROWS] 
       [BYPASSING BUFFER] 

       [CONNECTION con|(con_syntax)] 
  ... 
[ENDSELECT.] 

Effect

This statement reads data from one or more database tables into data objects. If the results set is not assigned in one step, a loop is opened which is closed using ENDSELECT. This loop provides the results in the target fields.

Additions

Specifies the rows

• SINGLE|{ } - Defines a single row or multiple row results set.
• FOR UPDATE - Sets an exclusive lock in the database.
• DISTINCT - Excludes duplicate rows from the results set.

Specifies the columns as a SELECT list

• * - Reads all columns of all data sources.
• data_source~* - Reads all columns of the specified data source.
• col [AS alias] - Reads individual columns col and assigns an alternative column name alias (optional).
• aggregate( [DISTINCT] col1 ) ... aggregate( [DISTINCT] col2 ) ... - Evaluates aggregate functions aggregate for columns col1, col2 .... Duplicate arguments can be excluded.
• sql_exp - SQL expression evaluated on the database.
• AS a1 ... AS a2 ... - Defines alternative column names a1, a2, ...
• (column_syntax) - Specifies the columns as the content of column_syntax.

Specifies the database table(s)

• FROM data_source [AS tabalias]
  Specifies a data source data_source. Database tables, classic views, and CDS views in ABAP Dictionary are all possible.
• FROM [(] {data_source [AS tabalias]}|join
  {[INNER] JOIN}|{LEFT|RIGHT [OUTER] JOIN}
  {data_source [AS tabalias]}|join ON join_cond [)]
  
  Joins multiple data sources in a join expression. Inner and outer joins can be created using join conditions join_cond.
• (source_syntax)
  Specifies the database tables as the content of source_syntax.
• AS tabalias
  Defines an alternative table name tabalias.
• USING CLIENT clnt
  Switches automatic client handling to the client specified in clnt.
• CLIENT SPECIFIED
  Deactivates automatic client handling.

Specifies a condition

• FOR ALL ENTRIES IN itab
  Enables a condition to be used after WHERE on all rows in an internal table itab.
• WHERE sql_cond
  Restricts the results set using the condition sql_cond. The condition can consist of different relational expressions.

Specifies a grouping

• GROUP BY { {col1|sql_exp1, col2|sql_exp2, ...} | (column_syntax) }
  Combines groups of rows that have the same content in the columns col1, col2, ... or in SQL expressions sql_exp1, sql_exp1, ... into a single row of the results set. Here, the columns can be specified dynamically in column_syntax.
• HAVING sql_cond
  Restricts rows combined in groups in the results set using a condition sql_cond.

Specifies a sort

• ORDER BY
  {PRIMARY KEY}|{{{col1|a1} [ASCENDING|DESCENDING],}
  {{col2|a2} [ASCENDING|DESCENDING],} ...}|(column_syntax)
  
  Sorts the results set by primary key, statically specified columns col1, col2, ... or a1, a2, ... or dynamically specified columns in column_syntax. The sort direction can be specified for individual columns.

Specifies the target area

• INTO wa|(dobj1, dobj2 ...)
  Specifies a work area wa or a list of data objects dobj1 dobj2 ... as the target fields.
• INTO|APPENDING TABLE itab [PACKAGE SIZE n]
  Specifies an internal table itab as the target area. The results set is read into or attached to the table as a whole or in packages.
• CORRESPONDING FIELDS OF
  Transports only the columns that have target fields with the same name.
• @DATA(wa), @DATA(itab)
  Inline declaration of the target area.
• CREATING READER|LOCATOR FOR ... COLUMNS ...
  Specifies what kind of LOB handle is created.

Specifies additions

• UP TO n ROWS
  Restricts the number of rows read to n.
• BYPASSING BUFFER
  Bypasses SAP buffering.
• CONNECTION con|(con_syntax)
  Executes the statement on a secondary database connection.