compute subsets in sql

Question

i have a table t(a char) with 3 values "a" "b" "c" i want to calculate a unique subset of the cartesian product withou identity.

oracle sql

select t1.a t1a,t2.a t2a from t t1,t t2;

a   a
a   b
a   c
b   a
b   b
b   c
c   a
c   b
c   c

eliminate identical entries ok easy

select t1.a t1a,t2.a t2a from t t1,t t2 where t1.a<>t2.a;

a   b
a   c
b   a
b   c
c   a
c   b

But i want to eliminate the duplets in the last result set e.g. a b and b a is for me the same i want one of them so that results that i wan could be

a   b
a   c
c   b

How can i achieve this with SQL statements (oracle stile) (no procedure or any iterativ programming please)?

select t1.a t1a,t2.a t2a from t t1,t t2 where t1.a<>t2.a and t2.a not in (select a from t t3 where t3.a=t1.a);

does not change the rresult set

explanation what i want in a grphical style:

 a b c 
a- - -
b* - -
c* * -

also ok

 a b c 
a- * *
b- - *
c- - -

i want just the * combination in the above tablaus {(b,a),(c,a),(c,b)} or {(a,b),(a,c),(b,c)} is this posible in SQL??

Answer 1

firstly you really ought to learn ANSI SQL JOIN syntax, the comma-separated join syntax you are using hasn't been considered good practice for 30+ years.

You can use LEAST and GREATEST to order the columns i.e. turn b | a into a | b

Then use DISTINCT to remove duplicates

Answer 2

Use a hierarchical query (which avoids the self-join):

SELECT PRIOR value AS value1,
       value AS value2
FROM   table_name
WHERE  LEVEL = 2
CONNECT BY PRIOR value < value

Which, for the sample data:

CREATE TABLE table_name (value) AS
SELECT 'a' FROM DUAL UNION ALL
SELECT 'b' FROM DUAL UNION ALL
SELECT 'c' FROM DUAL;

Outputs:

VALUE1	VALUE2
a	b
a	c
b	c

fiddle