CODEWITHSUNDEEP
google-bigquery
user3782531
user3782531

Reputation: 129

MINUS functionality in BigQuery database

I am new to BigQuery database.

Like in Oracle database MINUS operator what is the same functionality in BigQuery? I did not find MINUS operator in BigQuery.

Oracle --> Minus
BigQuery --> ??

Upvotes: 12

Views: 38488

Answers (4)

Mr. Llama
Mr. Llama

Reputation: 20909

BigQuery doesn't have "MINUS", but it does have the functionally identical "EXCEPT DISTINCT".

Upvotes: 8

Abhi
Abhi

Reputation: 11

StandardSQL Output for MINUS where ID is the composite key or primary key in Table 1 and Table2

same concept as Vamsi Mohan's

Select ID, Name from Table 1
where ID not in (Select distinct ID in Table 2)

Upvotes: 1

Vamsi Namburu
Vamsi Namburu

Reputation: 86

with whole as
( select 1 as id, 'One' as value
  union all
  select 2 as id, 'Two' as value
  union all
  select 3 as id, 'Three' as value
  ),
  sub_set as
  (
   select 1 as id, 'One' as value
  union all
  select 2 as id, 'Two' as value
  )
  select * from whole
  except distinct
  select * from sub_set

Result was

    3 Three

Refer: https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#except

I am getting the error EXCEPT ALL is not supported, DISTINCT worked. Hope this helps.

Upvotes: 7

Shayan Masood
Shayan Masood

Reputation: 1057

Though there is no MINUS function in BigQuery, you can use a LEFT OUTER JOIN as an alternative. 

 SELECT name, uid FROM a
 MINUS
 SELECT name, uid FROM b

Can be written as: 

SELECT a.name, a.uid
FROM a LEFT OUTER JOIN b ON a.name= b.name AND a.uid= b.uid
WHERE b.name IS NULL

Upvotes: 7

Related Questions