merge tow querys in sql

I have a issuse in sql query.

I have 2 select:

The first one showing:

 columnA|columnB
   A    |2
   B    |3
   D    |5

The other one showing:

columnA|columnC
     A   |1
     B   |5
     C   |7

I’m tying to merge this tow query,

this is my query:

with 
cte1 as (
select  A,B  from table1 group by A
),
cte2 as (
   select  A,C from table2  group by A
)
select c1.A, c1.B, c2.C
from cte1 c1 left join cte2 c2 
on c2.A = c1.A
order by c1.A asc

The problem is when I’m using “Left join” the result is :

ColumnA|ColumnB|ColumnC
A      |2      |1
B      |3      |5
D      |5      |null

It’s not showing the value C for the second table,

And whene I’m using “right join” the result is :

ColumnA|ColumnB|ColumnC
A      |2      |1
B      |3      |5
C      |null   |7

It’s not showing the value D for the first table,

The result should be like that:

 ColumnA|ColumnB|ColumnC
    A      |2      |1
    B      |3      |5
    C      |null   |7
    D      |5      |null

any solution please

Answer

Use full outer join

select coalesce(table1.ColumnA,table2.ColumnA) as ColumnA,
       ColumnB,ColumnC  
from table1 full outer join table2 on table1.ColumnA=table2.ColumnA