SQL Syntax NOT IN for Google BigQuery

Question

The Google SQL syntax guide found here https://developers.google.com/bigquery/docs/query-reference does not seem to address use of the NOT IN terminology.

I get the error

Query Failed
Error: Encountered "" at line 0, column 0. Was expecting one of:  

(and then Google lists nothing after the words 'one of')

when my query is

SELECT ageinmonths,amountborrowed,borrowerrate,creationdate,creditgrade,
       debttoincomeratio,groupkey,key,lenderrate,listingkey,modifieddata,
       originationdate,prosperrating,status,term
FROM prosperloans1.loans2 as big,
     prosperloans1.dupelistingkeysinloans2 as small
WHERE big.listingkey NOT IN small.listingkey

the table 'small' has 14 rows. The table big has 57K rows.

Any ideas? Not sure if Google SQL supports NOT IN.

Thanks Shawn

Answer 1

Semijoins and antijoins use the following syntax: SELECT field FROM table WHERE field [NOT] IN (SELECT other_field FROM other_table WHERE foo = 'bar')

So your updated query with the subselect is the correct version.