Question: How do I define in a simple single line a temporary constant table in BigQuery (or Postgres the syntax of which will likely work in BigQuery)?

```sql
with my_fruits as (
--- ??? define a table 
--- consisting of a single column fruit_name
--- with values ('apple', 'pear', 'gooseberry', ...  (12 more fruits))
)
select * from my_fruits left join 
(select fruit_name, 1 is_present from my_dataset.your_fruits)
using (fruit_name)

```

Answer: 
You can use [UNNEST](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#unnest):

```
select * from unnest(['apple', 'pear', 'gooseberry']) as fruit_name

```

You also can join with UNNEST [directly](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays).
With Postgres you can easily create a multi-column constant table:

```
select * from (values (1,1,1), (1,2,3)) as x(one, two, three)

```

Unfortunately above doesn't work for BigQuery and not sure if there is a proper substitute for it.