Skip to Content

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:

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

  1. B. Meijs

    Horst,
    Reading your blog posts about ABAP on WAS751 with much interest. Hope to be able to play around with a new version soon.

    What would be a valid use case for using Cross Joins? Can you give an example of a query that you can only solve using a cross join (or the simulated version)

    Regards
    Ben

    (0) 
  2. Avery Zhu

    Hi Horst,

    I use the CROSS JOIN in a CDS view but the word CROSS is underlined as syntax error. Did you  ever meet the same problem?

     

    Thanks and regards,

    Avery

    (0) 
  3. Conor White

    Hi Horst,

     

    Is it possible to use a subquery in the where clause of a cross join in the CDS view? What i’m trying to achieve is something like:

    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 not in (select arbgb from <sometable>);

    I get an error, not sure if i’m doing something wrong or it’s not supported(I’m new to CDS views).

     

    Kind regards,

     

    Conor

    (0) 

Leave a Reply