How to do an outer join?

This has been bugging me for ages and I'm hopping that someone can help me out.

Basically in the database world there is the concept of an 'outer join'. Most often for me, I use a left outer join or a right outer join rather than a full outer join. In Excel VLookup is sort of a variation on this where only one of the joined records is returned.

Ok, here is an example which describes what I am looking for -

Ages = { 'Fred' 33; 'Josh' 23; 'Jane' 38; 'Mary' 16 };

Pets = {'Fred' 'Cat'; 'Fred' 'Dog'; 'Mary' 'Dog' };

% Left outer join Pets onto Ages on column 1 DesiredOutput = { 'Fred' 33 'Cat'; 'Fred' 33 'Dog'; 'Josh' 23 ''; 'Jane' 38 ''; 'Mary' 16 'Dog' };

You can see that I have two data sets being names and ages. The fact that these are cell arrays isn't too important for now. It is the concept that is important.

You will notice that the first column of each is a person's name. One person can only have one age but they can have zero to many pets. We seek to create a new data set which 'joins' these together in some fashion.

Obviously this could be done with a loop and find() but this is quite inefficient and just feels wrong. Any ideas on how to do this in a concise elegant fashion - ie set based?

Answer 1

[Oleg Komarov]

Since you're talking about joins I will use the Statistics Toolbox:

% Your data

Ages = { 'Fred' 33; 
         'Josh' 23; 
         'Jane' 38; 
         'Mary' 16 };
Pets = {'Fred' 'Cat';
        'Fred' 'Dog'; 
        'Mary' 'Dog'};

% Convert to datasets

Ages = dataset({Ages(:,1),'Name'},{Ages(:,2),'Age'});
Pets = dataset({Pets(:,1),'Name'},{Pets(:,2),'Pet'});

% Perform outer join

join(Ages,Pets,'Keys','Name','Type','outer','RightVars','Pet')

[Richard Willey]

The dataset array (and the join method) are also available in Statistics Toolbox.

[Stewart Charles]

Many thanks for this Oleg and Richard. This is exactly what I was looking for. Another useful function I have found too is ismember().

[Oleg Komarov]

@Richard: I meant Statistics TB. Typo corrected.

@Stewart: if you like my answer, accept it. Also, you have to perform ismember twice, that's why I preferred to refer to a Toolbox, more intuitive for a general user.

[Oleg Komarov]

The join is not a trivial set operation (as you can see from edit join). To give an idea of the complexity you can try on this example data:

Ages = { 'Fred' 33;

'Josh' 23;

'Jane' 38;

'Mary' 16 };

Pets = {'Fred' 'Cat';

'Fred' 'Whale';

'Fred' 'Dog';

'Mary' 'Dog';

'Jim' 'Whale'};