Google spreadsheet “=QUERY” join() equivalent func

Question

This question is concerning joining two databases in Google spreadsheet using =QUERY function

I have a table like so in range A1:C3

a d g
b e h
c f i

I have another table

c j m
a k n
b l o

I want the final table to look like this

a d g k n
b e h l o 
c f i j m

I can do this by using a vlookup function pretty easily in cell D1 and paste it down and across, but my dataset is huge. I would need a whole page of vlookups and Google Spreadsheet tells I'm at my limit in complexities.

I look at the Google's Query Language reference... there doesn't seem to be an type of "join" functions mentioned. You would think it would be an easy "join on A" type operation.

Can anybody solves this without a vlookup?

Answer 1

If you can map each "index" (a, b, c) to a specific row or column, then you could use the INDEX function.

In this case, you could probably map 'a' to column A (or row 1), 'b' to column B (or row 2), and so on.

Also, Merge Tables seem to address this exact use case.

Answer 2

With the 'other' table in A5:C7, please try:

=query({A1:C3,query(sort(A5:C7,1,TRUE),"Select Col2,Col3")})

Answer 3

Short answer

Google QUERY Language version 0.7 (2016) doesn't include a JOIN (LEFT JOIN) operator but this could be achived by using an array formula which result could be used as input for the QUERY function or for other uses.

Explanation

Array formulas and the array handling features of Google Sheets make possible to make a JOIN between two simple tables. In order to make easier to read, the proposed formula use named ranges instead of range references.

Named Ranges

• table1 : Sheet1!A1:C3
• table2 : Sheet2!A1:C3
• ID : Sheet1!A1:A3

Formula

=ArrayFormula(
   {
     table1,
     vlookup(ID,table2,COLUMN(Indirect("R1C2:R1C"&COLUMNS(table2),0)),0)
   }
)

Remarks:

• Using open ended ranges is possible but this could make the the spreadsheet slower.
• To spead up the recalculation time :
  1. Replace Indirect("R1C2:R1C"&COLUMNS(table2),0) by an array of constants from 2 to number of columns of table2.
  2. Remove the empty rows from the spreasheet

Example

See this sheet for an example

Note

On 2017 Google improved the official help article in English about QUERY, QUERY function. It still doesn't include yet topics like this but could be helpful to understand how it works.

Answer 4

You can use ARRAYFORMULA or YOU can just drag this formula: after an import or QUERY-ing the first table; in the D column:

=QUERY(Sheet2!A1:C3, "Select B,C WHERE A='" & A1 & "'", 0)