Left Join doesn't work properly

Teradata Studio
Enthusiast

Left Join doesn't work properly

Hello,

I've been doing some tests and teorically, if you put in a left join between two tables a condition in the "on" part that only affects to the left table, that condition will only affect to the join, right? I mean, the test of records from the left table will appear not being joinned with the right table. Isn't it?

 

On the other hand I'm doing more or les complex left join between two tables but this time the condition on the "on" that only affects to the left table is working like a "where" and the rest of the records from the left table don't appear in the result.

 

Maybe I'm doin something wrong or I'm getting crazy... If someone could have a look and let me know what is happening I would be grateful:

 

CREATE TABLE NQ_CATASTRO_NEW.TB_CATASTRO_INMUEBLES_V7A AS
select
cast(T15.COD_AGR_INMUEBLES || T14.NUM_ORDEN_CONST as VARCHAR2(42)) as COD_AGR_INMUEBLES, -- cast(rpad(T15.COD_AGR_INMUEBLES || T14.NUM_ORDEN_CONST, 42, '@') as VARCHAR2(42)) as COD_AGR_INMUEBLES,
T15.COD_AGR_PORTALES,
[...]
from
NQ_CATASTRO.TB_CATASTRO_INMUEBLES T15
left join
(select * from NQ_PRUEBAS.CATASTRO_T14 where COD_DESTINO = 'V') T14
on
(T15.COD_PROVIN || T15.COD_MUNICI_DGC || T15.REF_CAT_INMUEBLE_SIN_CTR) = (T14.COD_PROVIN || T14.COD_MUNICI_DGC || T14.PARCELA_CATASTRAL || T14.NUM_ORDEN_FISCAL)
and T15.BLOQUE || T15.ESCALERA || T15.PLANTA || T15.PUERTA in ('TODOS', 'SUELO', 'TODAS', 'PARTE', 'VUELO', 'COPRO', 'RUSTICA', 'SOLAR', 'TODO')
and T15.CLAVE_GRUPO_BIENES = 'V'
where
T15.COD_PROVIN = '28' and T15.COD_MUNICI_INE = '006' and T14.COD_PROVIN = '28' and T14.COD_MUNICI_INE = '006'
; commit;

Why in the table result I'm only getting the records where the "on" condition is matching? The rest don't even appear with the right table column to null...

Thanks and regards.

 

Juan

Accepted Solutions
Teradata Employee

Re: Left Join doesn't work properly

Hi Juan,

 

It's because you've put filter into the WHERE clause on t14, thus transforming your left join into an inner join.

Here are some examples :

-- Classic inner join (inner being an optional keyword)
      from table1 as t1
inner join table2 as t2 on t2.id = t1.id

-- Classic left join (outer being an optional keyword)
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id

-- With filters, this one is ok because you filter on t1
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id
   where t1.col = 'A'

-- The filter is "killing" the outer join
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id
   where t2.col = 'A'

-- You should write either one of those :
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id
   where t2.col = 'A' or t2.col is null

      from table1 as t1
 left join table2 as t2 on t2.id = t1.id and t2.col = 'A'

-- I prefer the later syntax in case t2.col is a nullable column.
1 ACCEPTED SOLUTION
1 REPLY
Teradata Employee

Re: Left Join doesn't work properly

Hi Juan,

 

It's because you've put filter into the WHERE clause on t14, thus transforming your left join into an inner join.

Here are some examples :

-- Classic inner join (inner being an optional keyword)
      from table1 as t1
inner join table2 as t2 on t2.id = t1.id

-- Classic left join (outer being an optional keyword)
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id

-- With filters, this one is ok because you filter on t1
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id
   where t1.col = 'A'

-- The filter is "killing" the outer join
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id
   where t2.col = 'A'

-- You should write either one of those :
      from table1 as t1
 left join table2 as t2 on t2.id = t1.id
   where t2.col = 'A' or t2.col is null

      from table1 as t1
 left join table2 as t2 on t2.id = t1.id and t2.col = 'A'

-- I prefer the later syntax in case t2.col is a nullable column.