How to get unique values from two different tables?

Question

I've two tables: T1 & T2. My column of interest are 'Item' from T1 & 'Store' from T2.

Here's my query & few options i've tried but could get desired result:

Query1: (very time consuming - Its oracle DB)

select distinct  T1.Item,T2.Store from
(select distinct * from T1) T1
full outer join 
(select distinct * from T2)T2
on T1.ITEM_ID=T2.ITEM_ID 

Query2: (This is so close to the result I need but i'm stuck with max of item value instead of the whole picture)

select max(T1.ITEM),T2.Store from T1 , T2 
where  T1.ITEM_ID=T2.ITEM_ID
group by T2.Store

I am trying to get unique row values for both column

Here's the scenario i'm facing: After doing distinct im getting result as below:

Item Store
1     8
2     8
3     8

But my desired result is (1 item per store):

Item Store
1     8
2     15
3     53

That is, It should be unique for all rows but i am unable to achieve it. Please suggest.

EDIT:

Table T1:

ID         Item  ITEM_ID    CREATE_DATE          UPDATE_DATE          
---------  ---------------  ---------  -------------------  -------------------  
123           8    1       2021-05-21 21:08:14  2021-05-21 21:08:14  
15           15    7      2021-05-22 14:15:49  2021-05-22 14:15:49  

Table T2:

Store      ITEM_ID      Itemsale       Status   
8          1             (null)          C      
65         87            (null)          C      

Solution for my case

**select T1.Item,T2.Store 
from
(SELECT  Item, DENSE_RANK() OVER (ORDER BY Item )  rnk from T1 group by Item   ) T1
inner join 
(SELECT Store ,DENSE_RANK() OVER (ORDER BY Store )  rnk  from T2 group by Store ) T2
on T1.rnk=T2.rnk**

Answer 1

It seems like you should simply be doing:

select distinct  T1.Item, T2.Store 
from T1
  inner join T2 on T1.ITEM_ID = T2.ITEM_ID 

Or "including items not found in any store":

select distinct  T1.Item, T2.Store 
from T1
  left outer join T2 on T1.ITEM_ID = T2.ITEM_ID 

Or "including empty stores"

select distinct  T1.Item, T2.Store 
from T1
  right outer join T2 on T1.ITEM_ID = T2.ITEM_ID 

The time taken to do this join will depend on which columns are indexed, how many rows there are and your connection to the server. For 3 rows it should reasonably be milliseconds

If there is something else that can be used to remove duplicates (other than distinct) you should leverage that (for example if items have a "discontinued" flag, and they are replaced by another item with the same name, you should look to include discontinued flag in the where/on rather than including them in the join and using DISTINCT to clobber them out afterwards)

---

If you meant you just want "every product cross combined with every store" that's;

select distinct  T1.Item, T2.Store 
from T1
  cross join T2 

---

To join item and store on a fake id, meaning items get assigned to stores, one item per store, in some order, you can do like..

select T1.Item,T2.Store
from
  (select item, row_number() over(order by Item) r from T1 group by item) T1
  inner join 
  (select store, row_number() over(order by store) r from T2 group by store) T2
  on T1.R = T2.R