Hi Lars, thanks for help, but:
1. Null value is only for example. You can exchange it for 0. 2. In MSAccess (linked tabled from MAXDB) query gets two equal results. In 99% cases i use correct clause but few days ago i have been in 1% of cases, by mistake. By the way, I have checked this: exchange null to 9 in my example and query this: select * from "Table1" left join "Table2" on "Table1_ID"="Table2_IDTable1"+1 Works well :) You can not get this syntax by simple mistake, so it is save. It looks almost lilke matematical condition, but query select * from "Table1" left join "Table2" on "Table1_ID"-"Table2_IDTable1"=1 generates error. I know that it is no condition but it behaviour looks like a condition. Help in maxdb and google does nothing, so i post this question to all. For me it is list of two fields, witch values must be equal. But syntax allows variant cases. Question was: all of above are bugs or not? If not, where is a documentation fot it? Best regards, Lukasz. Lars Breddemann wrote: > 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 > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
