SAP NetWeaver AS ABAP Release 750, ©Copyright 2016 SAP AG. All rights reserved.
ABAP - Keyword Documentation → ABAP - Reference → Processing External Data → ABAP Database Accesses → Open SQL → Open SQL - Read Accesses → SELECT → SELECT - FROM →
SELECT - JOIN
Syntax
... [(] {
data_source [AS tabalias]}|join
{[INNER] JOIN}|{LEFT|RIGHT [OUTER] JOIN}
{
data_source [AS tabalias]}|join ON join_cond [)] ... .
Addition:
Effect
Joins the columns of two or more data sources in a results set in a join expression. A join expression joins a left side with a right side, using either [INNER] JOIN or LEFT|RIGHT [OUTER] JOIN. A join expression can be an inner join (INNER) or an outer join (LEFT OUTER) or RIGHT OUTER) join. Every join expression must contain a join condition join_cond after ON (see below). The following applies to entries specified on the left side and on the right side:
The priority in which nested join expressions are evaluated is specified by the position of the ON conditions. From left to right, each ON condition is assigned to the directly preceding JOIN and creates a join expression. Join expressions of this type can be enclosed in parentheses, ( ). This is optional. Explicitly specified parentheses must match the parentheses specified implicitly by the ON conditions.
Example
See Multiple Joins.
Results set for inner joins
In a single results set, an inner join joins the columns of the rows in the results set of the left side with the columns of the rows in the results set of the right side. This results set contains all combinations of rows whose columns meet the condition join_cond. If there are no rows in the results set of the left and right sides that meet join_cond, a row is not created in the resulting results set.
Example
Results set for outer joins
The outer join creates the same results 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 results set, even if no rows on the other side meet the condition join_cond. The columns on the other side that do not meet the condition join_cond are filled with null values.
Notes
Example
... ON join_cond
Effect
Join condition. The syntax of the join conditions join_cond is the same as for the conditions sql_cond after the addition WHERE, but with the following differences:
Notes
Example
Join of the columns carrname, connid, and fldate of the database tables scarr, spfli, and sflight using two inner joins. This creates a list of flights from p_cityfr to p_cityto. An alias name is assigned to each table.
PARAMETERS: p_cityfr TYPE spfli-cityfrom,
p_cityto TYPE spfli-cityto.
TYPES: BEGIN OF wa,
fldate TYPE sflight-fldate,
carrname TYPE scarr-carrname,
connid TYPE spfli-connid,
END OF wa.
DATA itab TYPE SORTED TABLE OF wa
WITH UNIQUE KEY fldate carrname connid.
SELECT c~carrname, p~connid, f~fldate
FROM ( ( scarr AS c
INNER JOIN spfli AS p ON p~carrid = c~carrid
AND p~cityfrom = @p_cityfr
AND p~cityto = @p_cityto )
INNER JOIN sflight AS f ON f~carrid = p~carrid
AND f~connid = p~connid )
INTO CORRESPONDING FIELDS OF TABLE @itab.
cl_demo_output=>display( itab ).
Example
Join the columns carrid, carrname, and connid of the database tables scarr and spfli using a left outer join. For all flights that do not depart from p_cityfr, the column connid is set to the null value that was transformed to the type-friendly initial value (when passed to the associated data object). All the airlines that do not fly from p_cityfr are displayed.
PARAMETERS p_cityfr TYPE spfli-cityfrom.
TYPES: BEGIN OF wa,
carrid TYPE scarr-carrid,
carrname TYPE scarr-carrname,
connid TYPE spfli-connid,
END OF wa.
DATA itab TYPE SORTED TABLE OF wa
WITH NON-UNIQUE KEY carrid.
SELECT s~carrid, s~carrname, p~connid
FROM scarr AS s
LEFT OUTER JOIN spfli AS p ON s~carrid = p~carrid
AND p~cityfrom = @p_cityfr
INTO CORRESPONDING FIELDS OF TABLE @itab.
DELETE itab WHERE connid <> '0000'.
cl_demo_output=>display( itab ).