Lets say I have given table:

1   A
2   A
3   A

How do I JOIN / combine the table with itself so I get every possible unique pair combination of the first column:

1   1   A
1   2   A
1   3   A
2   1   A
2   2   A
2   3   A
...

CodePudding user response：

You can do something like this. Cross JOIN is used for cross product

    -- create
CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava');

-- fetch 
SELECT e1.empId, e2.empId, e1.name FROM EMPLOYEE e1
CROSS JOIN EMPLOYEE e2;