How to use Except clause in Bigquery?

Question

I am trying to use the existing Except clause in Bigquery. Please find my query below

select * EXCEPT (b.hosp_id, b.person_id,c.hosp_id) from 
person a 
inner join hospital b
on a.hosp_id= b.hosp_id
inner join reading c
on a.hosp_id= c.hosp_id

As you can see I am using 3 tables. All the 3 tables have the hosp_id column, so I would like to remove duplicate columns which are b.hosp_id and c.hosp_id. Simlarly, I would like to remove b.person_id column as well.

When I execute the above query, I get the syntax error as shown below

Syntax error: Expected ")" or "," but got "." at [9:19]

Please note that all the columns that I am using in Except clause is present in the tables used. Additional info is all the tables used are temp tables created using with clause. When I do the same manually by selecting column of interest, it works fine. But I have several columns and can't do this manually.

Can you help? I am trying to learn Bigquery. Your inputs would help

score 7 · Answer 1 · answered Aug 25 '19 at 12:02

7

I use the EXCEPT on a per-table basis:

select p.* EXCEPT (hosp_id, person_id),
       h.*,
       r.* EXCEPT (hosp_id)
from person p inner join
     hospital h
     on p.hosp_id = h.hosp_id inner join
     reading r
     on p.hosp_id = r.hosp_id;

Note that this also uses meaningful abbreviations for table aliases, which makes the query much simpler to understand.

In your case, I don't think you need EXCEPT at all if you use the USING clause.

answered Aug 25 '19 at 12:02

Gordon Linoff

1,242,037
58
646
786

1

Good to know this approach as well. Upvoted. Thank you. – The Great Aug 26 '19 at 00:36

score 3 · Accepted Answer · answered Aug 25 '19 at 06:14

3

Try this instead:

select * EXCEPT (person_id) from 
person a 
inner join hospital b
using (hosp_id)
inner join reading c
using (hosp_id)

You can only put column names (not paths) in the EXCEPT list, and you can simply avoid projecting the duplicate columns with USING instead of ON.

answered Aug 25 '19 at 06:14

Elliott Brossard

32,095
2
67
99

Hi, Thanks for the response. Will try..Upvoted. By path, do you mean alias? – The Great Aug 25 '19 at 07:24
I tried without alias for a column (ex - instead of `c.reading_number`, I just used `reading_number` and it works. Can bigquery automatically identify the source table of that column mentioned? – The Great Aug 25 '19 at 07:34
1

By path I mean something of the form `a.b` with a dot in between names. – Elliott Brossard Aug 25 '19 at 21:12

How to use Except clause in Bigquery?

2 Answers2