RE: Problem with outer join and retrieving null column values

[Patrick Harms]

Some more info.   When doing the query under SQL Server, the result depends on where the outer join is specified. If specifed in the where clause using the *= syntax (ie where t1.col1 *= t2.col1 ), it behaves like SAPDB. However if specified on the FROM clause using the SQL92 syntax ( ie from table1 t1 left outer join table2 t2 on t1.col1 = t2.col1) it works like Oracle. Using the SQL92 syntax in SAPDB made no difference.   Regards Pat Harms -----Original Message----- From: Pat and Helen Harms [mailto:[EMAIL PROTECTED]] Sent: Saturday, 19 January 2002 17:44 To: [EMAIL PROTECTED] Subject: Problem with outer join and retrieving null column values Hi   I'm having a problem using left outer joins and retrieving the column values which are null.   The following SQL demonstrates the problem:   Create Table Table1    (Col1 Decimal(38) Not Null,     Col2 Decimal(38) Null,     Primary Key (Col1)    ) // Create Table Table2    (Col1 Decimal(38) Not Null,     Col2 Decimal(38) Null,     Primary Key (Col1)    ) // Insert Into Table1 Values( 1 , 1) // Insert Into Table1 Values( 2 , 2) // Insert Into Table2 Values( 1 , 1) // commit // select t1.col1, t1.col2, t2.col1, t2.col2 from table1 t1, table2 t2 where ( t1.col1 = t2.col1 (+) ) // select t1.col1, t1.col2, t2.col1, t2.col2 from table1 t1, table2 t2 where ( t1.col1 = t2.col1 (+) )   and ( t2.col1 is null) //   The first query returns two rows: 1,1,1,1 2,2,?,?   The second query returns: 1,1,?,? 2,2,?,?   I was expecting just one row: 2,2,?,?   Which is how Oracle behaves.   Can anyone explain to me what is happening here ? Can I get it to behave how I want ?   Regards Pat Harms