MINUS functionality in BigQuery database

Question

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 --> ??

Answer 1

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

Answer 2

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.