Hack 14. Generate Combinations

A JOIN with no join conditions results in every row in one table being connected to every row in another table, forming all possible row combinations. Often this is done by mistake, but it can be useful.

CROSS JOIN queries occur rarely, but when you need them you need to know how to handle them. A table that is used more than once is known as a self-join. If there are no join conditions between the two instances of the same table, your query will produce every combination of rows possible. So, a table with a row containing 'A' and a row containing 'B', when joined with itself, will produce ('A','A'), ('A','B'), ('B','A'), and ('B','B'). The effect is to produce all combinations of rows.

To demonstrate this further, say you have four soccer teams in your soccer league. Each one will play the other twiceonce at home and once awayas shown in Table 2-10 and Table 2-11.

You need to write a query that shows the current scores for all possible games. To get all the possible combinations use a CROSS JOIN:

mysql> SELECT home.teamname Home, away.teamname Away -> FROM teams home CROSS JOIN teams away -> ; +----------+----------+ | Home | Away | +----------+----------+ | Lions | Lions | | Tigers | Lions | | Wildcats | Lions | | Toads | Lions | | Lions | Tigers | | Tigers | Tigers | | Wildcats | Tigers | | Toads | Tigers | | Lions | Wildcats | | Tigers | Wildcats | | Wildcats | Wildcats | | Toads | Wildcats | | Lions | Toads | | Tigers | Toads | | Wildcats | Toads | | Toads | Toads | +----------+----------+ 16 rows in set (0.00 sec)

Now you need to introduce a condition that stops the same team from playing itself:

mysql> SELECT home.teamname Home, away.teamname Away -> FROM teams home CROSS JOIN teams away -> WHERE home.teamname != away.teamname -> ; +----------+----------+ | Home | Away | +----------+----------+ | Tigers | Lions | | Wildcats | Lions | | Toads | Lions | | Lions | Tigers | | Wildcats | Tigers | | Toads | Tigers | | Lions | Wildcats | | Tigers | Wildcats | | Toads | Wildcats | | Lions | Toads | | Tigers | Toads | | Wildcats | Toads | +----------+----------+ 12 rows in set (0.00 sec)

To show the scores for games already played and to leave gaps where games have not been played you must use a LEFT OUTER JOIN ([Hack #13], [Hack #26]) to connect the cross-product to the tscores table:

mysql> SELECT home.teamname Home, away.teamname Away, -> tscores.homescore,tscores.awayscore -> FROM teams home CROSS JOIN teams away LEFT JOIN tscores on -> (home.teamname = tscores.hometeam -> AND tscores.awayteam = away.teamname) -> WHERE home.teamname != away.teamname -> ; +----------+----------+-----------+-----------+ | Home | Away | homescore | awayscore | +----------+----------+-----------+-----------+ | Tigers | Lions | NULL | NULL | | Wildcats | Lions | NULL | NULL | | Toads | Lions | NULL | NULL | | Lions | Tigers | NULL | NULL | | Wildcats | Tigers | 0 | 0 | | Toads | Tigers | 3 | 5 | | Lions | Wildcats | 1 | 4 | | Tigers | Wildcats | NULL | NULL | | Toads | Wildcats | NULL | NULL | | Lions | Toads | NULL | NULL | | Tigers | Toads | NULL | NULL | | Wildcats | Toads | NULL | NULL | +----------+----------+-----------+-----------+ 12 rows in set (0.00 sec)