I have two tables, as an example, TABLE1:
Cuenta Fecha Valor
1 201801 a
1 201802 b
1 201803 c
1 201804 d
2 201801 f
2 201802 g
2 201803 h
and TABLE2:
Cuenta Fecha Valor
1 201803 f
1 201804 d
1 201805 s
1 201806 f
2 201801 g
2 201802 s
2 201803 a
Then I do the following Query:
select tabla1.cuenta as cuenta_t1,
,tabla1.fecha as fecha_t1
,tabla2.cuenta as cuenta_t2
,tabla2.fecha as fecha_t2
from tabla1 as tabla1
full outer join tabla2 as tabla2
on tabla1.cuenta=tabla2.cuenta and tabla1.fecha=tabla2.fecha
where tabla1.cuenta = '1'
I'm interested in looking only at account 1 (actually the table I have is more complicated than this toy example, that's why I only look at account 1) ... the result is as follows,
Cuenta_t1 Fecha_t1 Cuenta_t2 Fecha_t2
1 201801 NULL NULL
1 201802 NULL NULL
1 201803 1 201803
1 201804 1 201804
When the result I expect is:
Cuenta_t1 Fecha_t1 Cuenta_t2 Fecha_t2
1 201801 NULL NULL
1 201802 NULL NULL
1 201803 1 201803
1 201804 1 201804
NULL NULL 1 201805
NULL NULL 1 201806
What is happening with the full outer join? Exchanging the order in which I choose the tables does not alter the result. Apparently he only gives me a number of rows equal to the largest number of rows between the two tables.
I would appreciate the help of this community,
Greetings.