Specifying Database Tables 

The FROM clause determines the database tables from which the data specified in the SELECT clause is read. You can specify either a single table or more than one table, linked using inner or outer joins. The names of database tables may be specified statically or dynamically, and you can use alias names. You can also use the FROM clause to bypass the SAP buffer and restrict the number of lines to be read from the database.

“Database table” can equally mean an ABAP Dictionary view. A view links two or more database tables in the ABAP Dictionary, providing a static join that is available across the system. You can specify the name of a view wherever the name of a database table may occur in the FROM clause.

The FROM clause has two parts, one for specifying database tables, and one for other additions that control database access:

SELECT ... FROM tables options ...

In tables, you specify the names of database tables and define joins.
In options, you can specify the following:

·        CLIENT SPECIFIED to disable automatic client handling

·        BYPASSING BUFFER to disable data buffering

·        UP TO n ROWS to restrict the absolute number of rows in the selection to n

·        For more details about these additions, refer to the keyword documentation.

Specifying Database Tables Statically

To specify the name of a database table statically, use the following for tables:

SELECT ... FROM dbtab [AS tabalias] options ...

The database table dbtab must exist in the ABAP Dictionary. The ASaddition allows you to specify an alternative name tabalias that you can then use in the SELECT; FROM, WHERE, and GROUP BYclauses.

Specifying Database Tables Dynamically

To specify the name of a database table dynamically, use the following for tables:

SELECT ... FROM (name) options ...

The field name must contain the name of a database table in the ABAP Dictionary. The table name must be written in uppercase. When you specify the name of a database table dynamically, you cannot use an empty INTO clause to read all of the columns into the work area dbtab. It is also not possible to use alternative table names.

Specifying Two or More Database Tables as an Inner Join

You can read from more than one table in a single SELECTstatement, such that the data in the tables all has to meet the same conditions, using the following join expression:

SELECT ...
...
  FROM tab [INNER] JOIN dbtab [AS alias] ON cond options
...

where dbtab is a single database table and tab is either a table or another join expression. The database tables can be specified statically or dynamically. You may also use aliases. A join expression links each line of tab with the lines in dbtab that meet the condition cond. This means that there is always one or more lines from the right-hand table that is linked to each line from the left-hand table by the join. If dbtab does not contain any lines that meet the condition cond, the line from tab is not included in the selection.

Specifying Two or More Database Tables as a Left Outer Join

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition cond. The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT...
...
  FROM tab LEFT [OUTER] JOIN dbtab [AS alias] ON cond
       options
...

Examples

Specifying a database table statically:

REPORT demo_select_static_database.

DATA wa TYPE scarr.

SELECT *
  INTO wa
  FROM scarr UP TO 4 ROWS.

  WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

The list output is:

The system reads four lines from the database table SCARR.

Specifying a database table dynamically:

REPORT demo_select_dynamic_database.

DATA wa TYPE scarr.

DATA name(10) TYPE c VALUE 'SCARR'.

SELECT  *
  INTO  wa
  FROM  (name) CLIENT SPECIFIED
  WHERE mandt = '000'.

  WRITE: / wa-carrid, wa-carrname.

ENDSELECT.

A condition for the MANDT field is allowed, since the example uses the CLIENT SPECIFIED option. If name had contained the value ‘scarr’ instead of ‘SCARR’, a runtime error would have occurred.

Inner join:

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,
        carrid TYPE spfli-carrid,
        connid TYPE spfli-connid,
        fldate TYPE sflight-fldate,
        bookid TYPE sbook-bookid,
      END OF wa,
      itab LIKE SORTED TABLE OF wa
                WITH UNIQUE KEY carrid connid fldate bookid.

SELECT  p~carrid p~connid f~fldate b~bookid
  INTO  CORRESPONDING FIELDS OF TABLE itab
  FROM  ( ( spfli AS p
            INNER JOIN sflight AS f ON p~carrid = f~carrid AND
                                       p~connid = f~connid    )
            INNER JOIN sbook   AS b ON b~carrid = f~carrid AND
                                       b~connid = f~connid AND
                                       b~fldate = f~fldate     )
  WHERE p~cityfrom = 'FRANKFURT' AND
        p~cityto   = 'NEW YORK'  AND
        f~seatsmax > f~seatsocc.

LOOP AT itab INTO wa.
  AT NEW fldate.
    WRITE: / wa-carrid, wa-connid, wa-fldate.
  ENDAT.
  WRITE / wa-bookid.
ENDLOOP.

This example links the columns CARRID, CONNID, FLDATE, and BOOKID of the table SPFLI, SFLIGHT, and SBOOK, and creates a list of booking numbers for all flights from Frankfurt to New York that are not fully booked. An alias name is assigned to each table.

Left outer join:

REPORT demo_select_left_outer_join.

DATA: BEGIN OF wa,
        carrid   TYPE scarr-carrid,
        carrname TYPE scarr-carrname,
        connid   TYPE spfli-connid,
      END OF wa,
      itab LIKE SORTED TABLE OF wa
                WITH NON-UNIQUE KEY carrid.

SELECT s~carrid s~carrname p~connid
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM scarr AS s
       LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid AND
                                     p~cityfrom = 'FRANKFURT'.

LOOP AT itab INTO wa.
  WRITE: / wa-carrid, wa-carrname, wa-connid.
ENDLOOP.

The output might look like this:

The example links the columns CARRID, CARRNAME, and CONNID of the tables SCARR and SPFLI using the condition in the left outer join that the airline must fly from Frankfurt. All other airlines have a null value in the CONNID column in the selection.

If the left outer join is replaced with an inner join, the list looks like this:

Only lines that fulfill the ON condition are included in the selection.