WITH
    tblA AS
        (
        select 1 cust_id, 'A' brand_name, '1a' product_id, 1 val from dual union all
        select 1 cust_id, 'A' brand_name, '1ab' product_id, 2 val from dual union all
        select 1 cust_id, 'B' brand_name, '1b' product_id, 3 val from dual union all
        select 2 cust_id, 'A' brand_name, '2a' product_id, 4 val from dual union all
        select 3 cust_id, 'A' brand_name, NULL product_id, NULL val from dual union all
        select 3 cust_id, 'C' brand_name, NULL product_id, NULL val from dual
        ),
    tblB AS
        (
        select 'A' brand_name,'1a'  product_id from dual union all
        select 'A' brand_name,'2a' product_id from dual union all
        select 'A' brand_name,'1ab' product_id from dual union all
        select 'A' brand_name,'3a' product_id from dual union all
        select 'B' brand_name,'1b' product_id from dual union all
        select 'B' brand_name,'2b' product_id from dual union all
        select 'C' brand_name,'3c' product_id from dual
       )
SELECT
    *
FROM
    tblA
    JOIN
    tblB
    ON

SQL> WITH
  2      tblA AS
  3          (
  4          select 1 cust_id, 'A' brand_name, '1a' product_id, 1 val from dual union all
  5          select 1 cust_id, 'A' brand_name, '1ab' product_id, 2 val from dual union all
  6          select 1 cust_id, 'B' brand_name, '1b' product_id, 3 val from dual union all
  7          select 2 cust_id, 'A' brand_name, '2a' product_id, 4 val from dual union all
  8          select 3 cust_id, 'A' brand_name, NULL product_id, NULL val from dual union all
  9          select 3 cust_id, 'C' brand_name, NULL product_id, NULL val from dual
 10          ),
 11      tblB AS
 12          (
 13          select 'A' brand_name,'1a'  product_id from dual union all
 14          select 'A' brand_name,'2a' product_id from dual union all
 15          select 'A' brand_name,'1ab' product_id from dual union all
 16          select 'A' brand_name,'3a' product_id from dual union all
 17          select 'B' brand_name,'1b' product_id from dual union all
 18          select 'B' brand_name,'2b' product_id from dual union all
 19          select 'C' brand_name,'3c' product_id from dual
 20         )
 21  SELECT *
 22  FROM
 23      tblA partition by (cust_id)
 24      right outer join
 25      tblB
 26      ON tbla.brand_name = tblb.brand_name and tbla.product_id = tblb.product_id
 27  WHERE tblb.brand_name in
 28        (select brand_name from tbla a where a.cust_id = tbla.cust_id)
 29  order by 1,5,6
 30  /
   CUST_ID B PRO        VAL B PRO
---------- - --- ---------- - ---
         1 A 1a           1 A 1a
         1 A 1ab          2 A 1ab
         1                  A 2a
         1                  A 3a
         1 B 1b           3 B 1b
         1                  B 2b
         2                  A 1a
         2                  A 1ab
         2 A 2a           4 A 2a
         2                  A 3a
         3                  A 1a
         3                  A 1ab
         3                  A 2a
         3                  A 3a
         3                  C 3c

tblA partition by (cust_id)