Using left outer join in SQL

chokka
chokka used Ask the Experts™
on
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 ??
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
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

chokkaStudent

Author

Commented:
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.

Commented:
try the example that I posted
can you post data type for the columns in your table? or table structure?
chokkaStudent

Author

Commented:
@Eugenez, Thanks !! My syntax is correct, but some conditional filtration filters the NULL Value column. Thank you for checking !!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial