MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table match_main (player_id int, team_id int)
insert into match_main values(1, 1), (2, 1), (3, 2), (4, 2)

Records: 4  Duplicates: 0  Warnings: 0


create table users (player_id int auto_increment primary key, player_name varchar(20))
insert into users (player_name) values ('Jim'), ('Joe'), ('Fred'), ('Bill')

Records: 4  Duplicates: 0  Warnings: 0


SELECT *
FROM (SELECT MIN(team_id) AS team_1, MAX(team_id) AS team_2 FROM match_main) teams
JOIN (SELECT *, @rn1 := @rn1 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn1 := 0) r
      ORDER BY team_id ASC) m1 ON m1.team_id = teams.team_1
JOIN (SELECT *, @rn2 := @rn2 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn2 := 0) r
      ORDER BY team_id DESC) m2 ON m2.team_id = teams.team_2 AND m2.rownumber = m1.rownumber


team_1
team_2
player_id
team_id
@rn1 := 0
rownumber
player_id
team_id
@rn2 := 0
rownumber


1
2
1
1
0
1
3
2
0
1

1
2
2
1
0
2
4
2
0
2


SELECT u1.player_name, u2.player_name
FROM (SELECT MIN(team_id) AS team_1, MAX(team_id) AS team_2 FROM match_main) teams
JOIN (SELECT *, @rn1 := @rn1 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn1 := 0) r
      ORDER BY team_id ASC) m1 ON m1.team_id = teams.team_1
JOIN (SELECT *, @rn2 := @rn2 + 1 AS rownumber
      FROM match_main
      CROSS JOIN (SELECT @rn2 := 0) r
      ORDER BY team_id DESC) m2 ON m2.team_id = teams.team_2 AND m2.rownumber = m1.rownumber
JOIN users u1 ON u1.player_id = m1.player_id
JOIN users u2 ON u2.player_id = m2.player_id


player_name
player_name


Jim
Fred

Joe
Bill