Question: Which of the two queries below would be more efficient query-wise and better in terms of etiquette? Is there a difference? Just trying to see which would be better so I can get in the habit of trying to make my queries a bit more efficient.

```
select
a,
b,
c

from TABLE1
left join TABLE2

union all

select
d,
e,
f
from TABLE3
left join TABLE2

```

or 

```
with TABLES as (
select
a,
b,
c
from TABLE1

union all

select
d,
e,
f
from TABLE3)

select *

from TABLES
left join TABLE2

```

Answer: 
In general, I find it good practice to minimize joins when using BQ. Because it is a columnar database, unions are pretty 'easy'. However, your example is a bit unclear, as `TABLE1` and `TABLE3` have different columns (`a,b,c` vs `d,e,f`). If the join to `TABLE2` is **different** for each table, then I would recommend doing the joins first and *then* unioning the results, instead of trying a more complicated join later on.
It depends on indexes and table sizes. Most databases have a way to explain how it handles queries. Many databases have the keyword EXPLAIN for that. Try it, or something similar for your database.