此处找到的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:
(然后Google在“其中一个”之后没有列出任何内容)
当我的查询是
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行 . 表大有57K行 .
有任何想法吗?不确定Google SQL是否支持NOT IN .
谢谢肖恩
1 回答
Semijoins和反连接使用以下语法:
SELECT field FROM table WHERE field [NOT] IN (SELECT other_field FROM other_table WHERE foo = 'bar')
因此,使用subselect的更新查询是正确的版本 .