JOIN | BUS 104 – SQL Exam

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 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 is the primary key...

The primary key of boarding_passes, id, identified 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 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:

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

LBL98111:15 AM30 MIN

...and the terminals table.

LBL98111:15 AM30 MIN

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!

ToolsSQL Nerd Notes (PREVIEW ONLY)
ConceptWhat is SQL?
ConceptTroubleshooting errors (PREVIEW ONLY)

Leave a Comment