FULL OUTER JOIN with UNION and CROSS JOIN part 2

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

FULL OUTER JOIN, UNION and CROSS JOIN part 2

In the previous post, we have seen how to perform a FULL OUTER JOIN and what would be a practical scenario to perform such a query.

Here in part 2, based on the coaches and teams tables we used before and our role as bookmakers (who allows people to place bets on which coach will be appointed with whichever team), you will see how to perform a CROSS JOIN and when it could be useful.

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

 

Cross Join

A CROSS JOIN produces a cartesian product between two tables. As an example, based on the coaches and teams tables we have above, it will produce all possible coaches-teams combinations:

SELECT c.name as coach, t.name as team
FROM coaches c
CROSS JOIN teams t;

The query would returns 30 rows, based on 6 coaches * 5 teams. However, this result-set won't have much practical use in our case.

 

Practical use case for a CROSS JOIN

What would be very useful, is to find all possible combinations of coaches WITHOUT a team and teams WITHOUT a coach. Once we have this, as bookmakers, we could set the odds and let people place bets on it.

In the example below, the sub-select returns the teams without a coach and the outer-select returns coaches without a team.

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

The query above would produce:

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

 

So this returns all combinations of coaches without a job and teams looking for a coach.

We can take it a step further, first by creating a table that holds the odds for a coach being appointed with a team, then insert all possible combinations into this table that holds the odds.

CREATE TABLE coach_appointment_odds(
	id INT NOT NULL AUTO_INCREMENT,
    coach VARCHAR(256),
    team  VARCHAR(256),
    probability DECIMAL(7,2),
    odds VARCHAR(256),
    PRIMARY KEY (ID)
);

Now perform an INSERT based on the combinations we want from our CROSS JOIN:

INSERT INTO coach_appointment_odds(coach, team)
	SELECT c.name, t.name
	FROM coaches c
	CROSS JOIN(
		SELECT t.name 
		FROM coaches c
		RIGHT JOIN teams t on c.team_id = t.id
		WHERE c.id IS NULL
	) t
	WHERE c.team_id IS NULL;

This would then fill our table with:

id coach team probability odds
1 Kevin McHale Houston Rockets NULL NULL
2 Kevin McHale Memphis Grizzlies NULL NULL
3 Dave Joerger Houston Rockets NULL NULL
4 Dave Joerger Memphis Grizzlies NULL NULL
5 George Karl Houston Rockets NULL NULL
6 George Karl Memphis Grizzlies NULL NULL

 

Then you can set the odds by updating the records. For example:

UPDATE coach_appointment_odds
SET probability=0.25, odds="4:1"
WHERE id=2;