Unable to get all rows by doing full outer join in SQL-CodePudding

I have two tables. First table is

Category                            Date        Sales_1
Extra Soft Chew Bottle 67g          2022-02-09  2091847.8100
Eclipse Mint Pocket 17g             2022-02-09  1.6000
Wrigleys Eclipse Gum S/F 4pk 56g    2022-02-09  597003.4900
Eclipse Chewy Mints Bottle 93g      2022-02-09  1533084.3100
WRIGLEYS EXTRA S/F PRO 3 PACK       2022-02-09  3.5000
Eclipse Chewy Mint Pouch 70g        2022-02-09  3.0000

2nd table is

Category                            Date        Sales_2
Eclipse Mint Pocket 17g             2022-02-09  0.8000
Wrigleys Eclipse Gum S/F 4pk 56g    2022-02-09  131965.9200
Eclipse Chewy Mints Bottle 93g      2022-02-09  1890319.4700
Extra Active 28 pc 54g              2022-02-09  2.1600
Extra Soft Chew Bottle 67g          2022-02-09  2371403.1900

If i do full outer join on category from table 1 and table 2 on category. i.e.

   Category                     Date        Sales_1         Sales_2
   Extra Soft Chew Bottle 67g   2022-02-09  2091847.8100    2371403.1900
   Eclipse Mint Pocket 17g      2022-02-09  1.6000  0.8000
   Wrigleys Eclipse Gum S/F 4pk 56g 2022-02-09  597003.4900 131965.9200
   Eclipse Chewy Mint Pouch 70g 2022-02-09  3.0000  
   WRIGLEYS EXTRA S/F PRO 3 PACK    2022-02-09  3.5000  
    Null        Null                         Null            2.1600
   Eclipse Chewy Mints Bottle 93g   2022-02-09  1533084.3100    1890319.4700

Eclipse Chewy Mints Bottle 93g,2022-02-09,1533084.3100,1890319.4700

It's not including below value in the about output

Category                            Date        Sales_2
Extra Active 28 pc 54g              2022-02-09  2.1600

Can anyone help me with this?

CodePudding user response：

select table_1.* from table_1 
union
select table_2.* from table_2

CodePudding user response：

You are not selecting anything from table2 try

select coalesce(table_1.category,table2.category) cat,
       coalesce(table_1.date,table2.date) dt,
       coalesce(table_1.sales,table2.sales) sales
from table_1 
full outer join table_2 on table_1.category = table_2.category

Also consider a union instead