Google spreadsheet “=QUERY” join() equivalent function?

by

Short answer

Google QUERY Language version 0.7 (2016) doesn’t include a JOIN (LEFT JOIN) operator but this could be achieved 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 spreadsheet slower.
  • To speed 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 spreadsheet

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.

More Related Contents:

  1. How to display data in JOIN, and return null value? [closed]
  2. How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]
  3. Query to get only numbers from a string
  4. How to create a Calendar table for 100 years in Sql [closed]
  5. SQL multiple join statement
  6. Paging with Oracle
  7. What’s the difference between TRUNCATE and DELETE in SQL
  8. How to select from subquery using Laravel Query Builder?
  9. Error 1046 No database Selected, how to resolve?
  10. Calculating difference between two timestamps in Oracle in milliseconds
  11. Identity column value suddenly jumps to 1001 in sql server [duplicate]
  12. How to concatenate columns in a Postgres SELECT?
  13. How can I find which tables reference a given table in Oracle SQL Developer?
  14. PostgreSQL multi INSERT…RETURNING with multiple columns
  15. Selecting N rows in SQL Server
  16. Copy rows from one table to another, ignoring duplicates
  17. A SQL Query to select a string between two known strings
  18. DB design to use sub-type or not?
  19. How can I INSERT data into two tables simultaneously in SQL Server?
  20. Extract date (yyyy/mm/dd) from a timestamp in PostgreSQL
  21. How to run a stored procedure every day in SQL Server Express Edition?
  22. Cannot use a CONTAINS or FREETEXT predicate on table or indexed view because it is not full-text indexed
  23. SQL poor stored procedure execution plan performance – parameter sniffing
  24. SQL for ordering by number – 1,2,3,4 etc instead of 1,10,11,12
  25. RODBC Temporary Table Issue when connecting to MS SQL Server
  26. How can I solve ORA-00911: invalid character error?
  27. Try_Convert for SQL Server 2008 R2
  28. SQL Server variable scope in a stored procedure
  29. converting int to real in sqlite
  30. What is difference between foreign key and reference key?

Leave a Comment