ABAP News for Release 7.51 – Cross Join in Open SQL and ABAP CDS

A cross join returns the Cartesian product of rows of the two joined data sources. All entries on the left side are combined with all entries on the right side. The number of rows in the result set is the number of rows on the left side multiplied by the number of rows on the right side.

With release 7.51, Open SQL as well as ABAP CDS support cross joins.

Open SQL

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. 

ABAP CDS

@AbapCatalog.sqlViewName: 'DEMO_CDS_CRSJN' 
@AccessControl.authorizationCheck: #NOT_REQUIRED 
define view demo_cds_cross_join 
  as select from 
                 t000 
      cross join t100 
    { 
      t000.mandt, 
      t000.mtext, 
      t100.sprsl, 
      t100.arbgb, 
      t100.msgnr, 
      t100.text 
    } 
    where 
      t100.arbgb = 'SABAPDEMOS' 

The examples show a cross join of table T000 that contains the client ids 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.

A cross join should only be used with extreme caution. It is not possible to specify an ON condition. Therefore, all data of all involved data sources is read. In the case of very large datasets, the result set (whose number of rows is always the product of the number of all rows of both data sources) can quickly become very large.

So, why do we offer it? Only because we can? No, there are use cases and application developers asked for it. Furthermore, you can simulate a cross join anyhow as follows:

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

A cross join returns exactly the same result set as an inner (or outer) join whose ON condition is always true (you can test that with ASSERT). So it is better to offer the explicit syntax CROSS JOIN instead of forcing developers to use a dummy ON condition if they need the cross product of data sources.

Nevertheless, for large data sources, a cross join is never such a good idea. Ok, you can use a WHERE condition as above . But a cross join with a WHERE condition has the same result as an inner join with an identical ON condition.

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

Gives the same again! And unlike the inner join, in a cross join all data is read first before the condition is evaluated. In an inner join only data that meets the ON condition is read.

Bottom line

With ABAP 7.51, cross joins are possible in Open SQL and ABAP CDS, but use them after careful consideration only.

For more information see:

• Joins in Open SQL
• Joins in ABAP CDS