Order in left join

--Apple-Mail-1--602997650
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	delsp=yes;
	format=flowed

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

--Apple-Mail-1--602997650--