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:
