All the Matches of the League - Problem

Database Easy

You are given a table Teams that contains the names of teams in a league.

Write a SQL query to report all the possible matches of the league.

Note: Every two teams play two matches with each other - one match where team A is the home team and team B is the away team, and another match where team B is the home team and team A is the away team.

Return the result table in any order.

Table Schema

Teams

Column Name	Type	Description
`team_name` PK	varchar	The name of the team (unique)

Primary Key: team_name

Note: Each row represents one team in the league

Input & Output

Example 1 — Basic League Matches

Input Table:

team_name
Arsenal
Chelsea
Liverpool

Output:

home_team	away_team
Arsenal	Chelsea
Arsenal	Liverpool
Chelsea	Arsenal
Chelsea	Liverpool
Liverpool	Arsenal
Liverpool	Chelsea

💡 Note:

With 3 teams, each team plays against the other 2 teams twice (once at home, once away). Arsenal vs Chelsea, Arsenal vs Liverpool, Chelsea vs Arsenal, Chelsea vs Liverpool, Liverpool vs Arsenal, and Liverpool vs Chelsea - totaling 6 matches.

Example 2 — Two Teams Only

Input Table:

team_name
TeamA
TeamB

Output:

home_team	away_team
TeamA	TeamB
TeamB	TeamA

💡 Note:

With only 2 teams, there are exactly 2 matches: TeamA hosts TeamB, and TeamB hosts TeamA. Each team plays once at home and once away.

Constraints

1 ≤ number of teams ≤ 100
team_name contains only letters and spaces
All team names are unique

Visualization

Tap to expand

Asked in

M Meta 12 A Amazon 8 G Google 6

Use self-join with CROSS JOIN to pair every team with every other team, filtering out cases where a team would play itself.

Table Schema

Teams

Column Name	Type	Description
`team_name` PK	varchar	The name of the team (unique)

Primary Key: team_name

Note: Each row represents one team in the league

Common Approaches

✓ Self-Join

⏱️ Time: O(n²) Space: O(n²)

Use a self-join to pair every team with every other team. The join condition ensures no team plays against itself, automatically generating home and away matches for each pair.

Self-Join — Algorithm Steps

Step 1: Join Teams table with itself using different aliases
Step 2: Filter out matches where a team would play itself
Step 3: Each valid combination creates both home and away scenarios

Visualization

Tap to expand

Step-by-Step Walkthrough

Self Join

Teams × Teams

Filter

Remove self-matches

Result

All possible matches

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n²)

Each team must be paired with every other team

⚠ Quadratic Growth

Space Complexity

O(n²)

Result contains n*(n-1) match combinations

⚠ Quadratic Space

23.4K Views

Medium Frequency

~8 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

All the Matches of the League - Problem

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Self-Join — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler