What join only returns matched records from the tables that are being joined?

Joins allow us to re-construct our separated database tables back into the relationships that power our applications.

In this article, we'll look at each of the different join types in SQL and how to use them.

Here's what we'll cover:

• $ psql
  psql (11.5)
  Type "help" for help.
  
  john=# \c fcc
  You are now connected to database "fcc" as user "john".
  fcc=#
  

  0

• $ psql
  psql (11.5)
  Type "help" for help.
  
  john=# \c fcc
  You are now connected to database "fcc" as user "john".
  fcc=#
  

  1

• $ psql
  psql (11.5)
  Type "help" for help.
  
  john=# \c fcc
  You are now connected to database "fcc" as user "john".
  fcc=#
  

  2

(Spoiler alert: we'll cover five different types—but you really only need to know two of them!)

What is a join?

A join is an operation that combines two rows together into one row.

These rows are usually from two different tables—but they don't have to be.

Before we look at how to write the join itself, let's look at what the result of a join would look like.

Let's take for example a system that stores information about users and their addresses.

The rows from the table that stores user information might look like this:

 id |     name     |        email        | age
----+--------------+---------------------+-----
  1 | John Smith   | [email protected] |  25
  2 | Jane Doe     | [email protected]   |  28
  3 | Xavier Wills | [email protected]     |  3
...
(7 rows)

And the rows from the table that stores address information might look like this:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

We could write separate queries to retrieve both the user information and the address information—but ideally we could write one query and receive all of the users and their addresses in the same result set.

This is exactly what a join lets us do!

We'll look at how to write these joins soon, but if we joined our user information to our address information we could get a result like this:

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Here we see all of our users and their addresses in one nice result set.

Besides producing a combined result set, another important use of joins is to pull extra information into our query that we can filter against.

For example, if we wanted to send some physical mail to all users who live in Oklahoma City, we could use this joined-together result set and filter based on the

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Now that we know the purpose of a joins—let's start writing some!

Setting up your database

Before we can write our queries we need to setup our database.

For these examples we'll be using PostgreSQL, but the queries and concepts shown here will easily translate to any other modern database system (like MySQL, SQL Server, etc.).

To work with our PostgreSQL database, we can use

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

To begin, let's create our database. With PostgreSQL already installed, we can run the command

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ createdb fcc

Next let's start the interactive console by using the command

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Note: I've cleaned up the

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

7 output in these examples to make it easier to read, so don't worry if the output shown here isn't exactly what you've seen in your terminal.

I encourage you to follow along with these examples and run these queries for yourself. You will learn and remember far more by working through these examples rather than just reading them.

Now onto the joins!

1A, 1B, 1C 2A, 2B, 2C 3A, 3B, 3C 3

The simplest kind of join we can do is a

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

This join takes each row from one table and joins it with each row of the other table.

If we had two lists—one containing

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

Each value from the first list is paired with each value of the second list.

Let's write this same example as a SQL query.

First let's create two very simple tables and insert some data into them:

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

Our two tables,

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

Now let's join them together with a

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

This is the simplest type of join we can do—but even in this simple example we can see the join at work: the two separate rows (one from

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

While this type of join is often discussed as a mere academic example, it does have at least one good use case: covering date ranges.

1A, 1B, 1C 2A, 2B, 2C 3A, 3B, 3C 3 with date ranges

One good use case of a

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

Say for example you were building an application that tracked daily tasks—things like brushing your teeth, eating breakfast, or showering.

If you wanted to generate a record for every task and for each day of the past week, you could use a

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

To make this date range, we can use the

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

The

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

The first parameter is the starting value. In this example we use

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

The second parameter is the current date (

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

The third parameter is the "step interval"—or how much we want to increment the value each time. Since these are daily tasks we'll use the interval of one day (

CREATE TABLE letters(
  letter TEXT
);

INSERT INTO letters(letter) VALUES ('A'), ('B'), ('C');

CREATE TABLE numbers(
  number TEXT
);

INSERT INTO numbers(number) VALUES (1), (2), (3);

Putting it all together, this generates a series of dates starting five days ago, ending today, and going one day at a time.

Finally we remove the time portion by casting the output of these values to a date using

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

The output of this query is the past five days plus today:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Going back to our tasks-per-day example, let's create a simple table to hold the tasks we want to complete and insert a few tasks:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Our

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

Now let's

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

(Since our date generation query is not an actual table we just write it as a subquery.)

From this query we return the task name and the day, and the result set looks like this:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Like we expected, we get a row for each task for every day in our date range.

The

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

Creating directors and movies

To illustrate the following join types, we'll use the example of movies and movie directors.

In this situation, a movie has one director, but a movie isn't required to have a director—imagine a new movie being announced but the choice for director hasn't yet been confirmed.

Our

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

Let's create those two tables and insert some data into them:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

We have five directors, five movies, and three of those movies have directors assigned to them. Director ID 1 has two movies, and director ID 2 has one.

SELECT * FROM letters CROSS JOIN numbers; 8

Now that we have some data to work with let's look at the

SELECT *
FROM letters
CROSS JOIN numbers;

A

SELECT *
FROM letters
CROSS JOIN numbers;

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

The first difference is that a

SELECT *
FROM letters
CROSS JOIN numbers;

A join condition specifies how the rows between the two tables are related to each other and on what criteria they should be joined together.

In our example, our

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

SELECT *
FROM letters
CROSS JOIN numbers;

Here's how we write this join between our two tables:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Notice the join condition we specified that matches the movie to its director:

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

Our result set looks like an odd Cartesian product of sorts:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

The first rows we see are ones where the movie had a director, and our join condition evaluated to true.

However, after those rows we see each of the remaining rows from each table—but with

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

Note: if you're unfamiliar with

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

8 values, in this SQL operator tutorial.

We also see another difference between the

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

SELECT generate_series( (CURRENT_DATE - INTERVAL '5 day'), CURRENT_DATE, INTERVAL '1 day' )::DATE AS day; 4

The next join type,

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

An inner join only returns rows where the join condition is true.

In our example, an inner join between our

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

The syntax is basically the same as before:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Our result shows the three movies that have a director:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Since an inner join only includes rows that match the join condition, the order of the two tables in the join don't matter.

If we reverse the order of the tables in the query we get same result:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Since we listed the

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

This is a useful property of inner joins, but it's not true for all join types—like our next type.

$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 3 / $ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 4

These next two join types use a modifier (

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Note: the

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

3 and

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

4 can also be referred to as

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

08 and

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

09.

These joins are used in queries where we want to return all of a particular table's data and, if it exists, the associated table's data as well.

If the associated data doesn't exist, we still get back all of the "primary" table's data.

It's a query for information about a particular thing and bonus information if that bonus information exists.

This will be simple to understand with an example. Let's find all movies and their directors, but we don't care if they have a director or not—it's a bonus:

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

The query follows our same pattern as before—we've just specified the join as a

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

In this example, the

SELECT *
FROM letters
CROSS JOIN numbers;

If we write the query on one line it makes this a little easier to see:

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

A left join returns all records from the "left" table.

A left join returns any rows from the "right" table that match the join condition.

Rows from the "right" table that don't match the join condition are returned as

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Looking at that result set, we can see why this type of join is useful for "all of this and, if it exists, some of that" type queries.

$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 4

The

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

In a right join, all of the rows from the "right" table are returned. The "left" table is conditionally returned based on the join condition.

Let's use the same query as above but substitute

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Our result set now returns every

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

All we've done is switch which table we're considering the "primary" one—the table we want to see all of the data from regardless of if its associated data exists.

$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 3 / $ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 4 in production applications

In a production application, I only ever use

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

I do this because, in my opinion, a

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

When I'm writing queries I like to think of starting with a "base" result set, say all movies, and then bring in (or subtract out) groups of things from that base.

Because I like to start with a base, the

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

In practice, I don't think I've ever even seen a

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

Re-writing $ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 4

If we wanted to flip our scenario above and instead return all directors and conditionally their movies, we can easily re-write the

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

All we need to do is flip the order of the tables in the query, and change

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

Note: I like to put the table that is being joined on (the "right" table—in the example above

SELECT *
FROM letters
CROSS JOIN numbers;

7) first in the join condition (

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

34)—but that's just my personal preference.

Filtering using $ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 3

There's two use cases for using a

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

The first use case we've already covered: to return all of the rows from one table and conditionally from another.

The second use case is to return rows from the first table where the data from the second table isn't present.

The scenario would look like this: find directors who don't belong to a movie.

To do this we'll start with a

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

SELECT *
FROM letters
CROSS JOIN numbers;

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

For a director that doesn't belong to a movie, the columns from the

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

In our example, director ID 3, 4, and 5 don't belong to a movie.

To filter our result set just to these rows, we can add a

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

$ createdb fcc

And there are our three movie-less directors!

It's common to use the

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

(Since we know that all the columns from the

SELECT *
FROM letters
CROSS JOIN numbers;

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

Using $ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 3 to find matches

In our previous query we found directors that didn't belong to movies.

Using our same structure, we could find directors that do belong to movies by changing our

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

$ createdb fcc

 id |     name     |        email        | age | id |      street       |     city      | state | user_id
----+--------------+---------------------+-----+----+-------------------+---------------+-------+---------
  1 | John Smith   | [email protected] |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | [email protected]   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | [email protected]     |  35 |  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

This may seem handy, but we've actually just re-implemented

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

Multiple joins

We've seen how to join two tables together, but what about multiple joins in a row?

It's actually quite simple, but to illustrate this we need a third table:

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

This table will represent tickets sold for a movie:

$ createdb fcc

The

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

 letter | number
--------+--------
 A      | 1
 A      | 2
 A      | 3
 B      | 1
 B      | 2
 B      | 3
 C      | 1
 C      | 2
 C      | 3
(9 rows)

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

We've also inserted two tickets sold for movie ID 1, and one ticket sold for movie ID 3.

Now, let's join

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

$ createdb fcc

Since these are inner joins, the order in which we write the joins doesn't matter. We could have started with

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

SELECT *
FROM letters
CROSS JOIN numbers;

SELECT *
FROM letters
CROSS JOIN numbers;

It again comes down to what you're trying to query and what makes the query the most understandable.

In our result set, we'll notice that we've further narrowed down the rows that are returned:

$ createdb fcc

This makes sense because we've added another

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

Our query essentially says: "return all directors that belong to movies that also have ticket sales."

If instead we wanted to find directors that belong to movies that may not have ticket sales yet, we could substitute our last

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ createdb fcc

We can see that

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

$ createdb fcc

This movie didn't have any ticket sales, so it was previously excluded from the result set due to the

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

I'll leave this an Exercise For The Reader™, but how would you find directors that belong to movies that don't have any ticket sales?

Join execution order

In the end, we don't really care in what order the joins are executed.

One of the key differences between SQL and other modern programming languages is that SQL is a declarative language.

This means that we specify the outcome we want, but we don't specify the execution details—those details are left up to the database query planner. We specify the joins we want and the conditions on them and the query planner handles the rest.

But, in reality, the database is not joining three tables together at the same time. Instead, it will likely join the first two tables together into one intermediary result, and then join that intermediary result set to the third table.

(Note: This is a somewhat simplified explanation.)

So, as we're working with multiple joins in queries we can just think of them as a series of joins between two tables—although one of those tables can get quite large.

Joins with extra conditions

The last topic we'll cover is a join with extra conditions.

Similar to a

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

For example, if we wanted to find movies with directors that are not named "John Smith", we could add that extra condition to our join with an

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

$ createdb fcc

We can use any operators we would put in a

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

We also get the same result from this query if we put the condition in a

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

$ createdb fcc

There are some subtle differences happening under the hood here, but for the purpose of this article the result set is the same.

(If you're unfamiliar with all of the ways you can filter a SQL query, check out the previously mentioned article here.)

The reality about writing queries with joins

In reality, I find myself only using joins in three different ways:

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

4

The first use case is records where the relationship between two tables does exist. This is fulfilled by the

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

These are situations like finding "movies that have directors" or "users with posts".

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

3

The second use case is records from one table—and if the relationship exists—records from a second table. This is fulfilled by the

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

These are situations like "movies with directors if they have one" or "users with posts if they have some."

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

3 exclusion

The third most common use case is our second use case for a

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

These are situations like "movies without directors" or "users without posts."

Two very useful join types

I don't think I've ever used a

SELECT *
FROM letters
CROSS JOIN numbers;

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

I have occasionally used a

1A, 1B, 1C
2A, 2B, 2C
3A, 3B, 3C

So, good news! There's really only two types of joins you need to understand for 99.9% of the use cases you'll come across:

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

$ psql
psql (11.5)
Type "help" for help.

john=# \c fcc
You are now connected to database "fcc" as user "john".
fcc=#

If you liked this post, you can follow me on twitter where I talk about database things and all other topics related to development.

Thanks for reading!

John

P.S. an extra tip for reading to the end: most database systems will let you just write

 id |      street       |     city      | state | user_id
----+-------------------+---------------+-------+---------
  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | 5678 Party Ln     | Tulsa         | OK    |       3
(3 rows)

SELECT generate_series(
  (CURRENT_DATE - INTERVAL '5 day'),
  CURRENT_DATE,
  INTERVAL '1 day'
)::DATE AS day;

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

ADVERTISEMENT

John Mosesman

A simple web developer who likes helping others learn how to program.

If you read this far, tweet to the author to show them you care. Tweet a thanks

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

What only returns matched records from the tables that are being joined?

Which type of join is used to find not matched data from table?

Which join uses records whether or not there are matching values in both tables?

How to get matching data from two tables in SQL?