What does it do? Joins the columns of two or more database tables in a results set.
The syntax of join is a recursively nestable join expression. A join expression consists of a left side and a right side, joined using either [INNER] JOIN or LEFT [OUTER] JOIN. A join expression can be an inner join (INNER) or an outer join ( LEFT OUTER). Every join expression can be enclosed in parentheses. When a join expression is used, the SELECT statement is not subject to SAP buffering.
On the left side, either a transparent database table, a view dbtab_left, or another join expression join can be specified. On the right side, a single transparent database table or a view dbtab_right must be specified, alongside join conditions join_cond after ON. In this way, it is possible to specify a maximum of 24 join expressions after FROM; these expressions join 25 transparent database tables or views together. Pooled and cluster tables cannot be joined using join expressions.
For each of the specified database tables, or for each view, AS can be used to specify an alternative table name tabalias. A database table or a view can exist more than once within a join expression, and can have various alternative names.
Results set for inner joins In a single results set, an inner join links 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, then a row is not created in the resulting results set.
Example ABAP Coding See Joins.
Results set for outer joins In principle, the outer join creates the same results set as the inner join, with the difference that, for each selected row on the left side, at least one row is created in the results set, even if no rows on the right side fulfill the condition join_cond. The columns on the right side that do not meet the condition join_cond are filled with null values.
Latest notes:If columns from the right side are specified after the addition ORDER BY, the sort order in the case of null values may be determined by the database system.
Example ABAP Coding See Joins.
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: After ON, at least one comparison must be specified. Single comparisons can only be joined using AND. All comparisons must contain a column of the database table or view dbtab_right of the right side, as an operand. The following additions cannot be used: NOT, LIKE, IN. Subqueries cannot be used. In the outer join, only equality comparisons are possible (=, EQ). If there is an outer join after FROM, the join condition of each join expression must contain at least one comparison between the columns on the left and right sides. In outer joins, all comparisons that contain columns from the database table or view dbtab_right on the right side (as an operand) must be specified in the associated join condition. These columns are not allowed as operands in the WHERE condition of the same SELECT statement. Comparisons between table columns that do not have the same data type and length in the database can behave differently on different database platforms. The different behavior can result in different results or in SQL errors on individual platforms. This is because the join condition is evaluated fully in the database and no ABAP type conversion takes place beforehand. The behavior displayed depends fully on the conversion rules of the database. However, databases generally offer fewer conversion options than ABAP.
Latest notes:The results sets of the left and right sides are independent of each other and are constructed in the same way as SELECT statements. For example, a WHERE condition for a column in a database table applies to precisely the results set of this table. Inner joins between two individual database tables are commutative. If the same column name appears in multiple database tables of a single join expression, these tables must be identified in all other additions of the SELECT statement using the column selector ~. Since join expressions are not subject to SAP buffering, they should not be used on buffered tables. Instead it may be a good idea to use the addition FOR ALL ENTRIES in these cases, which can access the table buffer. It is strongly recommended that join conditions are used only between database columns with the same type and length.
