Type 1 Joins

Joins basically come in two styles. I have arbitrarily decided to call them Type 1 and Type 2. I can do that. I'm the author.

In my opinion, type 1 joins joins should be generally preferred. They are easier to visualize and debug. They also are far easier to create subqueries to join to the main data.

Cross Joins

Cross joins simply shuffle the data from two tables together. Every record on the left table will be duplicated the necessary number of times so it can be joined to every record on the right table.

SELECT * FROM person AS p
CROSS JOIN award AS a
WHERE person_id = 'X00001';

person_id	person_first	person_last	award_id	award_fund	award_offered	award_accepted	award_disbursed
X00001	Alex	Alexander	X00001	PELL	100.0	100.0	100.0
X00001	Alex	Alexander	X00001	SEOG	101.0	101.0	101.0
X00001	Alex	Alexander	X00001	SUB	102.0	0.0	0.0
X00001	Alex	Alexander	X00001	UNSUB	103.0	103.0	0.0
X00001	Alex	Alexander	X00002	PELL	200.0	0.0	0.0
X00001	Alex	Alexander	X00002	SEOG	21.0	20.0
X00001	Alex	Alexander	X00002	SUB	202.0	0.0	0.0
X00001	Alex	Alexander	X00002	UNSUB	203.0	203.0	0.0
X00001	Alex	Alexander	X00003	PELL	3000.0	300.0	300.0
X00001	Alex	Alexander	X00004	SUB	402.0	402.0	0.0
X00001	Alex	Alexander	X00004	UNSUB	400.0	4.0	0.0
X00001	Alex	Alexander	X00005	PELL	555.0	500.0	500.0
X00001	Alex	Alexander	X00008	PELL	88.0	0.0	0.0
X00001	Alex	Alexander	X00008	SEOG	80.0	80.0	80.0
X00001	Alex	Alexander	X00009	SEOG	90.0
X00001	Alex	Alexander	X00010	PELL	100.0
X00001	Alex	Alexander	X00010	SEOG	110.0

As we saw in the general joins page, cross joins create a large output dataset. Without the WHERE clause the query above generated 170 rows.

Cross Joins As Inner Joins

Cross joins have some uses, mostly for creating every possible combination for statistical or probabilistic study. But for most work you will need to produce results where the data from the two tables are logically connected. For example, if the right and left tables hold data about students you only want to match the rows on the right table to rows on the left table if the rows are both referring to the same student.

The example above is not logically connected. We have students from the person table matched to awards that are not for that particular student. We can fix this by getting rid of the rows that do not match.

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

person_id	person_first	person_last	award_id	award_fund	award_offered	award_accepted	award_disbursed
X00001	Alex	Alexander	X00001	PELL	100.0	100.0	100.0
X00001	Alex	Alexander	X00001	SEOG	101.0	101.0	101.0
X00001	Alex	Alexander	X00001	SUB	102.0	0.0	0.0
X00001	Alex	Alexander	X00001	UNSUB	103.0	103.0	0.0
X00002	Bob	Baker	X00002	PELL	200.0	0.0	0.0
X00002	Bob	Baker	X00002	SEOG	21.0	20.0
X00002	Bob	Baker	X00002	SUB	202.0	0.0	0.0
X00002	Bob	Baker	X00002	UNSUB	203.0	203.0	0.0
X00003	Charlene	Crystal	X00003	PELL	3000.0	300.0	300.0
X00004	Dolly	Donaldson	X00004	SUB	402.0	402.0	0.0
X00004	Dolly	Donaldson	X00004	UNSUB	400.0	4.0	0.0
X00005	Edgar	East	X00005	PELL	555.0	500.0	500.0
X00008	Helen	Helene	X00008	PELL	88.0	0.0	0.0
X00008	Helen	Helene	X00008	SEOG	80.0	80.0	80.0
X00009	Isabella	Inanova	X00009	SEOG	90.0
X00010	John	Jacobson	X00010	PELL	100.0
X00010	John	Jacobson	X00010	SEOG	110.0

This knocks down the results to just 17 rows and logically associated. The WHERE statement only keeps the rows where the students from each table match. On every row the person ID from the left table is also the corresponding ID on the right table. Logically, this creates an inner join. The rows on the left table that have no match on the right table are deleted and vice versa. We will explore inner joins more below.

We can replace the WHERE clause with an ON statement. The results for the query below are the same as the query above.

SELECT * FROM person AS p
CROSS JOIN award AS a
ON p.person_id = a.award_id;

Full Outer Joins

We have seen we can limit cross joins to only records that match IDs between tables using an ON statement. This creates an inner join. For a full outer join, a left join, a right join, or a natural inner join we must provide an ON statement.

SELECT * FROM person AS p
FULL OUTER JOIN award AS a ON p.person_id = a.award_id;

person_id	person_first	person_last	award_id	award_fund	award_offered	award_accepted	award_disbursed
X00001	Alex	Alexander	X00001	PELL	100.0	100.0	100.0
X00001	Alex	Alexander	X00001	SEOG	101.0	101.0	101.0
X00001	Alex	Alexander	X00001	SUB	102.0	0.0	0.0
X00001	Alex	Alexander	X00001	UNSUB	103.0	103.0	0.0
X00002	Bob	Baker	X00002	PELL	200.0	0.0	0.0
X00002	Bob	Baker	X00002	SEOG	21.0	20.0
X00002	Bob	Baker	X00002	SUB	202.0	0.0	0.0
X00002	Bob	Baker	X00002	UNSUB	203.0	203.0	0.0
X00003	Charlene	Crystal	X00003	PELL	3000.0	300.0	300.0
X00004	Dolly	Donaldson	X00004	SUB	402.0	402.0	0.0
X00004	Dolly	Donaldson	X00004	UNSUB	400.0	4.0	0.0
X00005	Edgar	East	X00005	PELL	555.0	500.0	500.0
X00006	Freddy	Freaky
X00007	Grant	Gradenow
X00008	Helen	Helene	X00008	PELL	88.0	0.0	0.0
X00008	Helen	Helene	X00008	SEOG	80.0	80.0	80.0
X00009	Isabella	Inanova	X00009	SEOG	90.0
X00010	John	Jacobson	X00010	PELL	100.0
X00010	John	Jacobson	X00010	SEOG	110.0

