Unique combination of records in a SQL table (Get edges from nodes table)

I came across a use case where I wanted to generate a combination table where the attributes of a record had to be matched with all other records in the table. Something like creating list of edges in a Complete Graph using a nodes table.

To give you an example of the requirement, you can consider the following table (Postgres).

create table test_users
(
    id         uuid default uuid_generate_v4(),
    first_name text,
    last_name  text
);

I added some records into the table using the following insert queries

INSERT INTO public.test_users (id, first_name, last_name) VALUES ('3ef309db-07cd-4e3f-95db-406f8165e54d', 'john', 'doe');
INSERT INTO public.test_users (id, first_name, last_name) VALUES ('50f33b51-1d3a-463c-9d5a-6f0b2c15cba2', 'marry', 'poppins');
INSERT INTO public.test_users (id, first_name, last_name) VALUES ('9812fbcf-d269-47a4-801d-2a8916e114e1', 'tony', 'stark');
INSERT INTO public.test_users (id, first_name, last_name) VALUES ('8d7b8d00-9602-4a12-914d-3df6a40d2716', 'idris', 'alba');
INSERT INTO public.test_users (id, first_name, last_name) VALUES ('2dee874f-f41e-4d68-a341-45e0a18079df', 'john', 'frusciante');

And you’ll get the following table in place

                  id                  | first_name | last_name
--------------------------------------+------------+------------
 3ef309db-07cd-4e3f-95db-406f8165e54d | john       | doe
 50f33b51-1d3a-463c-9d5a-6f0b2c15cba2 | marry      | poppins
 9812fbcf-d269-47a4-801d-2a8916e114e1 | tony       | stark
 8d7b8d00-9602-4a12-914d-3df6a40d2716 | idris      | alba
 2dee874f-f41e-4d68-a341-45e0a18079df | john       | frusciante

Now I want to create a combination of these names in such a way that the first_name of a row should have all the possible combination available such that (f1, f2) = (f2, f1). To give you some more context the expected output should be as follows.

 first_name | first_name | last_name  | last_name
------------+------------+------------+------------
 john       | marry      | doe        | poppins
 john       | tony       | doe        | stark
 john       | idris      | doe        | alba
 john       | john       | doe        | frusciante
 marry      | tony       | poppins    | stark
 marry      | idris      | poppins    | alba
 marry      | john       | poppins    | frusciante
 tony       | idris      | stark      | alba
 tony       | john       | stark      | frusciante
 idris      | john       | alba       | frusciante

You can see that the combination of column 1 and column 2 for first_name is unique in the table.

The closest that I was able to reach was to have an inner join using the following query.

select t1.first_name,
    t2.first_name,
    t1.last_name,
    t2.last_name
from test_users t1
        left join test_users t2 on t1.id <> t2.id;

But this yields all the records where (f1, f2) != (f2, f1) hence creating redundant records. Is there a way to get unique list of edges from the list of nodes in a Complete Graph using SQL?

Answer

Just use < (or >) instead of <>in the join condition:

select 
    t1.first_name first_name1, 
    t2.first_name first_name2, 
    t1.last_name last_name1, 
    t2.last_name last_name2
from test_users t1
inner join test_users t2 on t1.id < t2.id

Notes:

• you probably want to alias the column in the resultset to disambiguate the columns coming from both tables

• no need for a left join – an inner join does what you want