Which JOIN to use?

[sculptorlv]

Hello !

I have to tables, which I can get by LOAD statement.

Tables 1 - Salaries

ID Name Salary

1   John   300

2   Smith 400

3  Angel  400

4 Bob       300

Table 2 - Bonuses

4 Bob   200

5 Mike  700

6 Alex   700

Which kind of Join statement I need to use in order to get Joined table 3 with all mentioned people:

ID Name Salary  Bonuses

1   John   300        0 (or NULL)

2   Smith 400        0

3  Angel  400        0

4 Bob       300      200

5 Mike     0           700

6 Alex   0              700

[CurtDaughtry]

Use an outer join to keep records from both tables

[sculptorlv]

In case of outer join, Bob row will be dublicated and in m dataset I will have 95% of dublicated rows..

[CurtDaughtry]

That's not correct.

If you use this script (see below), you will get this output (see image).

table1:
load * inline [
ID, Name, Salary
1,John,300
2,Smith,400
3, Angel,400
4,Bob,300
];

outer join (table1)
table2:
load * inline [
ID, Name, Bonus
4,Bob,200
5,Mike,700
6,Alex,700
];