How to select unique pairs of rows from a table at random?

Question

I have two tables:

CREATE TABLE people (
    id INT NOT NULL,
    PRIMARY KEY (id)
)

CREATE TABLE pairs (
    person_a_id INT,
    person_b_id INT,
    FOREIGN KEY (person_a_id) REFERENCES people(id),
    FOREIGN KEY (person_b_id) REFERENCES people(id) 
)

I want to select pairs of people in a random order from the people table, and after choosing them, I add a randomly selected pair to the pairs table. person_a_id always refers to a person with a lower pair identifier (since the order of the pair does not matter).

The thing is, I never want to double-select one pair, so I need to check the table of pairs before I return my randomly selected pair.

Is it possible to do this using only one SQL query with reasonable efficiency and elegance?

(I do this using the Java Persistence API, but hopefully I can translate any responses into JPA code)

Answer 1

select a.id, b.id
from people1 a
inner join people1 b on a.id < b.id
where not exists (
    select *
    from pairs1 c
    where c.person_a_id = a.id
      and c.person_b_id = b.id)
order by a.id * rand()
limit 1;

Limit 1 , " " . , .

,

1 - 2
2 - 7

2 - 7 , , 2 . , only one,

select a.id, b.id
from people1 a
inner join people1 b on a.id < b.id
where not exists (
    select *
    from pairs1 c
    where c.person_a_id in (a.id, b.id))
  and not exists (
    select *
    from pairs1 c
    where c.person_b_id in (a.id, b.id))
order by a.id * rand()
limit 1;

multiple pairs , - , . , LIMIT 6 3 .

select min(a) a, min(b) b
from
(
    select
      case when mod(@p,2) = 1 then id end a,
      case when mod(@p,2) = 0 then id end b,
      @p:=@p+1 grp
    from (
        select id
        from (select @p:=1) p, people1
        order by rand()
        limit 6
    ) x
) y
group by floor(grp/2)

Answer 2

, , .

WHILE EXISTS(SELECT * FROM people 
    WHERE id NOT IN (SELECT person_a_id FROM pairs) 
    AND id NOT IN (SELECT person_b_id FROM pairs) 

, , . 1 CNT(*) ... , . ( , ... - , )

.

, , .... "" , ... , , 25% - ( , 1/n ^ 2)