Welcome to the future… again.
If you have not seen our previous video on Joins, I would highly recommend you do.
The reviews from critics have been overwhelmingly online…
If you have seen our previous video, you’ve only begun to discover your SQL powers.
Join me, and I will complete your training.
With our combined strength, we can end this destructive conflict,
and bring order to the database!
Welcome to Mars.
You can tell we are on Mars because of the picture of Mars on your screen.
The Martian database we will explore has 5 tables:
martian, base, visitor, inventory, and supply.
Today we are going to generate several reports
for monitoring Martian activity.
To make each report, we will need to select data from 2 tables.
We will do this using “Joins”.
The four flavors of Joins we will use are INNER, LEFT, RIGHT, and FULL.
Let’s begin by viewing the data in each table.
The Martian table has 5 columns and 13 rows.
The “base_id” tells you the base where the Martian lives, works, and plays.
John Carter is currently unassigned.
The “super_id” is the Martian ID of the person they report to.
If the “super_id” is null, then that Martian is the head of their base,
and they bow to no one.
The Base table has 3 columns and 5 rows.
There are 4 populated bases, with a Spa & Casino coming soon.
Everyone’s betting on when it will open.
The “visitor” table has 4 columns and 7 rows, because there are currently 7 visitors
on the Red Planet.
The “host_id” is the Martian ID of the person serving as host to the visitor.
Every visitor needs a host, and currently there are 2 people without one.
We will need to fix that.
The “inventory” table keeps track of the supplies in storage at each base.
And the “supply” table lists what is available in the Central Martian Storage Hub.
Thank Ares there is an abundance of Famous Ray’s Frozen Pizza.
If you’ve never eaten a slice of Ray’s while watching a Martian sunset,
then you haven’t lived….
on Mars.
When you have two tables, and there is a value that connects them, you can join them together
when you need data from both tables.
You do this by writing “SELECT” and the column names you want.
If you need to make it clear which table a column is from, you write the table name,
dot, and column name.
Next, the FROM clause.
Write the first table, and give it an ALIAS if you are allergic to excessive typing.
Next, you join it on table 2 using the “JOIN” keyword.
We will give table 2 an alias as well.
In the blank, you will specify the TYPE of join.
You write an ON clause to show when to JOIN rows from the two tables.
Typically, two tables will be connected by an ID.
You finish this query as you would any other select...
With an optional WHERE clause… or ORDER BY clause.
Here are the choices for the KINDS of joins we will use:
INNER LEFT
RIGHT, and FULL (aka FULL OUTER)
An INNER join only returns connected rows that satisfy the “ON” criteria.
The LEFT join returns all the rows in an “INNER” join, but will also return the remaining rows
from the LEFT table, table1.
Since the remaining rows in TABLE1 are not connected to a row from TABLE2, there will
be null values in the TABLE2 columns.
The RIGHT join is the same as the LEFT join, except this time the unmatched rows from the
RIGHT table are included.
And the FULL join includes connected rows, unconnected rows from the LEFT table… and
unconnected rows from the RIGHT table.
We are now ready to generate our Martian Reports for the Area 51 Martian Monitoring System
located on Phobos.
The first report will be a visitor report.
This should display the name of each visitor along with the name of their host.
The data for this report will come from the “visitor” table and “Martian” table.
They are joined by the host_id.
This identifies who is hosting the visitor.
We want to select the first and last names of the visitor…
...as well as the first and last names of the host.
We select this data from the visitor table joined on the martian table.
Let’s use the inner join first.
The ON clause shows how rows are connected.
A visitor row is joined to a martian row when the host_id equals the martian_id.
Execute…
Because we used an INNER JOIN, we only see those visitors who have a host.
We should be able to see every visitor in the “Visitor Report”, so we will change
our query to a LEFT JOIN so that every visitor will be included...even those without a host.
Execute…
We can still improve things.
For starters, our query is quite verbose.
The words “visitor” and “Martian” appear all over the place.
To fix this, let’s give “visitor” the alias “v”, and “Martian” the alias “m”.
We can now replace “visitor” with “v” throughout the query…
And all the occurrences of “martian” with “m”.
The SELECT now fits on a single line.
Execute.
{small disappointed noise} One other thing.
In the output, there are two columns named “first_name”, and two called “last_name”.
Which are the visitor names, and which are the Martian names?
We can clarify this by giving the column names aliases, too.
Let’s call the “visitor first name” “visitor_fn”...
The visitor last name “visitor_ln”...
The Martian first name “martian_fn”, and the Martian last name “martian_ln”...
Execute
The results are now clear as crystal!
But I am sad to see the SELECT occupies two rows again.
Sigh.
The second report will be the “Super Report.”
This should display a list of each Martian and the person they report to.
In this example there is a small twist.
For each person in the Martian table, the name of their “super” is ALSO in the Martian
table.
This means we will need to join the “Martian” table with itself.
You can relax.
This is perfectly legal in all planetary systems and is called a “Self Join.”
We will select all rows...
From the “martian” table with an alias of “m”
We’ll first do an INNER JOIN on the “martian” table with an alias of “s” for super.
The left table is the “martian”, and the right table is their boss.
We want the “super_id” for the Martian to match the “martian_id” of their superior.
And order by the martian_id.
Execute…
There is a problem.
We want a report of EVERY Martian.
But not every Martian has a superior.
We fix this by doing a LEFT join.
Execute…
Much better.
All Martians are in the report.
But the columns are a complete mess.
There are even duplicate names!
We must fix this immediately!
For a clean report, we will only select the name of the Martian with compact aliases…
And the name of their super with aliases “super_fn” and “super_ln”.
Execute…
Much easier to read.
We see a list of all Martians…
And the name of their super.
If their supervisor’s name is null, that means they are the head of their base.
The next report will be an “Inventory Report” Or the “Base Supply Inventory Report”
for short.
This has been a lifelong dream of mine - to generate an inventory report.
We will generate this report for Base #1.
The two tables containing the data we need are the Inventory and Supply tables.
A short SELECT query will return all the supplies at base 1.
Execute.
This is not terribly helpful, because we would like to know the name of each supply item.
Furthermore, we want to know what items they do NOT have in storage.
So we will join this data - just these 6 rows - with the supply table.
To do this we need to create a new SELECT.
Instead of selecting from the entire inventory table, we will select from the results of
our first query.
We need to give these rows a name, and we do that with an alias.
We will call it “i” for inventory.
This “query inside a query” is creatively called a “subquery.”
Next, perform a RIGHT JOIN on the “supply” table, aliased as “s”.
The reason for a RIGHT JOIN is that we want to see ALL available supplies, not just those
in stock at base 1.
Order by “supply_id” and execute…
We now need to clean this up.
To help, let’s highlight the columns from the subquery… and highlight the columns
from the supply table.
There are two “supply_id” columns.
The supply_id column from the inventory subquery is sometimes null.
This happens when the item is not in stock.
So we will select the supply_id from the supply table.
Next, there are two “quantity” columns.
We want to know the quantity at the base, so add that value to our select.
Next, select the name and description of each supply item.
Execute…
A much better report.
One improvement I would recommend is to replace null quantities with 0.
I challenge thee to find a way to do this with your database.
Our next query is the “No Host Report.”
Every visitor to Mars should have a host.
But there are some visitors without a host.
We do not want a Martian to have to host more than one visitor, so we will write a query
to find all visitors without a host, and all Martians who are available for the job.
Select all columns…
From the “visitor” table (which we’ll call “v”)
And do a FULL JOIN on the “martian” table (which we’ll call “m”).
Two rows are joined when the visitor’s host_id matches the “martian_id”.
We use a FULL JOIN because we especially want visitors without a match on the right, and
Martians without a matching row in the left table.
With a bit of cleverness, we can filter out people who are already paired up.
We will only select rows WHERE “martian_id” is NULL or the “visitor_id” is NULL.
Remember, in a FULL JOIN, if a row from one table does not have a match in the other,
nulls are used to fill in the empty columns.
Execute…
Then tidy up the columns we select.
We will select the names of the visitors… and the names of the available Martians…
Execute…
Well done us...
Your query skills are most impressive.
Socratica has taught you well.
Indeed you are powerful as I have foreseen.
Now go my viewer, leave me!
Call To Action!!
(Like, Share, Subscribe, Patreon!)