I have STORE_TABLE

and TRANSACTION_TABLE

With oracle, what query is to find the product that sold in all store?

>Solution :

You can use a PARTITIONed OUTER JOIN:

SELECT t.item
FROM   store_table s
       LEFT OUTER JOIN transaction_table t
       PARTITION BY (t.item)
       ON (s.id = t.store)
GROUP BY
       t.item
HAVING COUNT(t.store) = COUNT(s.id)

Which, for the sample data:

CREATE TABLE store_table (id, store_name) AS
SELECT 1, 'STORE A' FROM DUAL UNION ALL
SELECT 2, 'STORE B' FROM DUAL UNION ALL
SELECT 3, 'STORE C' FROM DUAL;

CREATE TABLE TRANSACTION_TABLE (id, tran_id, store, item, qty) AS
SELECT 1, '001', 1, 'ruler',   2 FROM DUAL UNION ALL
SELECT 2, '002', 2, 'book',    5 FROM DUAL UNION ALL
SELECT 3, '003', 1, 'pencil', 10 FROM DUAL UNION ALL
SELECT 4, '004', 1, 'tissue',  3 FROM DUAL UNION ALL
SELECT 5, '005', 2, 'tissue',  5 FROM DUAL UNION ALL
SELECT 6, '006', 3, 'tissue',  2 FROM DUAL UNION ALL
SELECT 7, '007', 1, 'pencil',  5 FROM DUAL;

Outputs:

ITEM
tissue

db<>fiddle here