JOIN explained
Imagine you're in the JFK airport flying from New York to Chicago. You printed out your boarding pass, which contains the following information:
You then hear on the loudspeaker that your flight has been delayed. You missed though for how long the delay would last, so you walk over to the terminal screen.
In order to figure out how long your flight has been delayed for, you need to find your flight ID. Eventually, you find it!
After finding it, you are now able to recognize that your flight will be delayed 30 minutes.
By matching the flight ID on our boarding pass to the flight ID on the list of flights within the terminal screen, we performed a JOIN statement.
We took the information from one "table", our boarding pass, and joined it to the corresponding information in another "table", the flights on the terminal screen.
JOIN statements are used to link together tables that contain relevant information to each other.
How to code a JOIN
Here's the general template for JOIN statements:
JOIN database_name.join_table_name ON from_table_name.key = join_table_name.key;
Let's utilize the above situation with the airport and figure out how we can code one.
When we entered the airport we had the boarding pass on hand. We had printed it out before, therefore we had that information on us throughout our time in the airport.
So, we'll place the boarding_passes table as our from_table_name.
JOIN database_name.join_table_name ON boarding_passes.key = join_table_name.key;
When we heard about our flight delay on the announcements, we had to join the information on the terminal screens (listing out the flights) to our boarding pass to learn how long our flight was delayed for.
Therefore, the join_table_name is the flights table, since we joined that information to our boarding pass.
JOIN database_name.flights ON boarding_passes.key = flights.key;
Now, how do we identify the database_name and the keys?
JOIN database_name.flights ON boarding_passes.key = terminals.key;
Schemas
Schemas enable a viewer to visualize the tables, fields, and how those tables and fields are connected within a database.
A schema is essentially a blueprint to a database.
Below is the schema of the database in airport situation.
The schema above shows us we've got a database with two tables, boarding_passes and flights, and a some fields in them. They seem to be connected as well. We'll discuss that connection in a second.
As you can see, the database's name is airport.
So, let's place this in our JOIN statement as database_name.
JOIN airport.flights ON boarding_passes.key = flights.key;
Now for the keys.
JOIN airport.flights ON boarding_passes.key = flights.key;
Before proceeding, let's take a step back and first understand primary keys (PK) and foreign keys (FK). This will help us understand how the tables are connected in the schema.
Primary Keys
A primary key (PK) uniquely identifies each record in a table.
Confused? Consider this: every U.S. citizen must have a unique Social Security Number to identify themselves and no one else. Therefore, a Social Security Number to a U.S. citizen is the equivalent of a primary key to a record.
If some U.S. citizens had the same Social Security Number, we wouldn't be able to uniquely identify them. This would be a huge issue.
In the same way, if we can't unique identify each record in a table, then we'd run into problems. This is why every record must have a primary key.
In relation to our airport database, the reason that boarding_passes.id is the primary key of the boarding_passes table...
...is because each boarding pass must be able to be uniquely identified. If they aren't, then multiple people could use the same boarding pass.
The reason that flights.flight_id is the primary key of the flights table...
...is because each flight must be able to be uniquely identified. If they aren't, then no one would know which flight a given flight ID is actually referring to, and might go to the wrong boarding gate!
Foreign Keys
A foreign key (FK) links to the primary key of a different, related record in another table.
While every U.S. citizen has a unique Social Security Number, they do not have a unique home address. There could be other potential family members or roommates living at the same address. Therefore, a home address to a U.S. citizen is the equivalent of a foreign key to a record.
Although home addresses are unique among houses, they are not unique among citizens.
If some U.S. citizens had the same home address, it wouldn't be an issue. We'd still be able to identify them uniquely with their Social Security Number and identify what specific house they live in with their home address.
Every record does not need to have a foreign key. They only need one if they're related to another record in a different table.
In our airport database, the reason that boarding_passes.flight_id is a foreign key in the boarding_passes table...
...is because multiple boarding passes can correspond to the same flight. Even though the flight itself is unique, it's not unique in regards to the boarding passes.
Locating keys in the schema
Alright, back to coding our JOIN statement. How do we know what to place in keys?
JOIN airport.flights ON boarding_passes.key = flights.key;
We must look at the schema, specifically what two fields the line connecting the tables points to.
By doing this, we can see that the two tables are connected through the flight_id foreign key in boarding_passes and the flight_id primary key in flights.
Therefore, we'll place these two keys in the JOIN statement.
JOIN airport.flights ON boarding_passes.flight_id = flights.flight_id;
And there you have it! We've successfully joined together two tables with SQL!
In order to see the results of this query, we must complete our SELECT and FROM statements.
Finishing the query
First, let's code our SELECT statement. What columns do we want to see in our output?
Well, in order to know when we should board our flight considering the delayed time, we need to see our flight ID from our boarding pass...
...the original boarding time on our boarding pass...
...and the delay status for the flight on the terminal screen.
So, we'll place these 3 fields into our SELECT statement.
SELECT boarding_passes.flight_id, boarding_time, status
FROM database_name.from_table_name
JOIN airport.flights ON boarding_passes.flight_id = flights.flight_id;
Because the "flight_id" field name is not unique within the airport database. Notice how the boarding_passes table and the flights table have fields called flight_id?
If we don't declare that we mean the boarding_passes table's flight_id, then SQL will get confused and return an error.
This concept is discussed further in Step 7: Troubleshooting errors.
Next, our FROM statement. We've already recognized that our database_name is airport.
SELECT terminals.flight_id, boarding_time, status
FROM airport.from_table_name
JOIN airport.flights ON boarding_passes.flight_id = flights.flight_id;
And our from_table_name, as stated earlier, is boarding_passes, since we brought our physical boarding pass in the airport.
SELECT terminals.flight_id, boarding_time, status
FROM airport.boarding_passes
JOIN airport.flights ON boarding_passes.flight_id = flights.flight_id;
Now, when we execute this query, we get the following results:
| flight_id | boarding_time | status |
| LBL981 | 11:15 AM | 30 MIN (DELAY) |
As expected, this corresponds with what we saw in our visuals. Our flight ID of LBL981 with an original boarding time of 11:15 AM...
...has been delayed for 30 minutes!
Take note of how we have information from the boarding_passes table...
| flight_id | boarding_time | status |
| LBL981 | 11:15 AM | 30 MIN (DELAY) |
...and the flights table.
| flight_id | boarding_time | status |
| LBL981 | 11:15 AM | 30 MIN (DELAY) |
Here in lies the power of JOINs. We can see information from two tables in one query.
Now that we understand how to join tables in SQL, let's move forward by learning how to refine our results and only show records that fit to certain conditions with WHERE!
SQL Cram Kit
Want to unlock 
content? Get your SQL Cram Kit now!
 | Apply | PRACTICE SQL EXAM (PREVIEW ONLY) |
 | Tools | SQL Nerd Notes (PREVIEW ONLY) |
 | Concept | What is SQL? |
| Concept | SELECT & FROM |
| Concept | JOIN |
| Concept | WHERE (PREVIEW ONLY) |
| Concept | GROUP BY (PREVIEW ONLY) |
| Concept | HAVING (PREVIEW ONLY) |
| Concept | ORDER BY (PREVIEW ONLY) |
| Concept | Troubleshooting errors (PREVIEW ONLY) |
