Add extra row to SQL query without specifying all columns

Question

I have a table 'Customers' which has about 30 columns of data. This comes from a 3rd party data source, so I have no control over the number of columns or ability to insert into the table.

Select * from customers 

What I would like to do in my query is a get a full list of customers, plus one extra row where I can link transactions to an 'unkonwn' customer.

On the extra row, I'd like to define just a few columns and have the remaining be blank.

SELECT -1 customer_id, 'No Customer' customer_name FROM DUAL

So essentially I need all records from the customer table, plus one additional 'dummy' record where all columns are null EXCEPT for the customer_id and customer_name

This can be accomplished with a UNION, however that requires you to explicitly define all of the unused columns as NULL. This is a problem because the 3rd party may add additional columns in the future which would break the query.

Is there any way to add an extra row to a query (union a row) without explicitly defining ALL columns, and instead define only the columns I don't want as NULL?

Example:

<h1>
Existing Customer table:
</h1>

<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Customer_id</th><th>customer_name</th><th>customer_city</th><th>customer_industry</th></tr></thead><tbody>
 <tr><td>5453</td><td>Apple Inc.</td><td>Cupertino</td><td>Technology</td></tr>
 <tr><td>7865</td><td>Union Pacific</td><td>Omaha</td><td>Shipping</td></tr>
</tbody></table>

<h1>With extra data</h1>
<table class="tableizer-table">
<thead><tr class="tableizer-firstrow"><th>Customer_id</th><th>customer_name</th><th>customer_city</th><th>customer_industry</th></tr></thead><tbody>
 <tr><td>5453</td><td>Apple Inc.</td><td>Cupertino</td><td>Technology</td></tr>
 <tr><td>7865</td><td>Union Pacific</td><td>Omaha</td><td>Shipping</td></tr>
 <tr><td>-1</td><td>Unknown Customer</td><td>[NULL]</td><td>[NULL]</td></tr>
</tbody></table>

Answer 1

Assuming customer_id and customer_name are the first two columns, you can do:

select x.customer_id, x.customer_name, c.* except (customer_id, customer_name)
from (select -1 as customer_id, 'No Customer' as customer_name) x left join
     customer c
     on 1 = 0;

The except functionality is quite useful in BigQuery.

Answer 2

Gordon's solution is great [as always :o)] but it still has solid drawback - it heavily depends on order of columns in customer table. Meaning if customer_id and customer_name are not two first columns in customer table that solution will fail if you will union it with original query.

Namely, below (as an example) will either fail or just will misplace columns (if types are match):

#standardSQL
WITH `project.dataset.customer` AS (
  SELECT 'many other columns here' other, 1 customer_id, 'abc' customer_name 
)
SELECT x.customer_id, x.customer_name, c.* EXCEPT (customer_id, customer_name)
FROM (SELECT -1 AS customer_id, 'No Customer' AS customer_name) x 
LEFT JOIN `project.dataset.customer` c
ON 1 = 0 
UNION ALL SELECT * FROM `project.dataset.customer`   

To address this issue, you should use yet another quite useful in BigQuery feature -

SELECT * REPLACE()    

so below is example (BigQuery Standard SQL) of solution to address above described issue

#standardSQL
WITH `project.dataset.customer` AS (
  SELECT 'many other columns here' other, 1 customer_id, 'abc' customer_name 
)
SELECT c.* REPLACE(-1 AS customer_id, 'No Customer' AS customer_name)
FROM (SELECT 1) x 
LEFT JOIN `project.dataset.customer` c
ON 1 = 0 
UNION ALL
SELECT * FROM `project.dataset.customer`  

as you can see, above have no dependency on columns order and always return result with original schema of customer table