SQL joining a table with itself

Question

I have a table looking like this:

+-----------+--------+------------+
| member_id |  name  | partner_id |
+-----------+--------+------------+
|         1 | John   | 2          |
|         2 | Eva    | 1          |
|         3 | Peter  | 4          |
|         4 | Mia    | 3          |
|         5 | Rachel | null       |
+-----------+--------+------------+

I want to display member name and name of its partner. I also need to display members without one. However, this is what I get when I use RIGTH JOIN:

SELECT m1.name, m2.name
FROM members m1
RIGHT JOIN members m2 ON m1.member_id=m2.partner_id

+--------+--------------+
|  name  | partner_name |
+--------+--------------+
| John   | Eva          |
| Eva    | John         |
| Peter  | Mia          |
| Mia    | Peter        |
| Rachel | null         |
+--------+--------------+

I want them to be displayed only once as a couple. So when I get John - Eva, I don't want Eva - John to be displayed. Can this be done?

Answer 1

@Mureinik's answer is absolutely correct. In case, if you want to hide null records in name1.

select m3.name1,m3.name2 from (
SELECT     m1.name as name1, m2.name as name2
FROM       members m1 
RIGHT JOIN members m2 ON m1.member_id = m2.partner_id AND m1.name > m2.name) as m3 where length(m3.name1)>1

Answer 2

Use left join:

select m.*, mp.name as partner_name
from members m left join
     members mp
     on m.partner_id = mp.member_id;

You have duplicates in your data, but they seem to be symmetric. A simple way around that is:

select m.*, mp.name as partner_name
from (select m.*
      from members m
      where m.member_id < m.partner_id or
            m.partner_id is null
     ) m left join
     members mp
     on m.partner_id = mp.member_id;

Here is a db<>fiddle.