Full outer join of two tables

Question

I have two tables, say, price (5 rows, 1 column) and pricedate (6 rows, 1 column), they don't have anything in common. I want to obtain full outer join, so that my new table ABC has 30 rows, basically, every row in pricedate has all prices.

How to do that? Can I use full outer join or something else?

Answer 1

My answer covers two cases:

1. P (prices) and PD (pricedates) are tables
2. P (prices) and PD (pricedates) are arrays

Here is the code:

% generate some sample data
price      = [1;2;3;4;5];
pricedate1 = [11;12;13;14;15;16];
pricedate2 = pricedate1+10;
pricedate3 = pricedate2+10;

% create sample table
P  = table(price);
PD = table(pricedate1,pricedate2,pricedate3);

% this is the code if P and PD are tables
TMP = repmat(P{:,1},1,size(PD,1))';
T = [repmat(PD,size(P,1),1),table(TMP(:),'VariableNames',{'price'})]

% create sample arrays
P  = price;
PD = [pricedate1,pricedate2,pricedate3];

% this is the code if P and PD are arrays
TMP = repmat(P,1,size(PD,1))'
T = [repmat(PD,size(P,1),1), TMP(:)]

It is possible to write it in a single line to to eliminate the TMP-variable.

For data of type table:

T = [repmat(PD,size(P,1),1),table(subsref(repmat(P{:,1},1,size(PD,1))',struct('type','()','subs',{{':'}})),'VariableNames',{'price'})]

For data of type array:

T = [repmat(PD,size(P,1),1),subsref(repmat(P,1,size(PD,1))',struct('type','()','subs',{{':'}}))];

I admit, that the single line looks quite cryptic.

Answer 2

Since it looks like you are trying to do a Cartesian Product, i suggest using the allcomb function from the FileExchange, http://www.mathworks.com/matlabcentral/fileexchange/10064-allcomb

since your data is also in (matlab) tables (hopefully), you can do:

function Tallcomb = allcomb_2tables(T1, T2)
    %USe allcomb to create the cartesian product of the indexes
    idxAB = allcomb(1:height(T1),1:height(T2));

    % Now horzcat the multi-broadcasted tables:
    Tallcomb = horzcat(T1(idxAB(:,1),:), T2(idxAB(:,2),:) );

end