Skip to Content

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:

10 Comments
You must be Logged on to comment or reply to a post.
  • 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

    • I’ve got an example (Hi Ben ;).

      This statement updates the material description of the languages that doens’t exist ( and given in selection screen) , with the description of a source language that does exist(give in selection screen).

      "Using a cross join, we can combine the source language MAKT data, with the destination languages T002C
          INSERT makt FROM " SELECT cannot be used on the table being modified ("MAKT").
          (  
             SELECT
               FROM mgv_v_makt
              INNER JOIN t002c            "Languages in use
                 ON 1 = 1                 "Get all possible combination using a cross join....
             FIELDS mgv_v_makt~matnr,
                    t002c~spras,          "destination language code
                    mgv_v_makt~maktx,     "source language description
                    mgv_v_makt~maktg      "source language description
              WHERE matnr IN
              (
              SELECT
                FROM mara
              FIELDS mara~matnr
               WHERE mara~matnr IN @s_matnr
                 AND mara~lvorm = ' '
              )
               AND t002c~spras IN @s_langu        "destination language
               AND t002c~lainst = 'X'
               AND mgv_v_makt~spras = @p_src      "source language
               AND NOT EXISTS                     "make sure the language to insert, doesn't exist
               ( SELECT
                   FROM mgv_v_makt AS makt_sub
                 FIELDS mandt
                  WHERE makt_sub~matnr = mgv_v_makt~matnr
                    AND makt_sub~spras = t002c~spras
               )
      
          ).
      

      Thank for the tip how to simulate one..we’re here on 7.50

      Leon

  • 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

  • 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

  • Furthermore, you can simulate a cross join anyhow as follows: ON 1 = 1

     

    So why hadn’t you implemented this syntax earlier? This sample

    SELECT t000~mandt, t000~mtext, t100~*
           FROM t000 JOIN t100 ON 1 = 1

    doesn’t work on my 7.40 SP4 system.

    It have no sense to introduce two variants for the same concept simultaneously. Wouldn’t it be more rational to allow ON dummy conditional long earlier?