Difference between Matlab JOIN vs. INNERJOIN

Question

In SQL, JOIN and INNER JOIN mean the same thing. In Matlab, they are different commands. Just from perusing the documentation thus far, they appear on the surface to fufill the same general function, with possible differences in the details, as controlled by parameters. I am slogging through the individual examples and may (or may not) find the fundamental difference. However, I feel that the difference should not be a subtlety that users have to ferrut out of the examples. These are two separate commands, and the documentation should make it clear up front why they are both needed. Would anyone be able to chime in about the key difference? Perhaps it could become a request to place it front and centre in the documentation.

Answer 1

I've empirically characterized the difference between JOIN and INNERJOIN (some would refer to this as reverse engineering). I'll summarize from the perspective of one who is comfortable with SQL. As I am new to SQL-like operations in Matlab, I've only been able to test drive it to a limited degree, but the INNERJOIN appears to join records in the same manner as SQL. Since SQL is a pretty open language, the behavioural specification of INNERJOIN is readily available, and I won't dwell on that. It's Matlab's JOIN that I need to suss out.

In short, from my testing, Matlab's JOIN seems to "join" the rows in the two operand table in a manner more like Excel's VLOOKUP rather than any of the JOINS in SQL. In general, the main differences with SQL joins seem to be (i) that the right hand table cannot have repeating values in the columns used matching up rows between the two tables and (ii) all combinations of values in the key columns of the left hand table must show up in the right hand table.

Here is the empirical testing. First, prepare the test tables:

a=array2table([
   1  2
   3  4
   5  4
],'VariableNames',{'col1','col2'})

b=array2table([
   4  7
   4  8
   6  9
],'VariableNames',{'col2','col3'})

c=array2table([
   2  10
   4  8
   6  9
],'VariableNames',{'col2','col3'})

d=array2table([
   2  10
   4  8
   6  9
   6  11
],'VariableNames',{'col2','col3'})

a2=array2table([
   1  2
   3  4
   5  4
   20 99
],'VariableNames',{'col1','col2'})

Here are the tests:

>> join(a,b)
Error using table/join (line 111)
The key variable for B must have unique values.

>> join(a,c)
ans = col1    col2    col3
      ____    ____    ____
      1       2       10
      3       4        8
      5       4        8

>> join(a,d)
Error using table/join (line 111)
The key variable for B must have unique values.

>> join(a2,c)
Error using table/join (line 130)
The key variable for B must contain all values in the key
variable for A. 

The first thing to notice is that JOIN is not a symmetric operation with respect to the two tables.

It seems that the 2nd table argument is used as a lookup table. Unlike SQL joins, Matlab throws an error if it can't find a match in the 2nd table [See join(a2,d)]. This is somewhat hinted at in the documentation, though not entirely clearly. For example, it says that the key values must be common to both tables, but join(a,c) clearly shows that the tables do not have to have common key values. On the contrary, just as one would expect of a lookup table, 2nd table contains entries that aren't matched do not throw errors.

Another difference with SQL joins is that records that cause the key values to replicate in 2nd table are not allowed in Matlab's join. [See join(a,b) & join(a,d)]. In contrast, the fields used for matching records between tables aren't even referred to as keys in SQL, and hence can have non-unique values in either of the two tables. The disallowance of repeated key values in the 2nd table is consistent with the view of the 2nd table as a lookup table. On the other hand, repetition on of key values are permitted in the 1st table.