Unable join columns side by side from from different tables-CodePudding

I have two tables. Let's call it as t1 and t2. Below is the t1 table

t1(product_number, date, sales_before)
t2(product_number, date, sales_after)

t1 table contains 247 rows and t2 contains 264 rows. I'm trying to match the rows on product_number and date and did inner join.

select t1.*, t2.sales_after from t1
inner join t2 
on t1.product_number = t2.product_number
and t1.date = t2.date

But it's returning around 600 rows.

The t1 and t2 table samples are below

product_number    date        sales_before 
1                 2022-01-01  22
2                 2022-01-02  20
3                 2022-01-03  47

product_number    date        sales_after 
1                 2022-01-01  31
2                 2022-01-02  9
4                 2022-01-10  97

I'm expecting output like

product_number    date        sales_before  sales_after
1                 2022-01-01  22            31
2                 2022-01-02  20            9
3                 2022-01-03  47            NULL
4                 2022-01-10  NULL          97

Can anyone help me with this?

CodePudding user response：

Try this code

enter code here

 SELECT COALESCE(t2.product_number,t1.product_number) product_number
   ,COALESCE(t1.date,t2.date)date ,
   t1.sales_before ,
   t2.sales_before
FROM #t1 t1
full JOIN #t2  t2 ON t1.product_number = t2.product_number
AND t1.date = t2.date

CodePudding user response：

try it :

SELECT COALESCE(t2.product_number,t1.product_number) product_number
   ,COALESCE(t1.date,t2.date)date ,
   t1.sales_before ,
   t2.sales_after
FROM t1
full outer JOIN t2 ON t1.product_number = t2.product_number
AND t1.date = t2.date

CodePudding user response：

Wha i've done here is I've added coalesce and fuller join inplace of inner join. Below is the query

select 
  coalesce(t1.product_number, t2.product_number), 
  coalesce(t2.date, t2.date), 
  t1.sales_before, 
  t2.sales_after 
from 
  t1 
  full outer join t2 on t1.product_number = t2.product_number 
  and t1.date = t2.date