Unique row in oracle sql

You can use:

SELECT s.*,
       ps.prod_name,
       ss.ser_name
FROM   subscriber s
       LEFT OUTER JOIN product_subscriber ps
       ON s.s_id = ps.s_id
       LEFT OUTER JOIN service_subscriber ss
       ON (   s.s_id = ss.s_id
          AND (ps.s_id IS NULL OR ps.id = ss.id + 10))
WHERE  ps.s_id IS NOT NULL
OR     ss.s_id IS NOT NULL

Which, for the sample data:

CREATE TABLE subscriber (S_ID, con) AS
SELECT 1, 111 FROM DUAL UNION ALL
SELECT 2, 222 FROM DUAL UNION ALL
SELECT 3, 333 FROM DUAL UNION ALL
SELECT 4, 444 FROM DUAL UNION ALL
SELECT 5, 555 FROM DUAL;

CREATE TABLE product_subscriber (s_id, id, prod_name) AS
SELECT 1, 20, 'offer 1' FROM DUAL UNION ALL
SELECT 1, 21, 'offer 2' FROM DUAL UNION ALL
SELECT 2, 20, 'offer 1' FROM DUAL UNION ALL
SELECT 3, 21, 'offer 2' FROM DUAL;

CREATE TABLE service_subscriber (s_id, id, ser_name) AS
SELECT 1, 10, 'offer 1' FROM DUAL UNION ALL
SELECT 1, 11, 'offer 2' FROM DUAL UNION ALL
SELECT 3, 11, 'offer 2' FROM DUAL UNION ALL
SELECT 3, 10, 'offer 1' FROM DUAL UNION ALL
SELECT 5, 10, 'offer 1' FROM DUAL;

Outputs:

S_ID	CON	PROD_NAME	SER_NAME
1	111	offer 1	offer 1
1	111	offer 2	offer 2
2	222	offer 1	null
3	333	offer 2	offer 2
5	555	null	offer 1

db<>fiddle here

To me, it looks as if you need to include additional join condition - a "virtual" column created by row_number analytic function (at least, that's what I figured out looking at desired output); something like this:

Sample data:

SQL> with
  2  subscriber (s_id, con) as
  3    (select 1, 111 from dual union all
  4     select 2, 222 from dual union all
  5     select 3, 333 from dual union all
  6     select 4, 444 from dual union all
  7     select 5, 555 from dual
  8    ),
  9  product_subscriber (s_id, id, prod_name) as
 10    (select 1, 20, 'offer 1' from dual union all
 11     select 1, 21, 'offer 2' from dual union all
 12     select 2, 20, 'offer 1' from dual union all
 13     select 3, 21, 'offer 2' from dual
 14    ),
 15  service_subscriber (s_id, id, ser_name) as
 16    (select 1, 10, 'offer 1' from dual union all
 17     select 1, 11, 'offer 2' from dual union all
 18     select 3, 11, 'offer 2' from dual union all
 19     select 3, 10, 'offer 1' from dual union all
 20     select 5, 10, 'offer 1' from dual
 21    ),
 22  --

Query begins here; CTEs just calculate row numbers in an outer join of subscriber and another table:

 23  temp_ps as
 24    (select s.s_id, ps.id, ps.prod_name,
 25            row_number() over (partition by s.s_id order by ps.id desc) rn
 26     from subscriber s left join product_subscriber ps on ps.s_id = s.s_id
 27    ),
 28  temp_ss as
 29    (select s.s_id, ss.id, ss.ser_name,
 30            row_number() over (partition by s.s_id order by ss.id desc) rn
 31     from subscriber s left join service_subscriber ss on ss.s_id = s.s_id
 32    )
 33  select ss.s_id, ss.ser_name, ps.prod_name
 34  from temp_ss ss left join temp_ps ps on ps.s_id = ss.s_id and nvl(ps.rn, -1) = nvL(ss.rn, -1)
 35  order by ss.s_id, ss.ser_name, ps.prod_name;

      S_ID SER_NAME   PROD_NAME
---------- ---------- ----------
         1 offer 1    offer 1
         1 offer 2    offer 2
         2            offer 1
         3 offer 1
         3 offer 2    offer 2
         4
         5 offer 1

7 rows selected.

SQL>