The ability to JOIN (even if it is limited to 8Mb) is pretty useful for a couple of specific use cases we came up against recently.

It can reduce the disk (& therefore more importantly, cache) space requirements of the materialised views you otherwise have to maintain with a product like Cassandra (which is still ACE! IMHO).

https://developers.google.com/bigquery/docs/query-reference#...