SELECT Statements in SAP ABAP

There are different types of Select Statements in ABAP. Below are some select statements for frequently used cases.
<FLD_LIST> -> List of Fields from Data Base table for selection
<DB_TABLE> -> Source Data Base Table
<WA> -> Target Work Area/Structure
<ITAB> -> Target Internal Table
<CONDITION> -> Where condition in the select statement based on DB Table Fields.

1. 
   
   SELECT *|<FLD_LIST>                             (We can select all fields or the fields in given order of the internal table)
   FROM <DB_TABLE>
       INTO <WA>
           WHERE <CONDITION>.

        ENDSELECT.
    Ex: SELECT * FROM KNA1
        INTO W_KNA1

WHERE KUNNR GT '0000100000'. (Where W_KNA1 is a STRUCTURE similar to KNA1)

        WRITE: / W_KNA1-KUNNR, W_KNA1-NAME1.
ENDSELECT.

PS: This is a loop on data base table. This Select-EndSelect loop will retrieve records one after the other from data base and places it in the structure/Work Area. Programmer can use the structure data for their programming purpose within the Select – EndSelect Statements.

     2.  SELECT *|<FLD_LIST>

    FROM <DB_TABLE>

        INTO TABLE <ITAB>

            WHERE <CONDITION>.

        Ex: SELECT * FROM KNA1

             INTO TABLE IT_KNA1

       WHERE KUNNR GT '0000100000'. (Where IT_KNA1 is an internal table with structure similar to KNA1)

              LOOP AT IT_KNA1.
                  WRITE: / IT_KNA1-KUNNR, IT_KNA1-NAME1.
              ENDLOOP.
            

PS: When INTO TABLE is used, it will no more be a Loop. ENDSELECT is not needed here. This is a single statement to get all the required records from the Data Base table into our internal table in a single step, which will be very faster as compared with earlier process as well as reduces number of hits to the DB and thus optimizing our programs performance.

Also, here we will have all the records for processing at a later stage in our internal table.


     3.  SELECT SINGLE *|<FLD_LIST>    (We can select all fields or the fields 
                                                                                in given order of the internal table)

     FROM <DB_TABLE>

          INTO <WA>

              WHERE <CONDITION>.

          Ex: SELECT SINGLE * FROM KNA1
                     INTO W_KNA1

          WHERE KUNNR GT '0000100000'. (Where W_KNA1 is a 
                                                                   STRUCTURE similar to KNA1)

PS: In this case also we don't need ENDSELECT. This is a statement which will bring the first selected record from the Data Base Table and place it in our structure/WA. This will be useful if we need only one record from the DB table thus not looping over the DB table number of times.

NOTE: One important thing to remember for all the above cases (when using the <FLD_LIST> in the Select statement) is that, the order of the fields in the <FLD_LIST> should be same as the order of the fields defined in the Work Area/Internal Table.

System will try to place the selected fields of each record in the order (i.e., first field into first field etc.). So, if the fields defined in the internal table and fields selected in Select statement are not in same order, there will be errors in the data selected (sometimes running into Run-Time errors when there is a data type conflict). The solution to this scenario is below.

          4. SELECT <FLD_LIST>

         FROM <DB_TABLE>

              INTO CORRESPONDING FIELDS OF < ITAB>

                  WHERE <CONDITION>.

By Using the INTO CORRESPONDING FIELDS OF addition, the select statement will try to identify the corresponding field of the Internal Table which has the same name as the Selected field from the Data Base table and updates it in that field. Thus even if the order of selection for the fields in the Select statement and in the Internal table do not match, the data is populated into the corresponding fields.
    The same functionality applies to all the three scenarios explained above.
    NOTE: One Limitation here however is that, the Names of the Fields in the Internal Table should be the same as the names of the Data Base Table. Or else, system cannot find any such field and so the fields will not be updated.
Below is a detailed description of the Generic Select Statement Syntax and its different parts.
Syntax:
SELECT <result>
INTO <target>
FROM <source>
[WHERE <condition>]
[GROUP BY <fields>]
[HAVING <cond>]

[ORDER BY <fields>].

The SELECT statement consists of a series of clauses, each of which fulfils a certain task:
SELECT clause: Defines the structure of the selection.

Syntax

SELECT [SINGLE]|[DISTINCT]

* | <si>...

<agg>( [DISTINCT] <sj>) [AS <aj>]...

The selection can be a single line SINGLE or a series of lines. You can eliminate duplicate lines using the DISTINCT addition. To select the entire line, use *, otherwise, you can specify individual columns <si>. For individual columns, you can use aggregate functions <agg>, and assign alternative column names <ai>.

 INTO clause: Defines the target area into which the selection from the SELECT clause is to be placed.

Syntax

... INTO [CORRESPONDING FIELDS OF] <wa>

| INTO|APPENDING [CORRESPONDING FIELDS OF] TABLE <itab>

[PACKAGE SIZE <n>]

| INTO (<f1>, <f2>,...)

The target area can be a flat work area <wa>, an internal table <itab>, or a list of fields <fi>. If you use the CORRESPONDING FIELDS addition, data is only selected if there is an identicallynamed field in the target area. If you use APPENDING instead of INTO, the data is appended to an internal table instead of overwriting the existing contents. PACKAGE SIZE allows you to overwrite or extend the internal table in a series of packages. The data type of the target area must be appropriate for the selection in the SELECT clause.

 FROM clause: Specifies the database tables from which the data in the selection in the SELECT clause is to be read.

Syntax

... FROM [<tab> [INNER]|LEFT [OUTER] JOIN] <dbtab> [AS <alias>]

[ON <cond>]

[CLIENT SPECIFIED]

[BYPASSING BUFFER]

[UP TO <n> ROWS]

You can read a single table <dbtab> or more than one table, using inner and outer joins to link tables with conditions <cond>, where <tab> is a single table or itself a join condition. You can specify individual database tables either statically or dynamically, and you can replace their names with an alternative <alias>. You can bypass automatic client handling with the CLIENT SPECIFIED addition, and SAP buffering with BYPASSING BUFFER. You can also restrict the number of lines read from the table using the UP TO <n> ROWS addition.

WHERE clause: Restricts the number of lines selected.

Syntax

... [FOR ALL ENTRIES IN <itab>] WHERE <cond>

The condition <cond> may contain one or more comparisons, tests for belonging to intervals, value list checks, subqueries, selection table queries or null value checks, all linked with AND, OR, and NOT. If you use the FOR ALL ENTRIES addition, the condition <cond> is checked for each line of the internal table <itab> as long as <cond> contains a field of the internal table as an operand. For each line of the internal table, the lines from the database table meeting the condition are selected. The result set is the union of the individual selections resulting from each line.

 GROUP BY clause: Groups lines in the selection

Syntax

... GROUP BY <s1> <s2>

Groups lines with the same contents in the specified columns. Uses aggregate functions for all other columns in each group. All columns listed in the SELECT clause that do not appear in the GROUP BY addition must be specified in aggregate expressions.

 HAVING clause: Restricts the number of line groups.

Syntax

... HAVING <cond>

Like the WHERE clause, but can only be used in conjunction with a GROUP BY clause. Applies conditions to aggregate expressions to reduce the number of groups selected.

 ORDER BY clause: Sorts the lines in the selection.

Syntax

... ORDER BY PRIMARY KEY |... <si> [ASCENDING|DESCENDING]...

Sorts the selection in ascending or descending order according to the primary key or the contents of the fields listed.