This lesson offers an optional exploration into something called a join statement. Join statements allow us to associate objects in a slightly different manner than seen in the previous lesson: By using specialized SQL statements.

You are not required to use join statements, but it is important to understand how data from multiple tables can be gathered in a single query statement using matching keys on related tables.

Demonstration

Database Relationship Without Join Statements

When you have a one-to-many relationship - say, a sponsor has many athletes - it's straightforward to select all of a sponsor's athletes. Here's some sample data:

If we want to get all of the athletes that Nike sponsors, we can write the following SQL statement:

SELECT * FROM athletes WHERE sponsor_id = 1;

Database Relationships With Join Statements

There's another way to get this data: using the SQL join statement. A join statement is different from a join table but performs a similar job: Joining information from multiple tables. Here's how it works:

We JOIN the athletes table to the sponsors table ON the key that is the same for and links both tables: The sponsor_id (which is athletes.sponsor_id on the athletes table and sponsors.id on the sponsors table).

We limit the JOIN to only those records WHERE the sponsors.id is 1.

So, in plain English, we're joining the athletes and sponsors tables together wherever the sponsor_id column of the athletes table is equal to the id column of the sponsors table. Then, we're narrowing to just the records where the sponsors table has an ID of 1.

The join statement is used to simplify queries across multiple tables. Again, it has nothing inherently to do with join tables. But, as we're about to see, you can use it to join together tables that include a join table.

Join Statements with Join Tables

Let's move to a more complex example. The spotlight on some athletes has grown, and now some athletes have many sponsors, and an organization can continue to sponsor many athletes. The data now looks like this:

We're taking all of the sponsors with ID 1, then joining all of the endorsements to that sponsor, and then joining all of the athletes to those endorsements, and finally selecting everything from those athletes.

Let's look at another join statement using an example we have seen before.

Sometimes it is easiest to look at join statements backwards. We want our results to be centered around whatever friend has an id = 1. So, we ask the database to look at the friends table and the cities_friends table together. Then, to return any entries where friends.id is the same as cities_friends.friend_id, or in our case, where the friend_id value in the cities_friends table is 1.

Just by looking at the cities_friends table, we can see that there are two entries where friend_id is 1. Then we ask the database to use those two entries, look at whatever their city_id is (in this case 1 and 2), then find those same numbers in the cities table (Chicago and New York) and return all (*) information about the cities.

Think of it like slowly filtering down results of our queries. We start with a lot of database entries, we filter the join table first for the the correct friend, then we look for the IDs of the other table that match with the result of our first filtering. Then we ask the database to return everything about the entries on the other table that matched.