Menu
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) ?
814
Solution. Select column values in a specific order within rows to make rows with duplicate sets of values identical. Then you can use SELECT DISTINCT to remove duplicates. Alternatively, retrieve rows in such a way that near-duplicates are not even selected.
Yes, you can! The longer answer is yes, there are a few ways to combine two tables without a common column, including CROSS JOIN (Cartesian product) and UNION.
Yes, if there are duplicate values.
The go to solution for removing duplicate rows from your result sets is to include the distinct keyword in your select statement. It tells the query engine to remove duplicates to produce a result set in which every row is unique.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
