Using left outer join in SQL

select table1.column_id,
table1.column_type,
table2.column_name
from table1
left outer join table2
on table1.column_type = table2.column_numb

In Table 1

column_id column_type
1 NULL
2 NULL
3 300

In Table 2

table2_column_id column_numb column_name
1 100 ABC
2 200 DEf
3 300 GHI

Expected value

column_id column_type column_name
1 NULL NULL
2 NULL NULL
3 300 GHI

My query is not picking the NULL Values. What could be the reason ??

chokka

Asked: 2012-12-31

Advertise Here

1 Solution

LVL 43

Eugene ZCommented: 2012-12-31

looks good for me : see test code

check your tables datatype

use test 
go
create table  Table1 (column_id   int,               column_type int)

go
insert into Table1 
select 1 ,                                    NULL
union all select 2 ,                                     NULL
union all select 3 ,                                    300

go

create table  Table2 (table2_column_id  int,      column_numb int,  column_name varchar(50))

insert into table2
select 1 ,                                 100 ,                    'ABC'
union all select 2 ,                                  200 ,                   'DEf'
union all select 3    ,                              300 ,                    'GHI'

go
select    table1.column_id,
             table1.column_type,
             table2.column_name 
from     table1
left outer join table2
on         table1.column_type = table2.column_numb

Open in new window

chokkaAuthor Commented: 2012-12-31

You say my query works fine. i actually mentioned the sample data.

i implemented same logic query in my production database, but records with null value is filtered.