OUTER JOIN

But how do we get the full list of candidates along with the number of contributors for each, including those candidates who have no contributors in our data set? SQL provides the “OUTER JOIN” syntax for doing just that. Outer joins are typically defined by the table from which we want to include non-matching rows, and we do so by referring to where that table appears in the JOIN statement.

• A LEFT OUTER JOIN includes all rows from the table on the left side of the statement and only matching rows from the table on the right side of the statement.
• A RIGHT OUTER JOIN includes all rows from the table on the right side of the statement and only matching rows from the left side of the statement.
• A FULL OUTER JOIN includes all rows from both tables.

Currently, SQLite only supports LEFT OUTER JOIN from the list above, but some other database management systems support the other two types as well.

Note

It’s easy to perform a RIGHT OUTER JOIN in SQLite by simply reversing the order of tables and using a LEFT OUTER JOIN. It’s also possible to do a FULL OUTER JOIN by combining LEFT OUTER JOINs using the UNION keyword.

This all probably makes more sense in an example. Let’s rewrite the grouping query from earlier to include all candidates:

SELECT candidates.id,
       candidates.last_name,
       count(contributors.id)
FROM candidates
LEFT OUTER JOIN contributors ON candidates.id = contributors.candidate_id
GROUP BY candidates.id, candidates.last_name;

(Aliases would work here as well, but I’ve used the full table names to make the relationships clearer.)

Notice the JOIN statement: candidates LEFT OUTER JOIN contributors. Because candidates is on the left side of that statement, the result set will include all of the candidate rows, even those for which there are no matching contributors:

Much better.

A Gentle Introduction to SQL Using SQLite by Troy Thibodeaux is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.