Syntax IN for Google

Question

在这里找到的Google SQL语法指南https://developers.google.com/bigquery/docs/query-reference似乎没有解决not IN术语的使用问题。

我得到了错误

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

(然后谷歌在“one of”后面什么也没有列出)

当我的查询是

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

表'small‘有14行。表big有57K行。

有什么想法吗？不确定Google SQL是否支持Not IN。

谢谢Shawn

Answer 1

半联接和反联接使用以下语法：SELECT field FROM table WHERE field [NOT] IN (SELECT other_field FROM other_table WHERE foo = 'bar')

因此，使用subselect更新的查询是正确的版本。