Database Reference
In-Depth Information
This is a semi-
JOIN
. An anti-
JOIN
is the opposite when you filter based
on not having membership in a list. The anti-
JOIN
version of the previous
query returns long words from all works except for the five largest:
SELECT word
FROM [publicdata:samples.shakespeare]
WHERE corpus
NOT
IN (
SELECT corpus FROM (
SELECT corpus, SUM(word_count) total
FROM [publicdata:samples.shakespeare]
GROUP BY corpus ORDER BY total DESC LIMIT 5))
AND LENGTH(word) > 14
GROUP BY word
You may wonder why this is considered a
JOIN
. It turns out that it is
possible to rewrite the semi-
JOIN
form as an
INNER JOIN
query and the
anti-
JOIN
as a
LEFT OUTER JOIN
. However, the query is a lot more
cumbersome when it is written with the explicit
JOIN
syntax. The
IN
operator makes the query easier to follow and modify.