Order in left join

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

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

   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"

Do you need more help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related lists.mysql.com Links announce benchmarks bugs certification cluster commits dotnet gui-tools internals java maxdb myodbc mysql mysqldoc packagers perl plusplus replication win32 Request more information about Pantek

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 Received on Wed Aug 1 19:15:38 2007