mysqljoinselectcross-joinnullif

MySQL: Get all combinations

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?

Solution

  • 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.