Watch 10 video solutions for All the Matches of the League, a easy level problem involving Database. This walkthrough by ThePrimeTime has 1,196,232 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Teams
+-------------+---------+ | 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.
Example 1:
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.
Problem Overview: The table Teams stores the names of all teams in a league. Every team must play against every other team. The task is to return every possible match as home_team and away_team, excluding cases where a team plays against itself.
Approach 1: Self Join on Teams Table (O(n2) time, O(1) extra space)
The core idea is to pair each team with every other team. In SQL, the cleanest way to generate these pairings is a self join. You join the Teams table with itself using two aliases such as t1 and t2. The join condition t1.team_name != t2.team_name removes matches where a team would play against itself. This produces every valid home–away combination, meaning if there are n teams, the result contains n × (n − 1) rows.
The database engine effectively performs a Cartesian pairing between rows from both table aliases and then filters invalid pairs. Sorting the result with ORDER BY home_team, away_team ensures consistent output. This pattern appears frequently in SQL interview questions where you need to generate combinations or permutations of rows from the same dataset.
This approach works because a league schedule can be modeled as permutations of teams where order matters: Team A vs Team B is different from Team B vs Team A. The self join naturally produces both rows. No additional tables or temporary structures are required, so the extra space usage remains constant while the query scans the table twice.
Understanding this pattern is valuable for many database problems. Anytime you need comparisons between rows of the same table—such as finding duplicates, pairing entities, or generating all possible interactions—a self join is usually the first tool to consider.
Recommended for interviews: The self join solution is the expected approach. It directly models the problem as pair generation and uses standard SQL join operations. Interviewers typically want to see that you recognize the need to join a table with itself and apply a filtering condition to remove identical pairs.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Self Join with Inequality Filter | O(n^2) | O(1) | Standard SQL solution when generating every ordered pair of rows from the same table |
| Cross Join + WHERE Filter | O(n^2) | O(1) | Useful when explicitly forming a Cartesian product and filtering invalid pairs afterward |
