Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 18 Dec 2007 @ 12:52:13 GMT

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

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 :-)


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023