CDS view allows following types of Joins:
1. Inner Join: Inner join requires each row in the two joined tables to have matching column values, and is a commonly used join operation in applications but should not be assumed to be the best choice in all situations. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied by matching non-NULL values, column values for each matched pair of rows of A and B are combined into a result row. E.g.
2. Left Outer Join: The result of a left outer join (or simply left join) for tables A and B always contains all rows of the “left” table (A), even if the join-condition does not find any matching row in the “right” table (B). This means that if the ON clause matches 0 (zero) rows in B (for a given row in A), the join will still return a row in the result (for that row)—but with NULL in each column from B. A left outer join returns all the values from an inner join plus all values in the left table that do not match to the right table, including rows with NULL (empty) values in the link column. e.g.
In this example, all records found in VBAK are considered in the output whereas records in VBAP which doesn’t have JOIN matching condition will have a NULL value in output as below:
3. Right Outer Join: A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those rows that have no match in B.
A right outer join returns all the values from the right table and matched values from the left table (NULL in the case of no matching join predicate). For example, this allows us to find each employee and his or her department, but still show departments that have no employees.
Right outer join is just reverse of table sequence of the Left outer join.
And the output is same:
4. Cross Join: CROSS JOIN returns the Cartesian product of rows from tables in the join. In other words, it will produce rows which combine each row from the first table with each row from the second table
Cross Join should be carefully used because a cartesian product can create a large data set.