Archives of the TeradataForum

Message Posted: Wed, 24 Jul 2002 @ 18:06:17 GMT

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

Michael,

You want to something real interesting try the same SQL in your example on Oracle and SQL Server. I bet you get three different results.

Here's what we have determined. "When" the Where condition is applied will affect the results. If the where condition is placed in the FROM clause, the filter is applied during the JOIN. If the JOIN is an OUTER JOIN this can have interesting consequences. If the WHERE condition is applied after the OUTER JOIN, the OUTER JOIN is neutralized (fewer rows, in your case 85 rows).

What is an OUTER JOIN on a column limited to a list in an IN clause? Should the OUTER JOIN even be performed?

After much discussion, we don't necessarily think this is a problem. Moving Where conditions from the WHERE clause to FROM clause will yield different results, but neither are necessarily wrong.

The bigger problem we have is that Oracle, SQL Server, and Teradata will all yield different results with the "SAME" SQL. We have figured out how to control what is happening, but I don't know what the intention is.

(Good to hear from you again.)

John

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