Archives of the TeradataForum

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

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

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