Q:

Google sheets query - Cartesian join between two ranges

How can we achieve the following join between two ranges on Google Spreadsheet using the query function?
Range 1
Model   Style
Nissan  Coupe
Nissan  Truck
Honda   Sedan
Honda   Hatchback
Toyata  Truck
Honda   Coupe

Range 2
Style       Engine
Coupe       1200 cc
Coupe       1500 cc
Sedan       1000 cc
Truck       2000 cc
Sedan       1800 cc
Hatchback   800 cc

Output
Model   Style       Engine
Nissan  Coupe       1200 cc
Nissan  Coupe       1500 cc
Honda   Sedan       1000 cc
Nissan  Truck       2000 cc
Honda   Sedan       1800 cc
Honda   Hatchback   800 cc
Honda   Coupe       1200 cc
Honda   Coupe       1500 cc
Toyata  Truck       2000 cc

A:

The Data Visualization Language, which we use via the query command, does not support joins of any kind. The desired result can be obtained with a custom function. 
Its format is =cartesianJoin(arr1, arr2, col1, col2) where the first two arguments are ranges to be joined, and the other two are column numbers (relative to the range) on which the join is to be made. For example, in your case the first array could be in A2:B8 and the second in D2:E8. Then the formula would be 
=cartesianJoin(A2:B8, D2:E8, 2, 1)

indicating that we join by the second column of first array (B) being equal to the first column of the second array (D). 
function cartesianJoin(arr1, arr2, col1, col2) {
  var output = [];
  for (var i = 0; i < arr1.length; i++) {
    var r1 = arr1[i];
    var matching = arr2.filter(function(r2) {
      return r1[col1 - 1] === r2[col2 - 1];
    }).map(function(r2) {
      var copyr2 = r2.slice(0);
      copyr2.splice(col2 - 1, 1);
      return r1.concat(copyr2);
    });
    output = output.concat(matching);
  }
  return output;                     
}

Logic: for each row of first array (r1), filter the second array by the equality requirement, then concatenate each of the matching rows to r1, first removing the matched column so it does not appear twice.
Screenshot:

