query

create table a
(
i varchar(10),
j varchar(11)
)
create table b
(
n varchar(10),
m varchar(11)
)
insert into a values(null, '1')
insert into a values(null, '2')
insert into b values(null, '3')
insert into b values(null, '4')
insert into a values('val1', '5')
insert into a values('val2', '6')
insert into b values('val3', '7')
insert into b values('val4', '8')
select * From a
inner join b on a.i = b.n
and a.i is null and b.n is null
hi i want to join table a and b and return the results
null, 1
null, 2
null, 3
null, 4
but i dont get this output. how do i do that?
thnx
ICHORThe query below should return the result you are after:
SELECT i, j
FROM a
WHERE i IS NULL
UNION
SELECT n, m
FROM b
WHERE n IS NULL
When you perform an INNER JOIN you only return the rows that satisfiy the
join of the first input with the second input. As a NULL is an unknown valu
e
NULL does not equal NULL hence the NULL records do not satisfy the join and
are not returned.
- Peter Ward
WARDY IT Solutions
"ichor" wrote:

> create table a
> (
> i varchar(10),
> j varchar(11)
> )
>
> create table b
> (
> n varchar(10),
> m varchar(11)
> )
> insert into a values(null, '1')
> insert into a values(null, '2')
> insert into b values(null, '3')
> insert into b values(null, '4')
>
> insert into a values('val1', '5')
> insert into a values('val2', '6')
> insert into b values('val3', '7')
> insert into b values('val4', '8')
>
> select * From a
> inner join b on a.i = b.n
> and a.i is null and b.n is null
>
> hi i want to join table a and b and return the results
>
> null, 1
> null, 2
> null, 3
> null, 4
> but i dont get this output. how do i do that?
> thnx
> ICHOR
>
>