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.

Afbeelding 1.png

In the join, select MARA and MARD

Afbeelding 2.png

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.

Afbeelding 3.png

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):

Afbeelding 4.png

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

Afbeelding 5.png

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:

Afbeelding 6.png

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.

Afbeelding 1.png

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

Now, to show all materials with checking group 02:

Afbeelding 2.pngAfbeelding 3.png

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.

Afbeelding 4.png

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.

Afbeelding 5.png

Afbeelding 6.png

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.

Afbeelding 7.png

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):

Afbeelding 8.png

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

Afbeelding 9.png

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.

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Typewriter TW

    H.M.

    Thanks for this well explained document! Enjoyed reading it 🙂

    One question – have you selected LGORT also? If yes, in the report storage location is not populating.

    Also, if in report we want MATNR, WERKS, LGORT what additional step is needed?

    TW

    (0) 
    1. H.M Hofman Post author

      Just select them in the query builder. This does not change the data join, what I tried to focus on.

      The reason I choosed just these fields, is because I am in the system of a customer and I do not want to show customer specific data.

      (0) 
    1. Ravi K

      Hi, Nice document,i have not understand outer join ,Can you please explain how it will work with example?

      (0) 

Leave a Reply