Welcome to the future. This timeline is amazing!
The global hyperloop network is finally complete. The bioengineered glow-in-the-dark Redwood
Forest in the Sahara is a vacation paradise. And best of all, we have colonies throughout
the Solar System. One thing that might surprise you about the future is that everyone
still uses SQL. I didn’t see that coming. So to prepare you for the future, we now explore
an essential part of SQL: Joins. The database we will be using to learn about
joins has 5 Tables: martian, base, visitor, inventory, and supply. The Martian table is a list of all people living on the Red Planet.
After all, if you are living on Mars, you are no longer an Earthling - you’re a Martian!
The “base” table holds information on all the different habitats on Planet #4.
In addition to serving as a stopover for people visiting the outer planets, Mars is now a
tourist destination. Every visitor to Mars is tracked in the “visitor”
table. The “inventory” table keeps track of the
supplies at each base. And the “supply” table shows what is available
at the central Martian distribution center. Let’s take a closer look at the “Martian”
and “Base” tables. Here are the columns in the “Martian”
table… The “super_id” is the “ID” of the
Martian they report to. And here are the columns in the “Base”
table. These two tables are connected, because each
“Martian” has a “base_id” identifying where they live and work.
Suppose you need to generate a report listing the full names of all Martians and the name
of their home base. How would you do it?
The Martian name is in the “Martian” table, while the “base” name is in the “Base”
table. The solution is to JOIN these two tables together
by the “base_id.” To begin, look at the DATA in the Martian
table… and the Base table. Let us visualize how to join these tables
together by the “base_id.” Take the first row from the “Martian”
table... Find the row in the Base table with the matching
base_id… And then join them together to make a new,
larger row. You continue doing this row… by row… by
row… This brings us to John Carter.
He does not have a base_id, so what do we do?
Do we include him in the join? Or leave him out?
This is a very important question. The answer depends on the TYPE of join you do. For now, we will leave John Carter out.
The result is 12 rows. Each row is a combination of data from both tables. Having joined these two tables, you can select
data from these rows like an ordinary SELECT query. By the way, notice that there is currently no Martian at the Olympus Mons Spa & Casino.
This is because the staff is en route from the Moon and will not arrive for a few more weeks. Here’s how to write a join using SQL.
Begin with SELECT * so we can see all columns. This will help in understanding the join.
Next, specify the two tables. FROM martian … INNER JOIN base.
We will call “martian” the left table, and “base” the right table.
The “JOIN” keyword indicates we will be joining these two tables.
We’ll explain the INNER keyword in just a moment. Next, specify HOW to connect two rows from these tables with an ON clause.
We only want to connect a martian row with a base row if they have the same base_id.
In the “ON” clause, you have to specify both the table name... and column name.
This makes it clear which table each column is from.
Now execute… The result contains all the columns from the
Martian table… And all the columns from the Base table.
The rows are matched by base_id. John Carter and the Olympus Mons Spa & Casino
do not appear in the result. This is because neither one had a match in
the other table. We can now update this query to return only
those columns we need for our report. First name… last name… and base name…
Execute. Report complete. When joining the tables, we encountered two
problematic rows. John Carter is in the Martian table, but his base is unknown. And the Olympus
Mons Spa & Casino currently has no Martians. So how do you tell SQL whether or not to include
rows from a table that do not have a match in the other? Hmm?? We have two tables, and
we have two choices per table. Include rows without a match or exclude them. This means
there are 4 total options. Spoiler Alert: SQL has a JOIN for each of the four options.
Let’s visit them now. We return to the “Martian” and “Base”
tables. We will call the “Martian” table the left
table, and “Base” the right table. Each table has a row with no match in the
other table. Here is the syntax for joining these two tables:
Write SELECT followed by the columns you want FROM martian...
blank... JOIN base ON martian.base_id = base.base_id
The ON clause specifies HOW rows from the two tables will be connected.
Because this is a SELECT query, you can also have WHERE and ORDER clauses.
All that remains is to fill in the blank and specify what kind of JOIN to perform. We will learn about four types of joins:
INNER.. LEFT .. RIGHT .. and FULL. An INNER JOIN will only return connected rows when there is a matching base_id in both tables. A LEFT JOIN will return EVERY row from the
left table, even if there is no matching row in the right table.
If a Martian row has no match in the Base table, it will return nulls for ALL the columns
in the right table. A RIGHT JOIN will return EVERY row from the
right table, even when there isn’t a match in the left table.
When a Base row does not have a matching Martian row, null values will be used for ALL columns
in the left table. And finally there is the FULL JOIN.
This is sometimes called a FULL OUTER JOIN. The FULL JOIN is a combination of the LEFT JOIN
and RIGHT JOIN. A full join returns every row from the left
table and every row from the right table. When the base_ids match, the rows are connected.
But when there isn’t a match, the row is still included in the JOIN with nulls for
the columns from the other table. A common way to visualize the difference between
these 4 kinds of joins is with Venn Diagrams. The Inner Join… Left Join… Right Join…
and Full Join… The inner join diagram highlights how only
rows with matching columns are connected. The left join diagram shows that all rows
in the left table are returned, even those without a match on the right.
The right join is the mirror image of the left join.
And the full join shows that all rows from both tables are returned.
When two rows match, they are connected. Otherwise, nulls will fill in the gaps. I just received a Dear John letter. It’s
especially sad because my name isn’t even John! It reads, “Dear John. Not every database
supports the four joins you discussed. For example, MySql and SQLite do not support FULL
JOINs. Sincerely, R. E. Joinder” I will not be deterred. We are going to see examples
of these four kinds of joins using Postgres. Maybe the other databases will… join us? Before seeing examples for INNER, LEFT, RIGHT, and FULL joins I would like to revisit our
first query. SELECT *
FROM martian INNER JOIN base
ON martian.base_id = base.base_id Execute… Notice how the output has TWO columns named base_id. The first one is from the “martian” table, and the second one is from the “base”
table. What if instead of selecting all columns from
both tables, we only select the martian_id, base_id, and base_name. Execute. We get an error. This is because it is ambiguous which base_id column we want. Is it the base_id from the martian table or base table? To avoid ambiguity, you specify the table AND column name in the SELECT clause.
We will select the “base_id” from the “base” table...
Execute… Success... Repeatedly typing the table name can make
queries a bit word-y, and hands a bit sore-y We can fix this by creating an “alias”
for each table name To do this, use the “AS” keyword.
If we write “FROM martian AS m”, then ‘m’ is now an alias for the table name
“martian” And let us give the “base” table an alias
of “b” Now everywhere in our query we can use “m”
instead of the table name “martian”, and use “b” instead of the table name “base”.
The “ON” clause becomes nicer and shorter: And the SELECT clause is more compact as well Now that you have properly been introduced
to Joins, it is time to see some examples. Not just one or two examples, mind you, but
a LOT of examples. But not too many! We don’t want you to feel bored. Somewhere between
not quite enough and way too many. That is how many examples we are going to see….
In our next video. So JOIN me in the next video as we boldly go where no one has gone
before… Ok, that’s a lie. If you look at the number of views on the next video,
you will see exactly how many people have gone before you.
