AS ABAP Release 758, ©Copyright 2024 SAP SE. All rights reserved.

ABAP - Keyword Documentation →  ABAP - Programming Language →  Processing External Data →  ABAP Database Access →  ABAP SQL →  ABAP SQL - Read Access →  SELECT, clauses →  SELECT, FROM → 
Mail Feedback

SELECT, FROM JOIN

Short Reference

Syntax

... [(] { data_source [AS tabalias]}|join
          {[INNER [cardinality]] JOIN}|{LEFT|RIGHT [OUTER [cardinality]] JOIN}|{CROSS JOIN}
             { data_source [AS tabalias]}|join [ON sql_cond] [)] ... .

Additions:

1. ... ON sql_cond

2. ... cardinality

Effect

Joins the columns of two or more data sources in a result set of a query in a join expression. A join expression joins a left side with a right side, using

Every join expression for an inner or outer join must contain a join condition sql_cond after ON (see below). A join expression for a cross join cannot contain any join conditions.

The following applies to possible specifications on the left side and on the right side:

The priority in which nested join expressions are evaluated is specified as follows:



Result set for inner joins

An inner join joins the columns of the rows in the result set of the left side with the columns of the rows in the result set of the right side into a single result set. This result set contains all combinations of rows for whose columns the join condition sql_cond is jointly true. If there are no rows in the result set of the left and right sides that meet sql_cond, no row is created in the resulting result set.

Result set for outer joins

The outer join creates the same result set as the inner join. The difference is that, for each selected row on the left side as LEFT OUTER JOIN or on the right side as RIGHT OUTER JOIN, at least one row is created in the result set, even if no rows on the other side meet the condition sql_cond. The columns on the other side that do not meet the condition sql_cond are filled with null values.

Result set for cross join

The cross join forms a cross product of the result set of the left side and the result set of the right side. The cross join joins the columns of the rows in the result set of the left side with the columns of the rows in the result set of the right side. This result set contains all possible combinations of rows. The number of rows in the result set of the cross join is the product of the number of rows of both joined result sets.

Hints

Example

Join of the columns CARRNAME, CONNID, and FLDATE of the DDIC database tables SCARR, SPFLI, and SFLIGHT using two inner joins. This creates a list of flights from CITYFROM to CITYTO. Alternative names are used for each table.

DATA:
  cityfr TYPE spfli-cityfrom VALUE 'FRANKFURT',
  cityto TYPE spfli-cityto   VALUE 'NEW YORK'.

cl_demo_input=>new(
  )->add_field( CHANGING field = cityfr
  )->add_field( CHANGING field = cityto )->request( ).

SELECT c~carrname, p~connid, f~fldate
       FROM ( ( scarr AS c
         INNER JOIN spfli AS p ON p~carrid   = c~carrid
                              AND p~cityfrom = @cityfr
                              AND p~cityto   = @cityto )
         INNER JOIN sflight AS f ON f~carrid = p~carrid
                                AND f~connid = p~connid )
       ORDER BY c~carrname, p~connid, f~fldate
       INTO TABLE @FINAL(itab).

Example

Join of the database tables SCARR and SPFLI using a left outer join. For all flights not departing from CITYFROM, the value of the column CONNID is the null value. Due to the WHERE condition, all airlines that do not fly from CITYFROM are output.

DATA cityfr TYPE spfli-cityfrom VALUE 'FRANKFURT'.
cl_demo_input=>request( CHANGING field = cityfr ).

SELECT s~carrid, s~carrname
       FROM scarr AS s
       LEFT OUTER JOIN spfli AS p ON s~carrid   =  p~carrid
                                  AND p~cityfrom = @cityfr
       WHERE p~connid IS NULL
       ORDER BY s~carrid, s~carrname
       INTO TABLE @FINAL(itab).

cl_demo_output=>display( itab ).

Example

Cross join of the DDIC database table T000 of all clients of an AS ABAP with the entries for the message class SABAPDEMOS in the table T100. Without the WHERE condition, the result set would be very large.

DATA BEGIN OF wa.
DATA mandt TYPE t000-mandt.
DATA mtext TYPE t000-mtext.
INCLUDE TYPE t100.
DATA END OF wa.
DATA itab LIKE STANDARD TABLE OF wa WITH EMPTY KEY.

SELECT t000~mandt, t000~mtext, t100~*
       FROM t000 CROSS JOIN t100
       WHERE t100~arbgb = 'SABAPDEMOS'
       ORDER BY t000~mandt, t100~sprsl, t100~msgnr
       INTO TABLE @itab.

cl_demo_output=>display( itab ).

Addition 1  

... ON sql_cond

Effect

Join condition. A join condition must be specified for an inner or outer join. A join condition must not be specified for a cross join.

The syntax of the relational expressions of a join condition sql_cond is subject to the following restrictions:

A join condition is met if the logical expression sql_cond is true.

Implicit ABAP SQL client handling applies. In joins between client-dependent tables, a comparison for equality between the client columns is added to the ON condition implicitly. The client column of a client-dependent data source cannot be used as an operand in the ON condition.

Hints



Executable Examples



Addition 2  

cardinality

Effect

Specifies the cardinality cardinality of an inner, left outer, or right outer join. It has an effect only on certain specific database systems.

The following cardinality specifications are possible:

The cardinality specification is used by the SQL Optimizer for performance optimizations by suppressing surplus joins on any databases that support this addition. It is important that the cardinality specification matches the data in question. Otherwise, the result is undefined and can depend on the entries in the SELECT list.

If no cardinality is specified, the implicit default cardinality is many-to-many.

Hints

Example

Incorrect use of the cardinality MANY TO ONE. The data in the DDIC database tables SCARR and SPFLI do not have the cardinality MANY TO ONE, but the reverse cardinality. On an SAP HANA database, for example, the result depends on the SELECT list. If the left and right side are specified here, no optimization takes place. If no columns are specified on the right side and the aggregate function COUNT(*) is used as an aggregate expression, an optimization takes place. Here, only that data is read that meets the prerequisite cardinality.

FINAL(out) = cl_demo_output=>new( ).

out->next_section( `Fields of left and right table` ).
SELECT FROM scarr AS c
              LEFT OUTER MANY TO ONE JOIN spfli AS p
                ON c~carrid = p~carrid
       FIELDS c~carrid   AS carrid,
              c~carrname AS carrname,
              p~connid   AS connid
       ORDER BY c~carrid
       INTO TABLE @DATA(itab).
out->write( itab ).
out->write( sy-dbcnt ).

out->next_section( `Fields of left table only` ).
SELECT FROM scarr AS c
              LEFT OUTER MANY TO ONE JOIN spfli AS p
                ON c~carrid = p~carrid
       FIELDS c~carrid   AS carrid,
              c~carrname AS carrname
       ORDER BY c~carrid
       INTO CORRESPONDING FIELDS OF TABLE @itab.
out->write( itab ).
out->write( sy-dbcnt ).

out->next_section( `COUNT(*)` ).
SELECT FROM scarr AS c
              LEFT OUTER MANY TO ONE JOIN spfli AS p
                ON c~carrid = p~carrid
       FIELDS COUNT( * ) AS count
       INTO @FINAL(count).
out->write( count ).

out->display( ).



Continue
Example SELECT, Inner, Outer, and Cross Joins
Example SELECT, Multiple Joins