given I have a user table users:
| user_id | name |
|---|---|
| 1 | Bob |
| 2 | Adam |
| 3 | Charlie |
Now I want to get all combinations of one or two users:
| user1 | user2 |
|---|---|
| Bob | NULL |
| Bob | Adam |
| Bob | Charlie |
| Adam | NULL |
| Adam | Bob |
| Adam | Charlie |
| Charlie | NULL |
| Charlie | Bob |
| Charlie | Adam |
Assuming I want to get only two-person combinations, I would be simply able to do something like that:
SELECT * FROM users u1
LEFT JOIN users u2 on u1.id!=u2.id
However, I want to include combinations with only one users. How can I get the desired result?
CodePudding user response：
Use a self CROSS JOIN:
SELECT u1.name AS user1,
NULLIF(u2.name, u1.name) AS user2
FROM users u1 CROSS JOIN users u2
ORDER BY u1.user_id, NULLIF(u2.user_id, u1.user_id);
See the demo.