JOIN

So far, we have only queried single tables. Things get more interesting when there are multiple related tables. Why would tables have relationships? Let's look at a use case.

Suppose we are working on a social media website called "Tweeter", where people can write short excerpts of texts called "tweets". The users are in a table called tweeters. The tweets are in tweets.

The tweets are always written by a user (a tweeter). There is a relationship between the tables because each tweet is owned by exactly one user. That information is stored in the user_id column. This is equal to the id of the user who wrote the tweet. In other words, tweets.user_id references tweeters.id. We call this relationship a foreign key. So, the user_id column is a foreign key to tweeters.id.

Visually, engineers often like to depict this relationship with something like this:

This shows the actual relationship very clearly. The forked tip at the user_id column is meant to show that a user can write many tweets, i.e. the relationship is "one to many".

Getting Tweets From a Tweeter

Suppose we need to get the tweets for username judy.blick60. It's no problem to filter the tweeters by WHERE username = 'judy.blick60'. However, the tweets table doesn't have username, but the tweeter ID. We don't know what ID we would filter on. So, we can 2 do separate queries, and use the ID we retrieve from the first query.

SELECT id
FROM tweeters
WHERE username = 'judy.blick60';

We see that her ID is 11, then we put that into our next query…

SELECT body
FROM tweets
WHERE user_id = 11;

This works, but we had to run 2 queries to get our data. This isn't always the most efficient. There is better way.

Performing a JOIN

SQL gives us the ability to get data from multiple related tables at once. How? The idea is to combine 2 (or more) tables side-by-side using a JOIN clause. Here is what it looks like in code:

SELECT *
FROM tweeters
JOIN tweets
  ON tweeters.id = tweets.user_id
ORDER BY username;

Notice that the result's columns are a fusion of both tables' columns. Here's what it look like visually.

The rows from tweeters try to match up with any rows from tweets. When the rows from the tables match, they combine to form 1 combined row. Those combined rows form the output table. However, notice that 1 row on the left matches multiple rows on the right. What happens in this case?

Whenever a single row matches multiple other rows, its contents are duplicated in the result. So, in the result, a user having 3 tweets will have their user information (email, username, etc.) appear 3 times.

Why is that useful? Well now these new combined rows represent tweets and they have the tweet information and the user information. We can therefore select the tweet information and filter on user information, like username, in a single query!

Let's have a look at the code to make this possible.

You specify the left-hand table in FROM, and then the right-hand table in JOIN. You also need to specify how the rows match up. How do we know which rows in tweets match which rows in tweeters? We know from the ON clause after the JOIN. Rows will match when the expression in the ON clause is true. In our case, when the user_id column in tweets equals the id column in tweeters, i.e. tweeters.id = tweets.user_id.

Notice the notation table_name.column_name, which is new. This is just a more specific way to refer to a column. It wasn't necessary when only one table was involved, but it is now. Watch what happens if we refer to the id column without being specific…

SELECT id
FROM tweeters
JOIN tweets
  ON tweeters.id = tweets.user_id;

error: column reference "id" is ambiguous

Just selecting id is ambiguous because both tables have an id column. Do we mean the tweeter ID or the tweet ID? We can prefix our column names with their respective table names to disambiguate. Let's also add the WHERE clause to filter to the tweets we want.

SELECT
  tweets.id AS tweet_id,
  tweets.body,
  tweets.created_at
FROM tweeters
JOIN tweets
  ON tweeters.id = tweets.user_id
WHERE tweeters.email = 'judy14@yahoo.com';

Notice how we can safely request tweets.id without error. Furthermore, we alias it to tweet_id instead. Otherwise, the name in the result would just be id.

Also notice that I use tweeters.email and tweets.body instead of just email and body. Because these columns are unambiguous (only occur in one of the tables involved), prefixing with the table name is optional. However, people reading your query may not know that. It is better to be specific and add the table name unless it is very obvious which table a column belongs to.

Table Aliases

Much like aliases can be assigned to columns in the SELECT clause, we can assign aliases to tables in the FROM and JOIN clauses.

SELECT
  t.id AS tweet_id,
  t.body,
  t.created_at
FROM tweeters u
JOIN tweets t
  ON u.id = t.user_id
WHERE u.email = 'judy14@yahoo.com';

When table aliases are used, the column references need to match the table aliases, not the original table name.

Table aliases never end up in the result. They are only used to disambiguate between columns or between multiple instances of the same table (we'll see exapmles of that later).

You also don't use AS before the alias, at least according to the SQL standard. However, Postgres lets you add AS if you want. I prefer to stick to the standard.

Other JOIN Types

This is just one type of join, called an "inner join". We could've typed INNER JOIN instead of JOIN. Inner joins are the default join type. We'll look at more join types soon. First, let's get some practice with the inner join.