FULL OUTER JOIN with UNION and CROSS JOIN part 1

Tags: queries, JOIN, SQL
Publish Date: 2016-05-28

FULL OUTER JOIN, UNION and CROSS JOIN part 1

While most of us developers are used to the INNER JOIN and LEFT / RIGHT JOIN queries, FULL OUTER JOIN and CROSS JOIN queries aren't something we come accross regularly. But what are exactly FULL OUTER JOIN and CROSS JOIN? And what is a useful scenario for them?

Before we look into a FULL OUTER JOIN, one must understand the concept behind LEFT JOIN and RIGHT JOIN. If you do already, you can skip though to FULL OUTER JOIN. Jump to the next article if you are only interested in a CROSS JOIN.

 

LEFT JOIN and RIGHT JOIN

Here is a brief recap on LEFT JOIN and RIGHT JOIN

When we perform a LEFT JOIN, the database returns all records from the "LEFT TABLE" (that is the table after the FROM keyword), regardless of whether it manages to find a corresponding record in the "RIGHT TABLE" (that is the table after the JOIN keyword), based on the given join condition. A RIGHT JOIN is the vice versa.

As an example, let's assume we have the following tables:

id name
authors
1 Malcolm Gladwell
2 Alan Watts
3 Aspiring Writer ;)
id title author_id
books
1 Outliers 1
2 David and Goliath 1
3 The Way of Zen 2
4 I Ching NULL

When we perform a LEFT JOIN like this:

SELECT b.title as title, a.name author
FROM books b
LEFT JOIN authors a ON b.author_id = a.id;

It returns all books (left table), including the ones we could not find its author (right table).

title author
Outliers Malcolm Gladwell
David and Goliath Malcolm Gladwell
The Way of Zen Alan Watts
I Ching NULL

 

When we perform a RIGHT JOIN like this:

SELECT b.title as title, a.name author
FROM books b
RIGHT JOIN authors a ON b.author_id = a.id;

It returns all authors (right table), including the ones that we could not find a book for (left table).

title author
Outliers Malclom Gladwell
David and Goliath Malcolm Gladwell
The Way of Zen Alan Watts
NULL Aspiring Writer ;)

 

 

FULL OUTER JOIN

A FULL OUTER JOIN is basically an aggregation of a LEFT JOIN with a RIGHT JOIN, without any duplicates.

In the previous examples, "Outliers" by Malcolm Gladwell appears in the results of both LEFT and RIGHT JOINs. In a FULL OUTER JOIN, this record will appear only once instead of twice.

In MySQL, the FULL OUTER JOIN syntax is not supported, so to achieve a FULL OUTER JOIN, we must use the keyword UNION.

UNION and UNION ALL

The UNION keyword performs an aggregation of two select queries and remove any duplicate records, while UNION ALL will keep these duplicates.

In order to perform an UNION (or UNION ALL), the number of columns to be returned from both queries must match. Besides of that, you better select the same columns in the same order in both queries, otherwise your result set wouldn't make sense.

Unfortunately, MySQL lets you get away with specifying different columns or same columns in a different order! But for the sake of good practice, please make sure the columns selected in both queries are exactly the same and in the same order.

Basic Syntax

The basic syntax to perform a FULL OUTER JOIN in MySQL:

SELECT a.column_1, b.column_1 [... more columns]
FROM table_a a
LEFT JOIN table_b b ON b.foreign_key = a.primary_key
UNION
SELECT a.column_1, b.column_1 [... more columns]
FROM table_a a
RIGHT JOIN table_b b ON b.foreign_key = a.primary_key;

 

Practical use case for a FULL OUTER JOIN

Most of the time when we perform a join query (inner, left or right), it happens in a scenario where one of the table has a reference (foreign key) to the other and we want to grab the corresponding information from the other table. So for the books / authors example above, we could get data from the authors table based on the books we have.

However, a FULL OUTER JOIN excels in the opposite. It is great for finding records from both sides, for which you cannot find a corresponding record in the other table. As an example, below are tables of qualified head coaches and basketball teams:

id name
teams
1 Dallas Mavericks
2 L.A. Clippers
3 Houston Rockets
4 Memphis Grizzlies
5 San Antonio Spurs
coaches
id name team_id
1 Rick Carlisle 1
2 Doc Rivers 2
3 Kevin McHale NULL
4 Dave Joerger NULL
5 Gregg Popovich 5
6 George Karl NULL

 

A bookmaker would be interested in a list of coaches without a team and a list of teams without coach, so that he can let people place bets on which coach will be appointed with whichever team.

If we perform a plain vanilla FULL OUTER JOIN:

SELECT c.name as coach, t.name as team
FROM coaches c
LEFT JOIN teams t ON c.team_id = t.id
UNION
SELECT c.name as coach, t.name as team
FROM coaches c
RIGHT JOIN teams t ON c.team_id = t.id;

It would return the following:

coach team
Rick Carlisle Dallas Mavericks
Doc Rivers L.A. Clippers
Kevin McHale NULL
Dave Joerger NULL
Gregg Popovich San Antonio Spurs
George Karl NULL
NULL Houston Rockets
NULL Memphis Grizzlies

 

The results from a plain FULL OUTER JOIN are not exactly very useful in our case, as we are only interested in coaches without a team or teams without a coach.
So to do that, we could either add WHERE-clauses in each select statement:

SELECT c.name as coach, t.name as team
FROM coaches c
LEFT JOIN teams t ON c.team_id = t.id
WHERE t.id IS NULL
UNION
SELECT c.name as coach, t.name as team
FROM coaches c
RIGHT JOIN teams t ON c.team_id = t.id
WHERE c.id IS NULL;

Or use a subquery:

SELECT * 
FROM (
	SELECT c.name as coach, t.name as team
	FROM coaches c
	LEFT JOIN teams t ON c.team_id = t.id
	UNION
	SELECT c.name as coach, t.name as team
	FROM coaches c
	RIGHT JOIN teams t ON c.team_id = t.id
) results 
WHERE coach IS NULL or team IS NULL;

Either of these FULL OUTER JOINs would give us the results we are after: coaches WITHOUT a team and teams WITHOUT a coach.

coach team
Kevin McHale NULL
Dave Joerger NULL
George Karl NULL
NULL Houston Rockets
NULL Memphis Grizzlies

 

In the next post, you will see a practical example of how a CROSS JOIN can be applied on the coaches / teams data that we have.