How to JOIN the same table with itself using ORDER BY RAND()?

Question

I want to get random combinations of id of a table with itself.

SELECT id FROM t1
SELECT id as id2 FROM t1 ORDER BY RAND()
SELECT id as id3 FROM t1 ORDER BY RAND()

How can I JOIN these queries to get

SELECT id,id2,id3

1    random_id    random_id
2    random_id    random_id
3    random_id    random_id
4    random_id    random_id
5    random_id    random_id

In other words, what can be the point of JOINing to simply place these three SELECTs side by side.

It is beneficial to create a unique combination, but with the above query ORDER BY RAND() can repeat the same id to id2 and id3. The former case is ideal, but the latter sufficiently works for me.

Answer 1

For this sample data of sequential ids from 1 to n, this query that uses string functions will work and will return in the columns id2 and id3 all if the ids once:

select t1.id, 
  find_in_set(t1.id, t2.ids2) id2, 
  find_in_set(t1.id, t3.ids3) id3
from tablename t1 
cross join (
  select group_concat(id order by rand()) ids2
  from tablename
) t2
cross join (
  select group_concat(id order by rand()) ids3
  from tablename
) t3

See the demo.
Results (like):

| id  | id2 | id3 |
| --- | --- | --- |
| 1   | 5   | 4   |
| 2   | 2   | 5   |
| 3   | 1   | 2   |
| 4   | 4   | 3   |
| 5   | 3   | 1   |

Answer 2

If you truly want random, then repeats are allowed. That suggests:

select t.*,
       (select t2.id
        from t t2
        order by rand()
        limit 1
       ) as id2,
       (select t3.id
        from t t3
        order by rand()
        limit 1
       ) as id3
from t;

If you want permutations in older versions of MySQL, then variables are handy:

select t.id, t1.id, t2.id
from (select t.id, (@rn := @rn + 1) as seqnum
      from t cross join
           (select @rn := 0) params
     ) t join
     (select t.id, (@rn1 := @rn1 + 1) as seqnum
      from (select t.* from t order by rand()) t cross join
           (select @rn1 := 0) params
     ) t1
     using (seqnum) join
     (select t.id, (@rn2 := @rn2 + 1) as seqnum
      from (select t.* from t order by rand()) t cross join
           (select @rn2 := 0) params
     ) t2 
     using (seqnum);

In MySQL 8+, Tim's answer is the best approach.

Here is a db<>fiddle

Answer 3

If you are using MySQL 8+, then ROW_NUMBER might work here:

WITH cte AS (
    SELECT id,
        ROW_NUMBER() OVER (ORDER BY id) rn1,
        ROW_NUMBER() OVER (ORDER BY RAND(UNIX_TIMESTAMP())) rn2,
        ROW_NUMBER() OVER (ORDER BY RAND(UNIX_TIMESTAMP()+1)) rn3
    FROM t1
)

SELECT
    t1.id,
    t2.id AS id2,
    t3.id AS id3
FROM cte t1
INNER JOIN cte t2
    ON t1.rn1 = t2.rn2
INNER JOIN cte t3
    ON t1.rn1 = t3.rn3;

The demo below was from a sample table containing the id values from 1 to 10 inclusive.

Demo