THIS IS A TUTORIAL, THE QUESTIONS ARE AT THE BOTTOM OF THE PAGE.

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.

Lets 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,

The Base table has 3 columns and 5 rows.

There are 4 populated bases

Everyones betting on when it will open.

The visitor table has 4 columns and 7 rows.

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.

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.

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.

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.

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

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.

The words visitor and Martian appear all over the place.

To fix this, lets 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.

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.

Lets call the visitor first name visitor_fn...

The visitor's last name visitor_ln...

The Martian first name martian_fn, and the Martian last name martian_ln...

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.

We can use Self-Join.

From the martian table with an alias of m

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

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.

All Martians are in the report.

But the columns are a complete mess.

There are even duplicate names!

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.

Much easier to read.

We see a list of all Martians

And the name of their supervisor

The next report will be an Inventory Report Or the Base Supply 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.

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

To help, lets 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.

One improvement I would recommend is to replace null quantities with 0.

Our next query is the No Host Report.

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 well call v)

And do a FULL JOIN on the martian table (which well call m).

Two rows are joined when the visitors 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.

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.

We will select the names of the visitors and the names of the available Martians

ExecuteGood job.

While reading the above tutorial, take notes focusing on the different types of joininner, left, right, and full. Reflect on the use of column and table alias, and consider the following scenarios:

What are some business concerns you might have if you want to retrieve the data to determine the superior for each martin even if each martin does not have a superior and did not use the correct join?

From a readability standpoint, what are the pros and cons of using table aliases when writing SQL? What are the issues when not using column aliases in the data that is displayed?

What reason would you use a subquery and right join to return the data for the Inventory Report? What happens if you didnt use the right join?