Skip to main content
Talentd

All the Matches of the League - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase3 min readAsked at: Microsoft
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Self Join with Inequality FilterO(n^2)O(1)Standard SQL solution when generating every ordered pair of rows from the same table
Cross Join + WHERE FilterO(n^2)O(1)Useful when explicitly forming a Cartesian product and filtering invalid pairs afterward

Video Solution

LeetCode 2339 "All Matches of the League" Microsoft Interview SQL Question with Detailed Explanation • Everyday Data Science • 1,265 views views

Frequently Asked Questions

Is All the Matches of the League easy or hard?
All the Matches of the League is classified as an Easy database problem. It mainly tests whether you recognize the need for a self join to generate pairwise combinations of rows.
All the Matches of the League Python/Java solution
This is a database problem, so the expected solution is written in SQL rather than Python or Java. The query uses a self join on the Teams table to generate every valid home and away team pairing.
How to solve All the Matches of the League in O(n)?
Generating every match between teams cannot be done in O(n) because the output itself contains O(n^2) rows. Each team must be paired with every other team, producing n × (n − 1) combinations. The optimal SQL approach therefore runs in O(n^2) time using a self join.
What is the best approach for All the Matches of the League?
The best approach is a SQL self join on the Teams table. Create two aliases of the table and pair every team with every other team using a condition like t1.team_name != t2.team_name. This generates all ordered match combinations with O(n^2) time complexity and constant extra space.
Is All the Matches of the League asked at Google/Amazon/Meta?
This problem represents a common SQL interview pattern used by companies like Amazon, Google, and Meta. Interviewers frequently test self joins, cross joins, and row pairing logic in database rounds. Variations of this question appear in many SQL screening assessments.
What data structure is used in All the Matches of the League?
The problem operates on a relational database table rather than traditional data structures. The key concept is a SQL self join, which pairs rows from the same table to produce combinations of teams.
What is the time complexity of All the Matches of the League?
The query runs in O(n^2) time because every team is paired with every other team. If there are n teams, the result contains n × (n − 1) rows. The database effectively compares each row with all other rows during the self join.

Ready to solve this problem?

Practice All the Matches of the League with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary