ABAP Keyword Documentation →  ABAP - Quick Reference 

SELECT - Quick reference

Reference

Other versions: 7.31 | 7.40 | 7.54

Syntax

SELECT [SINGLE [FOR UPDATE]] 

       { [DISTINCT] { * 

                 
  | { ..., data_source~, ...,
sql_exp} [AS alias], ... } 

                    | (column_syntax) 

         FROM { { {data_source [WITH PRIVILEGED ACCESS] [AS tabalias]} 

                | { [(] {data_source [WITH PRIVILEGED ACCESS] [AS tabalias]}|join 

                 
    {[INNER] JOIN}|{LEFT|RIGHT [OUTER [MANY TO ONE]] JOIN}|{CROSS JOIN} 

                 
       {data_source [WITH PRIVILEGED ACCESS] [AS tabalias]}|join [ON join_cond]
 [)] } 
      
         | (source_syntax) [WITH PRIVILEGED ACCESS] [AS tabalias] } 
   
            [ {USING CLIENT clnt} 

                | {USING CLIENTS IN @client_range_tab} 

                | {USING CLIENTS IN T000} 

                | {USING ALL CLIENTS} 

                | {CLIENT SPECIFIED [entity1~clnt] [, entity2~clnt] ...} ] } } 
 
   | { FROM { { {data_source [WITH PRIVILEGED ACCESS] [AS tabalias]} 

                | { [(] {data_source [WITH PRIVILEGED ACCESS] [AS tabalias]}|join 

                 
    {[INNER] JOIN}|{LEFT|RIGHT [OUTER [MANY TO ONE]] JOIN}|{CROSS JOIN} 

                 
       {data_source [WITH PRIVILEGED ACCESS] [AS tabalias]}|join [ON join_cond]
 [)] } 
      
         | (source_syntax) [AS tabalias] } 

                [ {USING CLIENT clnt} 

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

         FIELDS [DISTINCT] 

                { * 

                | { ..., data_source~, ...,
sql_exp} [AS alias], ... } 

                | (column_syntax) } 

       [[FOR ALL ENTRIES IN itab] WHERE sql_cond] 

       [GROUP BY {
sql_exp1|
grouping_sets1,
sql_exp2|
grouping_sets2, ...}|(column_syntax)] 

       [HAVING sql_cond] 

       [UNION ALL|DISTINCT SELECT ...] 

       [ORDER BY { {PRIMARY KEY} 

                 |{{{col1|alias1} [ASCENDING|DESCENDING],} 

                   {{col2|alias2} [ASCENDING|DESCENDING],} 

                   ... } 

                 | (column_syntax) }]. 

       { { INTO ([NEW] @dobj1|@DATA(dobj1), [NEW] @dobj2|@DATA(dobj2), ...) } 

       | { INTO [CORRESPONDING FIELDS OF] [NEW] @wa [INDICATORS {[NOT] NULL STRUCTURE null_ind} 

                 
                 
                    | (indicator_syntax)] } 

       | { INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE [NEW] @itab 

                 
        [INDICATORS {[NOT] NULL STRUCTURE null_ind} 

                 
                  | (indicator_syntax)] 

                 
        [PACKAGE SIZE n] } 

       | { INTO [NEW] @DATA(wa) [INDICATORS {[NOT] NULL STRUCTURE null_ind} 

                 
                  | (indicator_syntax)]} 

       | { INTO TABLE [NEW] @DATA(itab) [INDICATORS {[NOT] NULL STRUCTURE null_ind} 

                 
                 
        | (indicator_syntax)] 

                 
                [PACKAGE SIZE n] } } 

       [ EXTENDED RESULT @oref ] 
 
     [ CREATING {  READER|LOCATOR FOR { COLUMNS col1 col2 ... } 

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

                 
  [READER|LOCATOR FOR ...] } 

                | {  (crea_syntax) } ] 

       [UP TO n ROWS] 
       [OFFSET o] 

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

Effect

ABAP SQL statement. This statement reads data from one or more data sources 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

Specifying the Rows

• SINGLE - Defines a single row results set. The results set is multirow by default.
• FOR UPDATE - Sets an exclusive lock on a single row in the database.

Structure of the Results Set

• FIELDS - Required if the FROM clause was specified first.
• DISTINCT - Removes duplicate rows from the results set.
• * - Reads all columns of all data sources.
• data_source~* - Reads all columns of the specified data source.
• sql_exp [AS alias] - Reads the result of an SQL expression and assigns an alias name alias (optional).
• (column_syntax) - Specifies the columns as the content of column_syntax.

Specifying the Database Table(s)

• FROM data_source [AS tabalias]
  Specifies a data source data_source. Possible data sources are database tables, common table expressions, table functions, classic views, and non-abstract CDS entities of ABAP Dictionary. Parameters can be passed to CDS entities. Path specifications are possible after common table expressions and CDS views. An internal table can also be specified as a data source.
• FROM [(] {data_source [AS tabalias]}|join
  {[INNER] JOIN}|{LEFT|RIGHT [OUTER [MANY TO ONE]] JOIN}|{CROSS JOIN}
  {data_source [AS tabalias ]}|join [ON join_cond] [)]
  
  Joins multiple data sources in a join expression. Inner and outer joins can be created with join conditions join_cond and cross joins without conditions.
• WITH PRIVILEGED ACCESS
  Switches CDS access control off.
• (source_syntax)
  Specifies the data sources as the content of source_syntax.
• AS tabalias
  Defines an alias name tabalias for the data source.
• USING CLIENT clnt
  Switches implicit client handling to the client specified in clnt.
• USING [ALL] CLIENTS [IN]
  Switches implicit client handling to multiple clients.
• CLIENT SPECIFIED
  Obsolete: Disables implicit client handling.

Specifying 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.

Specifying a Grouping

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

Specifying a Union

• UNION ALL|DISTINCT SELECT ...
  
  Creates a union of the rows of the results sets of two SELECT statements.

Specifying a Sort

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

Specifying the Target Area

• INTO [NEW] @wa|([NEW] @dobj1, [NEW] @dobj2 ...)
  Specifies a work area wa or a list of data objects dobj1 dobj2 ... as the target fields. With the addition NEW, reference variables are used and anonymous data objects are created.
• INTO|APPENDING TABLE itab [PACKAGE SIZE n]
  Specifies an internal table itab as the target area. The result set is read into or attached to the table as a whole or in packages. With the addition NEW, a reference variable is used and an anonymous data object is created.
• CORRESPONDING FIELDS OF
  Transports only the columns that have target fields with the same name.
• [NEW] @DATA(wa), [NEW] @DATA(itab)
  Inline declaration of the target area or of reference variables.
• INDICATORS ...
  Specifies indicators such as the null indicator.
• EXTENDED RESULT @oref
  Provides an extended result in a result object.
• CREATING READER|LOCATOR FOR ... COLUMNS ...
  Specifies what type of LOB handle is created.

Specifying Additions

• UP TO n ROWS
  Restricts the number of rows read to n.
• OFFSET o
  Reads the rows from the offset o.
• BYPASSING BUFFER
  Bypasses table buffering.
• CONNECTION con|(con_syntax)
  Executes the statement on a secondary connection.