FULL OUTER JOIN does not throw me NULLs where the key has no match (SQL Server)

1

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.

    
asked by Eduardo Huerta 27.07.2018 в 18:27
source

1 answer

1

If you remove the where tabla1.cuenta = '1' it will bring you the NULL . your where only conditions 1 value of the table, leaving out the others that contain value NULL .

EXPLANATORY IMAGE 'JOIN'

    
answered by 27.07.2018 / 18:37
source