Database Reference
In-Depth Information
SELECT github.repository_name AS name,
github.repository_owner,
shakespeare.corpus_date
FROM [publicdata:samples.github_timeline] github
LEFT OUTER JOIN (
SELECT corpus, max(corpus_date) as corpus_date
FROM [publicdata:samples.shakespeare]
GROUP BY corpus) shakespeare
ON shakespeare.corpus = github.repository_name
WHERE repository_language = "Python"
LIMIT 5;
Note that in this case, we've changed the name field in the
SELECT
list
to the repository name field from the GitHub table rather than the corpus
name from the Shakespeare table. In an
INNER JOIN
, these can be used
interchangeably because they will always be identical. But in an
OUTER
JOIN
, the field referenced in the
ON
clause may be null if there was no match
on that side.
CROSS JOIN
CROSS JOIN
is used relatively rarely, but it is arguably the most powerful
type of
JOIN
operation.
CROSS JOIN
says to match each row in the left
table with each row in the right table. If you don't filter the results, you end
up with N x M results, where N is the number of rows in the left table and
M is the number of rows in the right table. It is easy to see that the numbers
can get large; if you have a billion rows in the left table and a million rows in
the right, a naïve
CROSS JOIN
will generate a quadrillion rows. That is a lot
of rows, and chances are that was not what you actually intended to do.
When combined with a judicious filter—a
WHERE
clause on either the left
side or right side or both—
CROSS JOIN
s can be extremely useful to push
the boundaries of SQL. Whereas
INNER
and
OUTER JOIN
s are used to
combine tables that have columns that can be tested for simple equality,
CROSS JOIN
, however, is usually used to relate tables using a more complex
condition. For example, this query computes the Wikipedia titles that
contain the name of any Shakespeare play.
