Database Reference
In-Depth Information
SQL standards so thoroughly? Yes, of course. It is possible that in the
future BigQuery will introduce a new syntax for table
UNION
(perhaps a
table-valued function
TABLE_UNION()
that lets you list tables) that
doesn't look like a
JOIN
.
Here is an example of a
UNION ALL
operation in BigQuery:
SELECT COUNT(ts), COUNT(DISTINCT id)
FROM [bigquery-e2e:public.device_20140218],
[bigquery-e2e:public.device_20140219],
[bigquery-e2e:public.device_20140220]
BigQuery limits table unions to 100 tables. If you want to query over more
than this, you need to split your query up into multiple pieces. If you query
over a lot of tables, you might consider using the table functions
TABLE_DATE_RANGE
and
TABLE_QUERY
, which allow you to specify a
range of tables rather than listing them individually.
INNER JOIN
INNER JOIN
is the most common type of join; it instructs the query
engine to take the left table and match it with rows in the right table where
both tables share a set of matching column values. One row containing
all columns from both tables is generated for every pair of rows from the
left and right table that satisfy the
ON
clause. In BigQuery the
ON
clause
is restricted to testing equality between fields from the tables. More
complicated conditions must be placed in the
WHERE
clause.
For example, say you wanted to look up GitHub repositories that are also the
name of Shakespeare plays and also return the date the Shakespeare play
was written for each one. You could run this query:
SELECT shakespeare.corpus AS name,
github.repository_owner,
shakespeare.corpus_date
FROM [publicdata:samples.github_timeline] github
INNER JOIN (
SELECT corpus, max(corpus_date) AS corpus_date
FROM [publicdata:samples.shakespeare]
