SQL joins allow our relational database management systems to be, well, relational.

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:

• What is a join?
• Setting up your database
• CROSS JOIN
• Setting up our example data (directors and movies)
• FULL OUTER JOIN
• INNER JOIN
• LEFT JOIN / RIGHT JOIN
• Filtering using LEFT JOIN
• Multiple joins
• Joins with extra conditions
• The reality about writing queries with joins

(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   | johnsmith@gmail.com |  25
  2 | Jane Doe     | janedoe@Gmail.com   |  28
  3 | Xavier Wills | xavier@wills.io     |  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   | johnsmith@gmail.com |  25 |  1 | 1234 Main Street  | Oklahoma City | OK    |       1
  2 | Jane Doe     | janedoe@Gmail.com   |  28 |  2 | 4444 Broadway Ave | Oklahoma City | OK    |       2
  3 | Xavier Wills | xavier@wills.io     |  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 city column.

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—the interactive PostgreSQL command line program. If you have another database client that you enjoy working with that’s fine too.

To begin, let’s create our database. With PostgreSQL already installed, we can run the command createdb <database-name> at our terminal to create a new database. I called mine fcc:

$ createdb fcc

Next let’s start the interactive console by using the command psql and connect to the database we just made using \c <database-name>:

$ 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 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!

#sql #database #web-development