JOIN

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;

Familiarize yourself with the schema

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;

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.

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.

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;

This results in the following query:

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:

Query Output

flight_idboarding_timestatus
LBL98111:15 AM30 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_idboarding_timestatus
LBL98111:15 AM30 MIN (DELAY)

...and the flights table.

flight_idboarding_timestatus
LBL98111:15 AM30 MIN (DELAY)

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

Activate AutoScroll

You haven't unlocked all of BUS 104 yet...

Unlock our 42 concept breakdowns & 10 practice problems with guided solution walkthroughs!