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

Answer 1

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.

Answer 2

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.