How to remove extra values ​​when JOIN on non-unique fields?

Question

MySQL

If two tables are joined by a unique field, then as a rule the result is something like:
id1 name1 id2 name2
1 Petr 1 Petrov
2 Ivan 2 Ivanov

But if id is not unique, but the same in both tables and equals, say, one, then we get something like:
id1 name1 id2 name2
1 Petr 1 Petrov
1 Petr 1 Ivanov
1 Ivan 1 Ivanov
1 Ivan 1 Petrov

Assume that name2 is unique, so we can understand that the addition of the fields took place
Do not prompt the algorithm to get or
1 Petr 1 Petrov
1 Ivan 1 Ivanov
Or:
1 Petr 1 Ivanov
1 Ivan 1 Petrov

Better, of course, the first)))

SELECT 
           `table1`.*,
            `table2`.*,
       
        FROM `table1` 
        LEFT JOIN `table2` ON `table1`.`id1` = `table2`.`id2 `

Answer 1

WITH 
cte1 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
          FROM t1 ),
cte2 AS ( SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) rn
          FROM t2 )
SELECT cte1.id id1, cte1.name name1, cte2.id id2, cte2.name name2
FROM cte1
JOIN cte2 USING (rn, id);

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b5a064...
Although, of course, refined nonsense. From the point of view of logic.