SQL joins allow our relational database management systems to be, well, relational. Show
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:
(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:
And the rows from the table that stores address information might look like this:
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:
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 Now that we know the purpose of a joins—let's start writing some! Setting up your databaseBefore 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 To begin, let's create our database. With PostgreSQL already
installed, we can run the command
Next let's start the interactive console by using the command
Note: I've cleaned up the 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! The simplest kind of join we can do is a 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
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:
Our two tables, Now let's join them together with a
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 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. CROSS JOIN with date rangesOne good use case of a 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 To make this date range, we can use the
The The first parameter is the starting value.
In this example we use The second parameter is the current date ( 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 ( 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 The output of this query is the past five days plus today:
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:
Our Now let's
(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:
Like we expected, we get a row for each task for every day in our date range. The Creating directors and moviesTo 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 Let's create those two tables and insert some data into them:
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. FULL OUTER JOINNow that we have some data to work with let's look at the A The first difference is that a
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 Here's how we write this join between our two tables:
Notice the join condition we specified that matches the movie to its director: Our result set looks like an odd Cartesian product of sorts:
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 Note: if
you're unfamiliar with We also see another difference between the
|