null value in joins

[rneel]

i have two tables a1 and b1. they have a common column which is not having any constraints... they have null values... when i use a join between the two columns the null values are neglected... how do i include the null values in the joins...

need help
r -neel

[chandu031]

Hi,

If you are using an Inner join then obviously the NULLS are neglected( two nulls are not the same). If you want the NULLs also then you can do an OUTER join on the two tables. In your case it should be a FULL OUTER JOIN on the two tables.

SUPPOSE YOUR TABLES ARE LIKE THIS

A1:
COL1 COL2
------- -------
1 A
2 B
(null) C
(null) D

B1:
COL1 COL3
------- -------
2 b
3 c
4 d
1 a

and you are joining on col1 then a full outer join should return this result set:

COL2 COL3
------- -------
B b
A a
D (null)
C (null)
(null) d
(null) c


Hope this helps.........