Archives of the TeradataForum

Message Posted: Tue, 04 Jan 2011 @ 07:34:00 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Chander,

This might due to the positioning of LEFT OUTER JOIN..ON/WHERE.

Teradata produces the LEFT OUTER JOIN first, and then applies the WHERE condition on the LEFT OUTER JOIN resultset, therefore, there will be huge different if the condition is not on the right place,

     SELECT a.*
     FROM table1 AS a
          LEFT OUTER JOIN table2 AS b
               ON a.col1 = b.col1
               AND b.col1 IS NULL

Is different from,

     SELECT a.*
     FROM table1 AS a
          LEFT OUTER JOIN table2 AS b
               ON a.col1 = b.col1
     WHERE b.col1 IS NULL

For detail, refer to the Case Study Example section in SQL Reference: Data Manipulation Statement

Hope it help.

Regards,

YB
T&O-BICC
DBS Bank Ltd

	<Prev	Next>		<<First	<Prev	Next>	Last>>