![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
![]() |
Tue, Sep 2 2008 12:05 PM | Permanent Link |
Heiko Knuettel | 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 |
Tue, Sep 2 2008 12:38 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Tue, Sep 2 2008 1:31 PM | Permanent Link |
Heiko Knuettel | 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 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |