sql - Cross Join without duplicate combinations

Question

I know this question is very similar to this one: Symmetric cross join and this one too: combinations (not permutations) from cross join in sql

But what about if we have two different tables, say A and B:

select A.id,B.id from A cross join B

and I want to consider the pair (a,b) equal to (b,a) ?

Answer 1

select A.id aid,B.id bid
from A inner join B on a.id <= b.id
union
select B.id,A.id
from A inner join B on b.id < a.id

If you wanted to be more sophisticated:

select distinct
       case when a.id<=b.id then a.id else b.id end id1,
       case when a.id<=b.id then b.id else a.id end id2
from A cross join B

In my little unscientific bake off with tiny tables, the latter was faster. And below, the case expressions written as subqueries.

select distinct
       (select MIN(id) from (select a.id union select b.id)[ ]) id1,
       (select MAX(id) from (select a.id union select b.id)[ ]) id2
from A cross join B