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.
Search WWH ::




Custom Search