Oracle SQL - join two tables show unmatched results-CodePudding

I have two tables:

POSITION_TABLE

Account	Security	Pos_Quantity
1	A	100
2	B	200

TRADE_TABLE

Account	Security	Trade_Quantity
1	A	50
2	C	10

I want to join them in a way that matching rows are displayed as one row, but unmatching rows are also displayed, so standard LEFT JOIN wouldnt work.

Expected output:

Account	Security	Pos_Quantity	Trade_Quantity
1	A	100	50
2	B	200	0
2	C	0	10

How do I do that?

CodePudding user response：

A full outer join would work nicely here:

with position_table as (select 1 account, 'A' security, 100 pos_quantity from dual union all
                        select 2 account, 'B' security, 200 pos_quantity from dual),
        trade_table as (select 1 account, 'A' security, 50 trade_quantity from dual union all
                        select 2 account, 'C' security, 10 trade_quantity from dual)                   
select coalesce(pt.account, tt.account) account,
       coalesce(pt.security, tt.security) security,
       coalesce(pt.pos_quantity, 0) pos_quantity,
       coalesce(tt.trade_quantity, 0) trade_quantity
from   position_table pt
       full outer join trade_table tt on pt.account = tt.account
                                         and pt.security = tt.security
order by account,
         security;

db<>fiddle - note how you can see that the full outer join works just fine with subqueries defined in a where clause!