Get unique pairs from self-join, plus rows without a match

Question

I'm having a problem with avoiding querying rows that are duplicates except the duplicate values alternate between columns.

I have:

select player_standings.player_ID, matched_player.player_ID 
from player_standings 
left join 
(select player_ID, wins from player_standings) as matched_player 
on matched_player.wins = player_standings.wins 
and matched_player.player_ID != player_standings.player_ID

Layout of table player_standings:

player_ID serial PRIMARY KEY,
wins int NOT NULL

Say I have the following rows in player_standings:

player_id | wins
----------+-------
1253      | 1
1251      | 1
1252      | 0
1250      | 0
1259      | 7

And I get back:

1253, 1251
1252, 1250
1250, 1252  -- reverse dupe
1251, 1253  -- reverse dupe
1259, NULL

The result I want is:

1253, 1251
1252, 1250
1259, NULL

Answer 1

One way:

SELECT DISTINCT
       GREATEST (p1.player_id, p2.player_id) AS id1
     , LEAST    (p1.player_id, p2.player_id) AS id2
FROM   player_standings p1
LEFT   JOIN  player_standings p2 ON p1.wins = p2.wins
                                AND p1.player_id <> p2.player_id;

Another way:

SELECT p1.player_id AS id1, p2.player_id AS id2
FROM   player_standings p1
JOIN   player_standings p2 USING (wins)
WHERE  p1.player_id > p2.player_id;

UNION ALL
SELECT player_id AS id1, NULL::int AS id2
FROM   player_standings p
WHERE  NOT EXISTS (
   SELECT 1
   FROM   player_standings
   WHERE  wins = p.wins
   AND    player_id <> p.player_id
   );

The expression p1.player_id > p2.player_id rules out duplicates (except if you have duplicates in the base table).

UNION ALL adds every row without a match once.