Database Reference
In-Depth Information
GROUP BY corpus) shakespeare
ON shakespeare.corpus = github.repository_name
LIMIT 5
With an
INNER JOIN
, if there are Shakespeare plays that aren't GitHub
repositories, they won't be returned; likewise if there are GitHub
repositories that aren't Shakespeare plays, those won't be returned either.
The above statements may seem somewhat obvious, but they aren't true of
OUTER JOIN
s.
Because
INNER JOIN
is so common, it is the default behavior when you use
JOIN
without any other modifiers.
OUTER JOIN
OUTER JOIN
s are used when you want to return all the rows in a particular
table, regardless of whether they match a result in the table you're joining
against. If there is no match, then the fields for the other table will be null in
the result.
OUTER JOIN
s have three flavors:
•
LEFT OUTER JOIN
: Returns all the rows in the left table (the table
mentioned in the
FROM
clause)
•
RIGHT OUTER JOIN
: Returns all the rows in the right table (the table
mentioned after the
JOIN
keyword).
All RIGHT OUTER JOIN
s can be
rewritten as
LEFT OUTER JOIN
s by swapping the order of the tables in
the query.
•
FULL OUTER JOIN
: Returns all the rows in both the left and right
tables
To come up with an example for an
OUTER JOIN
, suppose you want to
return all GitHub repositories, regardless of whether they matched the name
of a Shakespeare play. If they do match, however, you want to return the
date of the Shakespeare play whose name they share. To construct this
query, you can use the exact same query from the
INNER JOIN
section, but
change the
INNER JOIN
to a
LEFT OUTER JOIN
. (Use
LEFT
because the
table you want to read in full is the
LEFT
table.)
