More Join Types

Before we look at other join types, let's take note of something interesting about inner joins.

We know we have 2 users with names manuel_torp and ryley.denesik98…

SELECT
  username,
  email
FROM tweeters
WHERE username IN (
  'manuel_torp',
  'ryley.denesik98'
);

But look what happens when we JOIN their information with their tweets from the tweets table…

SELECT
  tweeters.email,
  tweeters.username,
  tweets.body,
  tweets.created_at
FROM tweeters
INNER JOIN tweets
  ON tweeters.id = tweets.user_id
WHERE tweeters.username IN (
  'manuel_torp',
  'ryley.denesik98'
);

Notice that the user ryley.denesik98 doesn't appear in any of the rows. Why not? This is because ryley.denesik98 doesn't have any tweets.

The INNER JOIN will only keep rows in either table that have matching values for the join condition. Rows without any matching rows from the other table are removed. Sometimes that is exactly what you want. For example, if you are displaying a list of tweets and you don't need to show anything for users who haven't tweeted. However, you might want to make a page that shows some user information (maybe their email or profile picture) plus their tweets, and you might want to keep user information even if they haven't tweeted.

LEFT, RIGHT JOIN

If we want rows for users even if they have no tweets, we can use a LEFT JOIN in this situation.

SELECT
  tweeters.email,
  tweeters.username,
  tweets.body,
  tweets.created_at
FROM tweeters
LEFT JOIN tweets
  ON tweeters.id = tweets.user_id
WHERE tweeters.username IN (
  'manuel_torp',
  'ryley.denesik98'
);

What's different now? The user ryley.denesik98 is there, and the tweet information is all NULL because they have no tweets. It's called a LEFT JOIN because the rows from the table on the "left side of the join" are preserved even if they have no matches for the join condition, and NULLs are filled instead. The "left" and "right" are determined by the order they appear in the query, i.e. FROM table_a JOIN table_b ON … means table_a is on the left and table_b is on the right.

This is often depicted with venn diagrams, like so:

In these diagrams, the overlapping region in the middle represents the set of rows which do have matches for the join condition. The filled-in regions are the ones kept in the result, and the blank ones are filtered out.

Conceptually, the RIGHT JOIN is exactly the same as the LEFT JOIN, except that only rows from the right-hand table are kept if they have no match for the join condition.

Notice here that I switch the order of tweeters and tweets.

SELECT
  tweeters.email,
  tweeters.username,
  tweets.body,
  tweets.created_at
FROM tweets
RIGHT JOIN tweeters
  ON tweeters.id = tweets.user_id
WHERE tweeters.username IN (
  'manuel_torp',
  'ryley.denesik98'
);

FULL JOIN

Once you understand the LEFT and RIGHT JOIN, the FULL JOIN, a.k.a FULL OUTER JOIN (same thing), is easy to understand. A FULL JOIN will match up rows which have a match, keep the rows from both tables if they have no match, and fill in NULL for either side when needed.