Google Sheets SQL – Using ‘WHERE NOT IN’ Subquery

google sheets

I'm attempting to perform the following SQL command concept, but with two sheets in the same google spreadsheet document:

select * from table1
where column1 not in (select column1 from table2)

How do I achieve the same subquery concept using Google Spreasheet SQL?

Update

Here is a conceptual example of what I'd like to accomplish: I have a list of requirements on one sheet, and a requirement accomplishment log on another sheet. The log is simply the person's name and the name of the requirement (matching the other sheet). I'd like to see a report on all the requirements that do not exist in the requirement accomplishment log.

Best Answer

Maybe something like this and with filtering to select "Not accomplished":

WA59459 example

The formula in C2 (copied down) is:

=if(not(iserror(vlookup(B2,Sheet2!B:B,1,0))),"","Not accomplished")

Code

function mySQL(ref, dates) {
  var output = [];
  for(var i = 0, iLen = ref.length; i < iLen; i++) {
    var user = ref[i][0], begin = new Date(ref[i][1]), end = new Date(ref[i][2]);
    for(var j = 0, jLen = dates.length; j < jLen; j++) {
      var date = new Date(dates[j]);
      if(date >= begin && date <= end) {
        output.push([date, user]);
      }
    }
  }
  return output;  
}