Menu
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
548
BigQuery supports the Google Standard SQL dialect, but a legacy SQL dialect is also available. If you are new to BigQuery, you should use Google Standard SQL as it supports the broadest range of functionality. For example, features such as DDL and DML statements are only supported using Google Standard SQL.
NOT EQUAL TO (!=) and EXISTS... EQUAL TO Giving Different Results.
Description. The SQL NOT condition (sometimes called the NOT Operator) is used to negate a condition in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement.
Google BigQuery is a cloud-based Architecture and provides exceptional performance as it can auto-scale up and down based on the data load and performs data analysis efficiently. On the other hand, SQL Server is based on client-server architecture and has fixed performance throughout unless the user scales it manually.
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.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