There are no award records that do not match to a person record. But there are two person records that do not match to any award records. For those two students the rows provided by the award table are null.

But suppose we only want certain columns? What if we only want one ID column, the fund name, and the disbursed amount?

SELECT
	p.person_id,
	a.award_fund,
	a.award_disbursed
FROM person AS p
FULL OUTER JOIN award AS a ON p.person_id = a.award_id;

person_id	award_fund	award_disbursed
X00001	PELL	100.0
X00001	SEOG	101.0
X00001	SUB	0.0
X00001	UNSUB	0.0
X00002	PELL	0.0
X00002	SEOG
X00002	SUB	0.0
X00002	UNSUB	0.0
X00003	PELL	300.0
X00004	SUB	0.0
X00004	UNSUB	0.0
X00005	PELL	500.0
X00006
X00007
X00008	PELL	0.0
X00008	SEOG	80.0
X00009	SEOG
X00010	PELL
X00010	SEOG

This is where the aliases we have been using help keep the rows organized. We can clearly see which table the columns are drawing from. If we had two tables with just "id" columns it would still be obvious. We we would simply use column names "p.id, a.id".

Instead of joining the entire award table we can use a subquery to manipulate the the awards prior to joining them.

SELECT
	p.person_id,
	a.award_fund,
	a.award_disbursed
FROM person AS p
FULL OUTER JOIN (
	SELECT
		award_id,
		award_fund,
		award_disbursed
	FROM award
) AS a ON p.person_id = a.award_id;

This query does the same as the previous one but late we will learn how to manipulate the award data prior to joining it. It also may give us better performance. When the database reads the code it will execute the subquery in the parenthesis. (Remember, operations in parenthesis are executed first similar to algebra.) With our subquery it will be able to create a small temporary table to be joined to the person table. But with the previous examples it needs to query the entire awards table including rows and columns we do not need. For large queries on large tables the temporary table's size can be reduced significantly. This can lead to the query taking less time to execute.

Left, Right, and Inner Joins

At this point you may be able to see the pattern we have going. To do a left, right, or inner join we only need to follow the pattern.

SELECT * FROM person AS p
LEFT JOIN award AS a ON p.person_id = a.award_id;

SELECT * FROM person AS p
RIGHT JOIN award AS a ON p.person_id = a.award_id;

SELECT * FROM person AS p
INNER JOIN award AS a ON p.person_id = a.award_id;

Multiple Joins

We can do multiple joins on our base table. In this example we are going to select all of the records from the person table. Then we are going to do left join to add a subquery selecting the disbursed amounts for the Pell grants. Then we are going to do the same for the SEOG grants. This will create one column for Pell and another for SEOG. This is sometimes called transforming from "long to wide format".

SELECT
	p.person_id,
	pell.award_disbursed AS pell_disbursed,
	seog.award_disbursed AS seog_disbursed
FROM person AS p

LEFT JOIN (
	SELECT
		award_id,
		award_disbursed
	FROM award
	WHERE award.award_fund = 'PELL'
) AS pell ON pell.award_id = p.person_id

LEFT JOIN (
	SELECT
		award_id,
		award_disbursed
	FROM award
	WHERE award.award_fund = 'SEOG'
) AS seog ON seog.award_id = p.person_id

person_id	pell_disbursed	seog_disbursed
X00001	100.0	101.0
X00002	0.0
X00003	300.0
X00004
X00005	500.0
X00006
X00007
X00008	0.0	80.0
X00009
X00010

The output has a lot of null values. This is to be expected. We started by selecting a complete list of students, but many of them do not have a Pell or SEOG award. We can remove the null lines with a WHERE clause. We must put the where clause under the last join.

SELECT
	p.person_id,
	pell.award_disbursed AS pell_disbursed,
	seog.award_disbursed AS seog_disbursed
FROM person AS p

LEFT JOIN (
	SELECT
		award_id,
		award_disbursed
	FROM award
	WHERE award.award_fund = 'PELL'
) AS pell ON pell.award_id = p.person_id

LEFT JOIN (
	SELECT
		award_id,
		award_disbursed
	FROM award
	WHERE award.award_fund = 'SEOG'
) AS seog ON seog.award_id = p.person_id

WHERE pell_disbursed IS NOT NULL
OR seog_disbursed IS NOT NULL

person_id	pell_disbursed	seog_disbursed
X00001	100.0	101.0
X00002	0.0
X00003	300.0
X00005	500.0
X00008	0.0	80.0

In Conclusion

This last example is why I usually prefer queries with this style of joins. It is easier to see what is going on. It is easier to develop and debug. We can easily see there is q query creating a base set of data pulled from the person table. Then we left join the award table twice. The award table columns are well named so we know the source of the columns in the column list. But we use aliases. Even if we were working a database with more generic column names the source of the columns would be apparent.