JOIN | BUS 104 – SQL Exam

Still feeling nervous for your exam? Check out the SQL Cram Kit for practice questions that emulate the difficulty that you'll face on your exam. Each question includes a thorough step-by-step explanation where I walk you through the entire problem solving process!

SQL Cram Kit logo

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:

Boarding pass with flight ID of "LBL981"

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.

Terminal screens in airport with travelers walking around

In order to figure out how long your flight has been delayed for, you need to find your flight ID. Eventually, you find it!

Your boarding pass held up to the terminal screen, identifying that flight LBL981 is delayed for 30 minutes

After finding it, you are now able to recognize that your flight will be delayed 30 minutes.

The terminal screen with the delayed time for LBL981 identified

By matching the flight ID on our boarding pass to the flight ID on 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 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 to our boarding pass to learn how long our flight was delayed for.

Therefore, the join_table_name is the terminals table, since we joined that information to our boarding pass.

JOIN database_name.terminals ON boarding_passes.key = terminals.key;

Now, how do we identify the database_name and the keys?

JOIN database_name.terminals 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 airport schema, with a boarding_passes and terminals table

The schema above shows us we've got a database with two tables, boarding_passes and terminals, 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.

Airport schema with title identified

So, let's place this in our JOIN statement as database_name.

JOIN airport.terminals ON boarding_passes.key = terminals.key;

Now for the keys.

JOIN airport.terminals ON boarding_passes.key = terminals.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 database, 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...

The primary key of boarding_passes, id, identified

...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.

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...

The foreign key of boarding_passes, flight_id, identified

...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.terminals ON boarding_passes.key = terminals.key;

We must look at the schema, specifically what two fields the line connecting the tables points to.

The keys for the boarding_passes and terminals tables identified in schema

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 terminals.

Therefore, we'll place these two keys in the JOIN statement.

JOIN airport.terminals ON boarding_passes.flight_id = terminals.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 flight_Id field in the boarding_passes table identified

...the original boarding time on our boarding pass...

The boarding_time field in the boarding_passes table identified

...and the delay status on the terminal screen.

The status field on the terminals table identified

So, we'll place these fields into our SELECT statement.

SELECT boarding_passes.flight_id, boarding_time, status
FROM database_name.from_table_name
JOIN airport.terminals ON boarding_passes.flight_id = terminals.flight_id;

Because the "flight_id" field name is not unique within the airport database. Notice how the boarding_passes table and the terminals table have fields called flight_id?

Both flight_id fields in the airport database identified

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.terminals ON boarding_passes.flight_id = terminals.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.terminals ON boarding_passes.flight_id = terminals.flight_id;

Now, when we execute this query, we get the following results:

flight_idboarding_timedelay_in_mins
LBL98111:15 AM30 MIN

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...

Our boarding pass with flight ID and boarding time identified

...has been delayed for 30 minutes!

The terminal screen with the delayed time for LBL981 identified

Take note of how we have information from the boarding_passes table...

flight_idboarding_timedelay_in_mins
LBL98111:15 AM30 MIN

...and the terminals table.

flight_idboarding_timedelay_in_mins
LBL98111:15 AM30 MIN

Here in lies the power of JOINs. We can see information from two tables in one query.

SQL Follow-along Guide

It's no secret you retain info better when you write it down. That's why I created the SQL Follow-along Guide for you!

As you come upon key concepts highlighted in yellow, like this sentence here, you can fill-in-the-blanks on your Follow-along Guide so that you remember all the important stuff for later.

You can obtain the SQL Follow-along Guide for FREE by entering your email below!

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!

Concepts

Each exam concept broken down with relatable situations to your life!

ToolsSQL Follow-along Guide (FREE)
LessonWhat is SQL?
LessonSELECT & FROM
LessonJOIN
LessonWHERE
LessonGROUP BY
LessonHAVING
LessonORDER BY
LessonTroubleshooting errors

Practice Problems

Include step-by-step explanations through each part of the problem!

SQL Cram Kit Logo

"The SQL Cram Kit was the best thing I could’ve done before my exam. It takes practice and more practice to be good at coding and that is what I needed."

Andrew Green
October 29, 2020

Rating: 5 out of 5.

Leave a Comment