Left outer join with a NULL value


Left outer join with a NULL value


Can some one tell me, how to join 2 tables where one of my table join condition is having a null value.

For example, i took some sample values and what i need is given below as output.

Table A

A    B    C

a    1    2

?    2    3

c    4    4

Table B

A    D    E

b    1    2

?    2    3

a    4    4

When I left outer join A and B, I should get,


A    B    C    D    E

a    1    2    4    4

?    2    3    ?    ?

c    4    4    ?    ?

The join condition between table A and Table B is column A.

When I try to do in teradata,

select a.* , b.* from a left outer join b on a.A = b.A

The null row(2nd in the output) is being dropped off. How do I retain it in my output ??

Or to put it in another way, I want to join table A and B, but whenever table A contains a null value i should not get that dropped out because of this join condition. If no matching records found, it should still return that null row.

Thank you,


Prabhakar. T


Re: Left outer join with a NULL value

Here you go...

sel la.*, lb.* from la left join lb on (coalesce(la.a,'(novalue)')=coalesce(lb.a,'(novalue)'))

Teradata Employee

Re: Left outer join with a NULL value

Using your simplified example above, I get three rows returned as expected - though of course the answer set has six columns instead of 5 because column A.A and B.A are both included. Perhaps you need to post something closer to the actual query.