Archives of the TeradataForum

Message Posted: Tue, 18 Dec 2007 @ 10:09:18 GMT


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

Subj:		Re: A question on Left Join

From:		Victor Sokovin

Sorry to make a RTFM reply, but this is one case when it's very appropriate since it's complicated and even the manual calls it 'non- intuitive'. The bottom line is that placing a search condition on the inner table in the WHERE clause turns an outer join into an inner join. All this is covered in the SQL Reference pp 2-34 to 2-37, but I'm pasting in the most relevant section.

Jonathan, thank you for posting the excerpts from the manual. They are shocking, especially the last section:

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.

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.

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.

If Teradata fails to return the same result, I think it is a problem and I cannot accept any "explanation" (not in a form of a "manual" nor in any other form) for it.

Regards,

Victor


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

Attachments

Library

Quick Reference