In MySQL I can do SELECT (SELECT COUNT(*) FROM table1) - (SELECT COUNT(*) FROM table2)
to get the difference in counts between two tables. When I try this in BigQuery, I get: Subselect not allowed in SELECT clause
. How do I run a query like this in BigQuery?
Though there is no MINUS function in BigQuery, you can use a LEFT OUTER JOIN as an alternative. it seems to me that 'IS NULL' was correct? you add matching B parts to A, so where there is no match, B vals will be NULL and that are the records that would also result from the MINUS..?
Hence NaN is interpreted by BigQuery as null since it is considered as an invalid value. A value can be a string in double quotes, or a number, or true or false or null, or an object or an array.
Safe Offset - Avoid “Out of Bounds” Errorwhich is results in NULL values for not existing elements.
COALESCE is the equivalent for NVL function in Bigquery.
2019 update:
The original question syntax is now supported with #standardSQL
SELECT (SELECT COUNT(*) c FROM `publicdata.samples.natality`)
- (SELECT COUNT(*) c FROM `publicdata.samples.shakespeare`)
As subselects are not supported inside the SELECT clause, I would use a CROSS JOIN for this specific query:
SELECT a.c - b.c
FROM
(SELECT COUNT(*) c FROM [publicdata:samples.natality]) a
CROSS JOIN
(SELECT COUNT(*) c FROM [publicdata:samples.shakespeare]) b
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With