Hi Lukasz,
this is not a bug of MaxDB but a slight confusion about what you're
asking the DB to do.
You've specified two OUTER joins and defined the table that column is
LEFT from the equal sign to be the table where NULLs will be added if
fitting values are missing.
In this case it may be easier to rewrite the statement into the
"oracle"-notation:
select * from "Table1"
left join "Table2" on "Table1_ID"="Table2_IDTable1"
will be
select * from "Table1", "Table2"
where "Table2"."Table2_IDTable1" = "Table1"."Table1_ID"(+)
This means: get me ALL rows from Table2 and fill in NULLs if no
fitting row from Table1 is found.
Likewise
select * from "Table1"
left join "Table2" on "Table2_IDTable1"="Table1_ID"
will be
select * from "Table1", "Table2"
where "Table2"."Table2_IDTable1"(+) = "Table1"."Table1_ID"
This means: get me ALL rows from Table1 and fill in NULLs if no
fitting row from Table2 is found
Unfortunately you made up a testcase where both resultsets are the
same, since there is always one row found and one row to be filled
with NULLs.
But since this testcase only includes 1 page per table the effect on
performance due to the different approaches is neglectible.
If you enter some more data into one of the tables you can easily
spot the difference between the two statement:
truncate table "Table2"
//
-- insert some testdata
insert into "Table2" (select rowno, NULL from tables t1, tables t2)
//
-- reinsert your testrow
update "Table2" set "Table2_IDTable1"=10 where "Table2_ID"=21
-- for joins MaxDB need statistics!
update stat "Table2"
//
Now check again:
select * from "Table1"
left join "Table2" on "Table2_IDTable1"="Table1_ID"
still delivers 2 rows while
select * from "Table1"
left join "Table2" on "Table1_ID"="Table2_IDTable1"
returns more than 270.000 rows on my (nearly empty) testdb.
So to answer your question:
Yes, of course the order of fields in the ANSI JOIN LEFT/RIGHT
statement is significant. Otherwise terms like LEFT and RIGHT would
simply make no sense, wouldn't they?
Best regards,
Lars
