Type 2 Joins

These joins are based on modifying a cross join to construct the join you desire.

Cross Join

Cross joins are pretty strait forward.

SELECT * FROM person AS p, award AS a;

Just select up two or more tables with a comma separating the tables.

Inner Joins

SELECT * FROM person AS p, award AS a
WHERE p.person_id = a.award_id;

For an inner join we can just create a cross join and then remove the rows that do not match to each other with a WHERE statement. In this case we matched the IDs like we have done before.

Full Outer Join

An outer join is going to require significantly more work. Remember, an outer join is an inner join plus the records from the left table that have no match to the right table plus the records from the right table that do not match to the right table.

We already know how to create an inner join. Now we are going to use a WHERE clause with a subquery to select the records from the person table (the left table) that have no match to the award table (the right table).

SELECT * FROM person AS p
WHERE p.person_id NOT IN (
	SELECT award_id from award
	)

person_id	person_first	person_last
X00006	Freddy	Freaky
X00007	Grant	Gradenow

We haven't used a subquery for a WHERE statement until now. It is not that complicated. We write a query that selects the IDs from the award table. We then ask for the IDs from the person table that are not in the award ID list.

For reasons that will be apparent in a little bit, we need to expand the list of columns to include the columns provided by the award table. So we get this version.

SELECT
	p.*,
	NULL AS award_id,
	NULL AS award_fund,
	NULL AS award_offered,
	NULL AS award_accepted,
	NULL AS award_disbursed
FROM person AS p
WHERE p.person_id NOT IN (
	SELECT award_id from award)

person_id	person_first	person_last	award_id	award_fund	award_offered	award_accepted	award_disbursed
X00006	Freddy	Freaky
X00007	Grant	Gradenow

There are a few new things here. First, we have always used the asterisk (*) to represent all of the columns in our query. But here we are using p.* to represent the columns for just the person table. The columns for the award table are all enumerated out. We use a constant value of NULL for the values in each of those columns.

Now we need to do another query that is the reverse of the above query. We want to select the IDs from the award table that are not in the person table. We also want to pad the columns out with null values to match the inner join and the previouse query.

We know that there is a student_id in the student table for each award_id in the award table. So the query will not return any results. But we still need to include this code for completeness. If an award record that doesn't match to any student is added to the award table our outer join needs to pick that up.

SELECT
	NULL AS person_id,
	NULL AS person_first,
	NULL AS person_last,
	a.*
FROM award AS a
WHERE a.award_id NOT IN (
	SELECT person_id from person
);

Now we need to join all of this data together. But in this case we are not joining row by row, but vertically. We are going to stack them on top of each other. This is called a UNION. To UNION the queries we write the three queries separated by the keyword UNION.

It is required to have the same number of columns for each query. They do not need to be named the same. The names in the first query will be used. But this is why we needed to add columns with null values to create the correct number of columns.

When we UNION queries together any duplicates will be removed. We can prevent this, if we want, by adding the keyword ALL to give us UNION ALL.

SELECT * FROM person AS p, award AS a
WHERE p.person_id = a.award_id

UNION ALL

SELECT
	p.*,
	NULL AS award_id,
	NULL AS award_fund,
	NULL AS award_offered,
	NULL AS award_accepted,
	NULL AS award_disbursed
FROM person AS p
WHERE p.person_id NOT IN (
	SELECT award_id from award
)

UNION ALL

SELECT
	NULL AS person_id,
	NULL AS person_first,
	NULL AS person_last,
	a.*
FROM award AS a
WHERE a.award_id NOT IN (
	SELECT person_id from person
);

Left and Right Joins

Left and right joins can be created from a cross join by using the query above and removing either the left or right table data. For example, if we want a left join we simply remove the last query that selects nulls for the person table and non-matching records from the award table.

In Conclusion

There may circumstances where you would use these styles to create and outer, left, or right join. But not many. It is a lot easier to use the formats in the previous tutorial. But we will be using UNIONs later. It is important to be able to build a working UNION stack.

And it is good to learn that there are usually multiple ways to write a query.