Join types SAP queries

[former_member218251]

Quickviewer / Query – Joining tables.

There are 2 ways of joining tables in SAP: Inner and outer joins.

If you have to following 2 tables, that you want to join, you see that there are 2 matching records: 11 and 12.

Table 1
11
12
13
14

Table 2
11
12
21
22

Using this table to explain the matter:

1: Inner join

This is a 1:1 relation. In short, this will only show records when the data is available in both tables.

Using an inner join, you will have a better performance. It is advisable to use this when possible.

When joining these 2 tables, the data that is available in both tables is 11 and 12. This is what you will get as a result.

Table 1	Table 2
11	11
12	12

2: outer Join

This is a 1:n relation. In short, this will show all records of the left table in the join, regardless if it is available in the joined table.

When joining these 2 tables, it will show all data of Table 1. Only there where there is a match, it will show the data of Table 2.

Table 1	Table 2
11	11
12	12
13
14

Example,:

you want to show all material information using MARA, and see wich materials are available in a storage location (MARD).

Inner Join:

Create a query using a Table Join.

In the join, select MARA and MARD

Note the connecting line, this shows you have an inner join.

Now click back and select the fields you want to use for selection and  output.

For this example, I have selected as selection and output: MARA-MATNR and MARD-WERKS.

I have 2 material numbers: 51 and 801.  Material 51 is not available in the MARD table.

The result from running the report is that only for 801 the results will be shown (inner join only shows data that is available in both tables):

Outer Join:

I go back to the data join, and change the join to a outer join.

For this, right-click on the connecting line and select Left Outer Join

Note the connecting line is now showing it is an outer join.

When now executing the report with the same selection, this is the result:

With a left outer join, all data from the left table will be shown and only there where this data is also available in the right table it will be shown.

In this example, material 51 is available in the left table (MARA) so the material number is listed. Since there is no info in the MARD, this info will remain empty.

Example 2:

Inner join preferred:

You have created a new Availability check and want to see all materials where (still) the old checking group is used.

For this, the table with the checking groups is TMVF. The master data is stored in MARC.

Since you only want to see the data that is available in both tables, you can use an inner join.

For the left table, you pick TMVF, since this is where you want to make the most important selection for your report.

The right table then is the MARC table.

You select the MTVFP field and MATNR and WERKS as both input and output fields.

Now, to show all materials with checking group 02:

Outer join:

You have a list of invoices, some of them might be in an invoice list. You want to see all invoices and when they are in an invoice list, the IL number should be shown.

Left table is the VBRK. Right table is the VBRL.

Note that I have changed the join to VBELN_VF and made it a Left Outer Join.

Now I select VBELN from VBRK and VBELN from VBRL as input and output.

Extra:

If I want to add more info about the invoice list, I need to also use the VBRK table. But I can only use this table once.

To be able to add info a second time from VBRK, I have to create an alias:

Go to the join and click on the Alias button. In the pop-up, click on create.

Fill in the table name, and think of a name for your alias. Accept your input.

Now insert the newly created 'alias' table and use an inner join from VBRL to the alias (outer join can only be used once in a dataflow):

Now when you select the billing type as output from VBRK and the alias, you will get the following result:

As you can see, the outer join is ignored because of the inner join further to the right. When using a right outer join, it should be the last table in the flow.