Database Reference
In-Depth Information
FROM (
SELECT 17 as vInt
)
) as t1
JOIN (SELECT "17" as vString) as t2
ON t1.vString = t2.vString
Note the only change necessary is to wrap one of the tables in a subquery
that applies the necessary type coercion.
Many of the cases where subqueries were required to work around quirks in
the BigQuery version of SQL have been fixed. As mentioned in the previous
section,
ORDER BY
columns used to be required to be in the
SELECT
list;
this often required a subquery if the field you wanted to sort by wasn't a
field you wanted to have in your output. Many of these limitations are being
quietly eliminated, and if you try again, one day they might just work. By the
time you read this topic, the
JOIN
coercion example in this section may no
longer require a subquery.
IN and NOT IN Clauses
A common use of a subquery is in an
IN
or a
NOT IN
clause. The following
query returns all the words in a play that match the title of a different
Shakespeare play:
SELECT corpus, word FROM
[publicdata:samples.shakespeare]
WHERE word <> corpus AND word IN (
SELECT corpus
FROM [publicdata:samples.shakespeare]
GROUP BY corpus)
Queries that use the
IN
keyword together with a subquery are called
semi-joins, and queries that use
NOT IN
however are called anti-joins.
Both of these types of queries are discussed more in the “Semi-JOIN and
Anti-JOIN” section later in this chapter.
Nested Computation
Often you'll want to run a subquery because that is the best way to compute
your data. For instance, you need to do a
GROUP BY
before you do a
JOIN
,
or you want to coerce fields in one table to look like another one. This query,
for example, computes the largest update size for the top 100 most edited
