Left Outer Join

[Shay A]

Hi,
Let's say I have 100 rows in my left table and 5 rows in my right table. Under which circumstances I will get more than 100 rows after the merge?

TY

 

[Hui]

When none of the rows in the Right Table are duplicates of the Left Table
based on a Merge key or Merge Fields

 

[Shay A]

But shouldn't I get exactly 100 rows? Otherwise what is the definition for left outer join? It keeps all records from left table

 

[Hui]

Setup a small data set and try it
Try different merge types

 

[Chihiro]

@Shay A

Only time when Left Outer will produce more result than Left table, is when Right table has duplicate values that are match to one of the key held in Left table.

Ex: Left key holds 1 to 100. Right table has 1, 1, 2, 3, 4. This will duplicate key 1, twice when expanded. 2, 3, 4 will be one to one, rest will return null.

Typically speaking, relationship between tables are One to many/May to one.
One to one rarely exists, as that type of related info should most often be stored in same table.

You could have many to many, but those should be handled with care and not used within analysis data model (though quite valid in data entry application).