I have a left outer join that doesn't return all rows from the "left" table. I have no where clause, so no filtering should be applied after the join.

I am expecting:

But I'm missing the last row. Any light you could shed into this is very appreciated.

To reproduce it:

-- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

drop table t1;
drop table t2;


create table t1 
(ov_product          varchar2(18 byte)
,product             varchar2(18 byte)
)
/

create table t2 
(reporting_month     number
,product             varchar2(18 byte) 
,sender              varchar2(2 byte) 
,items               number 
)
/

insert into t1
(
select 'Product 1' ov_product, 'P1' product from dual
union
select 'Product 2' ov_product, 'P2' product from dual
union
select 'Product 3' ov_product, 'P3' product from dual
union
select 'Product 4' ov_product, 'P4' product from dual
);

insert into t2
(
select 202108, 'P1', 'AT', 100 from dual
union
select 202108, 'P2', 'AT', 25 from dual
union
-- no P3 for AT
select 202108, 'P4', 'AT', 57 from dual
union
select 202108, 'P1', 'GR', 45 from dual
union
select 202108, 'P2', 'GR', 22 from dual
union
select 202108, 'P3', 'GR', 5 from dual
union
select 202108, 'P4', 'GR', 4 from dual
)
;
commit;

select t1.ov_product
      ,t2.sender
      ,t2.items
  from t1
  left outer join t2
    on t1.product = t2.product
 order by 2, 1   
;

CodePudding user response：

Your outer join works fine.

You probably mean partitioned outer join.

See the additional query_partition_clause in the join

PARTITION BY (sender) only this join will fill the gaps in sender as you expects.

select t1.ov_product
      ,t2.sender
      ,t2.items
  from t1
  left outer join t2
  PARTITION BY  (sender)
    on t1.product = t2.product   
 order by 2, 1


OV_PRODUCT         SE      ITEMS
------------------ -- ----------
Product 1          AT        100
Product 2          AT         25
Product 3          AT           
Product 4          AT         57
Product 1          GR         45
Product 2          GR         22
Product 3          GR          5
Product 4          GR          4

CodePudding user response：

Product 3 already joined with 'GR' '5' so there is no any Product 3 null null to show.

Are you clear on how left joins behave?

CodePudding user response：

Since there is a line in t2 with product 3 there is no a null-value record to be produced with the left-join.

In order to get such a line you need either to join a table of senders if you have one. Or, if the table does not exist, you may use a CTE like this

with senders(sender) as (select distinct sender from t2)

with senders(sender) as (select distinct sender from t2)

select t1.ov_product
      ,t2.sender
      ,t2.items
  from t1
  cross join senders
  left outer join t2
    on t1.product = t2.product
   and t2.sender = senders.sender
 order by 2, 1;