FULL OUTER JOIN with UNION and CROSS JOIN part 2
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.
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:
|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
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:
|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;