MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE players (id INT, name VARCHAR(255));
INSERT INTO players VALUES
(1,	'AA'),
(2,	'BB'),
(3,	'CC'),
(4,	'DD');

CREATE TABLE matching (id INT, player_id INT, match_id INT, `date` DATE, has_opponent INT);
INSERT INTO matching VALUES
(1,	1,	1,	'2021-06-23',	0),
(2,	2,	1,	'2021-06-23',	0),
(3,	3,	2,	'2021-06-21',	0),
(4,	4,	3,	'2021-06-22',	1);
WITH cte AS ( SELECT DISTINCT
                     match_id,
                     `date`,
                     MIN(player_id) OVER (PARTITION BY match_id) p1,
                     MAX(player_id) OVER (PARTITION BY match_id) p2,
                     has_opponent
              FROM matching )
SELECT match_id,
       `date`,
       p1.name first_opponent_name,
       CASE WHEN has_opponent OR (p1 != p2) THEN p2.name END second_opponent_name
FROM cte
JOIN players p1 ON cte.p1 = p1.id
JOIN players p2 ON cte.p2 = p2.id
ORDER BY 1


match_id
date
first_opponent_name
second_opponent_name


1
2021-06-23
AA
BB

2
2021-06-21
CC
null

3
2021-06-22
DD
DD


SELECT match_id,
       `date`,
       p1.name first_opponent_name,
       CASE WHEN has_opponent OR (p1 != p2) THEN p2.name END second_opponent_name
FROM ( SELECT match_id,
              `date`,
              MIN(player_id) p1,
              MAX(player_id) p2,
              has_opponent
       FROM matching
       GROUP BY match_id,
                `date`,
                has_opponent ) cte
JOIN players p1 ON cte.p1 = p1.id
JOIN players p2 ON cte.p2 = p2.id
ORDER BY 1


match_id
date
first_opponent_name
second_opponent_name


1
2021-06-23
AA
BB

2
2021-06-21
CC
null

3
2021-06-22
DD
DD