Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 18 Dec 2007 @ 15:51:42 GMT


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


Subj:   Re: A question on Left Join
 
From:   Diehl, Robert

All,

I don't understand the problem. Both Teradata and Oracle return the same answers in both cases. Simple way to think of it is the WHERE is applied after the join. This means filtering on the Inner table (one that is optional) will not return a row if it does not have a match, because the value is NULL.

Here are the results. First with Oracle then Teradata equivalent query.

This one has WHERE CLAUSE filter on the Left table in the LEFT OUTER JOIN

     1  SELECT *
        2  FROM ( SELECT 1 col1 FROM dual ) A left outer join (SELECT 2 col1 FROM
        3   dual) B
        4  ON ( A.col1 = B.col1)
        5*  WHERE A.col1 = 1
     SQL> /

            COL1       COL1
     ---------- ----------
               1


     SELECT *
     FROM ( SELECT 1 col1   ) A left outer join (SELECT 2 col1  ) B
     ON ( A.col1 = B.col1)
     WHERE A.col1 = 1;

       *** Query completed. One row found. 2 columns returned.
       *** Total elapsed time was 1 second.

     col1  col1
     ----  ----
         1     ?

Now with the WHERE CLAUSE filter on the Right table in left outer join

        1  SELECT *
        2  FROM ( SELECT 1 col1 FROM dual ) A left outer join (SELECT 2 col1 FROM
        3   dual) B
        4  ON ( A.col1 = B.col1)
        5*  WHERE B.col1 = 1
     SQL> /

no rows selected

     SELECT *
     FROM ( SELECT 1 col1   ) A left outer join (SELECT 2 col1  ) B
     ON ( A.col1 = B.col1)
     WHERE B.col1 = 1;

       *** Query completed. No rows found.
       *** Total elapsed time was 2 seconds.

Thanks,

Bob Diehl



     
  <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: 23 Jun 2019