Hi,

Something with joins doesn't work the way I expect. In this query :

select a.field1, b.field2
from table1 a
inner join table2 b  on (a.idx=b.idx)
where a.field3=Something
and b.field4=Something
order by a.field1

it doesn't matter if I use an inner, left outer or right outer join - the result set is
always the same, like the one I would exspect from an inner join. The only thing that
differs is the execution time.

A left outer join should show me the complete table1 (minus where filter) and fill field2
with nulls where no corresponding table2 rows are present, but instead it shows only rows
whose idx exist in table2.

Heiko

Heiko,

<< it doesn't matter if I use an inner, left outer or right outer join - the
result set is always the same, like the one I would exspect from an inner
join. The only thing that differs is the execution time.

A left outer join should show me the complete table1 (minus where filter)
and fill field2 with nulls where no corresponding table2 rows are present,
but instead it shows only rows whose idx exist in table2. >>

Actually, to do that, you would need this query:

select a.field1, b.field2
from table1 a
left outer join table2 b on (a.idx=b.idx) and b.field4=Something
WHERE a.field3=Something
order by a.field1

or this query:

select a.field1, b.field2
from table1 a
inner join table2 b  on (a.idx=b.idx)
where a.field3=Something
and (b.field4=Something or b.field4 IS NULL)
order by a.field1

Otherwise, the:

b.field4=Something

expression will cause any rows with NULL values generated by the LOJ to be
excluded since a NULL value cannot possibly equal anything.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

*arrrgh*

Thanks a lot. I did this wrong for an eternity, and today was the first time I actually
recognised it doesn't produce the results I always thought it would.

Heiko