Problem

Table: Teams

1
2
3
4
5
6
7

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| team_name   | varchar |
+-------------+---------+
team_name is the column with unique values of this table.
Each row of this table shows the name of a team.

Write a solution to report all the possible matches of the league. Note that every two teams play two matches with each other, with one team being the home_team once and the other time being the away_team.

Return the result table in any order.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

Input: 
Teams table:
+-------------+
| team_name   |
+-------------+
| Leetcode FC |
| Ahly SC     |
| Real Madrid |
+-------------+
Output: 
+-------------+-------------+
| home_team   | away_team   |
+-------------+-------------+
| Real Madrid | Leetcode FC |
| Real Madrid | Ahly SC     |
| Leetcode FC | Real Madrid |
| Leetcode FC | Ahly SC     |
| Ahly SC     | Real Madrid |
| Ahly SC     | Leetcode FC |
+-------------+-------------+
Explanation: All the matches of the league are shown in the table.

Solution

Method 1 – Using Self Join

Intuition

The key idea is to generate all possible pairs of teams where the home and away teams are different. By joining the Teams table with itself, we can pair each team with every other team, ensuring both home and away matches are covered.

Approach

  1. Perform a self join on the Teams table, aliasing it as t1 (home team) and t2 (away team).
  2. In the join condition, ensure that t1.team_name is not equal to t2.team_name to avoid pairing a team with itself.
  3. Select t1.team_name as home_team and t2.team_name as away_team.
  4. This will generate all possible matches, with each pair appearing twice (once for each home/away configuration).

Code

1
2
3
4
5
6
7
8
9

SELECT
  t1.team_name AS home_team,
  t2.team_name AS away_team
FROM
  Teams t1
JOIN
  Teams t2
ON
  t1.team_name <> t2.team_name;

Complexity

  • ⏰ Time complexity: O(n^2), where n is the number of teams (since each team is paired with every other team).
  • 🧺 Space complexity: O(1) (excluding output), as the query does not use extra space beyond the result set.