Archives of the TeradataForum

Message Posted: Fri, 28 May 2004 @ 21:23:01 GMT

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

     INSERT INTO Temp
     SELECT * FROM table_X X LEFT JOIN table_Y Y
     ON X.i = Y.i AND X.j = Y.j AND X.k = Y.k
     WHERE Y.i IS NULL
     ;

Dieter, I'd run an explain on this one. You're absolutely right that this version of the query will return the same result, but it may not get a good plan. I have seen a case where the optimizer chose to do the outer join and then remove the inner join rows from the result by running the spool though a retrieve step. In almost all cases, this would be a slower plan than using an exclusion join. At some versions, the optimizer may be smart enough to use an exclusion join, but if it doesn't, you've got plan that will not perform well.

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