 |
 |
Archives of the TeradataForum
Message Posted: Tue, 18 Dec 2007 @ 12:52:13 GMT
| Subj: | | Re: A question on Left Join |
| |
| From: | | Dieter Noeth |
Victor Sokovin wrote:
| | If a search condition on the inner table is placed in the WHERE clause, the join is logically equivalent to an inner join, even if you
explicitly specify the keywords LEFT/RIGHT/FULL OUTER JOIN in the query. The Optimizer always treats such a join as an inner join to simplify the
query, rewriting it to roll the entire complex process into a single step. | |
| | I simple terms, it means: we don't know how to execute your query so we make it simple and execute what we can. | |
No, it means: the optimizer is smart enough to recognize, that the result set is exactly the same for an Inner Join. Most DBMSes fail on
that...
| | Teradata folks must realize that they are not alone in this database market space. There are other database vendors and there are vendors
which try to build applications which can run on different RDBMS. And then there are users who are too busy to read all manuals on the planet and
compare them to each other. They paid their fees and need to do some work. Teradata must respect this and adjust to the market demands. | |
That's why Oracle added ANSI Join syntax and recommends now to use that instead of the old proprietary (*) syntax, which might returns a
different result set.
| | Anyway, this is a simple query on Oracle: | |
> SELECT *
> >FROM ( SELECT 1 col1 FROM dual ) A left outer join (SELECT 2 col1 FROM
> dual) B
> ON ( A.col1 = B.col1)
> WHERE A.col1 = 1
| | It still returns what we all think it should return: | |
> COL1 COL1_1
> 1
And this is how it should be.
And this is how it looks on Teradata, too.
So the OP's problem is a bug or he didn't post the exact query :-)
Dieter
| |
