All Forums Database
09 Nov 2012
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


mohan.mscss 31 posts Joined 04/11
10 Nov 2012

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

Fred 1096 posts Joined 08/04
11 Nov 2012

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.

You must sign in to leave a comment.