Skip to Content
Author's profile photo Horst Keller

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:

Assigned tags

      11 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo B. Meijs
      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      Since I am not an application expert, I have to search too, e.g.

      http://stackoverflow.com/questions/523091/sql-cross-join-what-use-has-anyone-found-for-it

      ?

      Author's profile photo Leon van Niekerk
      Leon van Niekerk

      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

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

       

      Apparently something is not up-to-date. Can the view be activated?

      Author's profile photo Former Member
      Former Member

      The system responsible told me the system version is 7.50, sorry for that stupid question...

      Author's profile photo Former Member
      Former Member

      Very Nice, Thanks for posting.

      Author's profile photo Former Member
      Former Member

      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

      Author's profile photo Horst Keller
      Horst Keller
      Blog Post Author

      No, no subqueries  in ABAP CDS up to now.

      Author's profile photo Pavel Astashonok
      Pavel Astashonok

      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?

      Author's profile photo Leon van Niekerk
      Leon van Niekerk

      For client dependent tables you could try using CLIENT SPECIFIED

       

      SELECT t001~mandt, t003~*
      FROM t001
      INNER JOIN t003 ON t001~mandt = t003~mandt CLIENT SPECIFIED
      WHERE t001~mandt = @sy-mandt