Left outer join help

Database
Enthusiast

Left outer join help

Hi,

I have two tables called outer_table & inner_table

outer_table:

select * from outer_table order by 1;

 *** Query completed. 4 rows found. 2 columns returned. 

 *** Total elapsed time was 1 second.

     Emp_no  Emp_name

-----------  --------

          1  ram

          2  shyam

          3  tom

          4  dam

Inner_table:

select * from inner_table order by 1;

 *** Query completed. 4 rows found. 2 columns returned. 

 *** Total elapsed time was 1 second.

     Emp_no  status

-----------  ----------

          1  ALIVE

          2  DEAD

          5  ALIVE

          6  UNKNOWN

CASE 1:

SELECT  a.Emp_no,a.Emp_name,b.Emp_no,b.status FROM

Outer_table a LEFT JOIN Inner_table b ON a.Emp_no=b.Emp_no

ORDER BY 1;

 *** Query completed. 4 rows found. 4 columns returned. 

 *** Total elapsed time was 1 second.

     Emp_no    Emp_name       Emp_no   status

    -----------    --------             -----------  ----------

          1          ram                 1              ALIVE

          2          shyam             2              DEAD

          3          tom                 ?              ?

          4          dam                 ?              ?

CASE 2:

SELECT  a.Emp_no,a.Emp_name,b.Emp_no,b.status FROM

Outer_table a LEFT JOIN Inner_table b ON a.Emp_no=b.Emp_no

WHERE

b.status<>'UNKNOWN'

ORDER BY 1;

 *** Query completed. 2 rows found. 4 columns returned. 

 *** Total elapsed time was 1 second.

     Emp_no     Emp_name       Emp_no     status

    -----------     --------             -----------     ----------

          1          ram                   1               ALIVE

          2          shyam               2               DEAD       


 I am quite sure how CASE 1 is working but seems to be lost looking at CASE 2.Please explain.(May be i am overlooking some thing)              

17 REPLIES
Senior Apprentice

Re: Left outer join help

Logically the JOIN is processed first creating an intermediate result set which is then filtered by the WHERE. Any comparison to NULL results in UNKOWN, thus "WHERE b.status<>'UNKNOWN'" removes the previously created rows with NULLs.

When you read the 2nd Explain you'll notice that the optimizer is smart and removed the Outer join and replaced it by an Inner join.

Depending on the expected result set you probably need to

- move the condition into the ON-clause

- or add "OR b.Status IS NULL"

Do a global search in the manuals for "outer join case study", this chapter is a rewritten from an article in the old Teradata Magazine named "A lesson on Outer Joins. Learned the hard way" :-)

Dieter

Enthusiast

Re: Left outer join help

Any comparison to NULL results in UNKOWN. This is how teradata works? Or any logical reason behind this? Btw, i will have to go through manual too.

Thanks!

Senior Apprentice

Re: Left outer join help

There's a three-way logic in SQL: TRUE, FALSE, UNKNOWN.

Any comparison (other than IS NULL) involving a NULL results in UNKNOWN, which is quite similar to FALSE in most cases (but NOT UNKNOWN is still UNKNOWN).

This is not how Teradata works, it's how each and every relational DBMS works, it's one of the principles of the relational model.

Dieter

Re: Left outer join help

I have a question on another query.

CREATE TABLE t1

(

 x CHAR(7) ,

 y CHAR(7) ,

 z CHAR(4) );

CREATE TABLE sr.t2

(

 y CHAR(7) ,

 z CHAR(8)

 );

Replace VIEW v_t2

(

  y

  ,DEL_FLG

) AS

SELECT

   y

   ,CASE WHEN z < '20110530' THEN 'D'

    ELSE '0' END

FROM sr.t2;

insert sr.t1 ('1234567','1234','1234');

select

A.x, A.y,

B.y, B.z

FROM

sr.t1 AS A

LEFT OUTER JOIN sr.t2 AS B

ON ( A.y=B.y ) where

B.z<>'D';

Can you please let me know whether the query should return any rows.

How will be the order of evaluation here .

first will it evaluate join and then apply where clause or vice versa.

Note that the second table/view does not have any rows.

Can anyone help in this.

Senior Apprentice

Re: Left outer join help

Why don't you simply run the query?

The logical order of evaluation is always the same:

JOIN -> WHERE -> GROUP BY -> HAVING -> OLAP -> QUALIFY -> SAMPLE -> ORDER BY

Your select will return no rows.

Intermediate set after JOIN:

'1234567','1234',NULL,NULL

WHERE removes the row as NULL<>'D' results in UNKNOWN.

I don't know what you actually wanted to know, as you create a view, which is not used?

Did you mean to access the view instead of the table?

Then the result is still empty, logically the view's result set is created first and then used (just like a Derived Table).

Dieter

Enthusiast

Re: Left outer join help

Thanks dnoeth. Its pretty much clear now :-)

Enthusiast

Re: Left outer join help

SELECT  a.Emp_no,a.Emp_name,b.status FROM

Outer_table a LEFT JOIN Inner_table b ON a.Emp_no=b.Emp_no

WHERE

b.Emp_no<>6

ORDER BY 1;

 *** Help information returned. 20 rows.

 *** Total elapsed time was 1 second.

Explanation

--------------------------------------------------------------------------------

4) We do an all-AMPs JOIN step from .b by way of a RowHash

     match scan with a condition of (".b.Emp_no <> 6"), which

     is joined to .a by way of a RowHash match scan with a

     condition of (".a.Emp_no <> 6").  .b and

     .a are joined using a merge join, with a join condition

     of (".a.Emp_no = RETAIL_APP.b.Emp_no").  The result goes

     into Spool 1 (all_amps), which is built locally on the AMPs.  Then

     we do a SORT to order Spool 1 by the sort key in spool field1 (

     .a.Emp_no).  The size of Spool 1 is estimated with no

     confidence to be 235 rows (11,045 bytes).  The estimated time for

     this step is 0.04 seconds.

From above plan, Optimizer is first using filters and then doing merge join and seems to be deviating from logical order..JOIN--> WHERE. COuld you please explain this?

Re: Left outer join help

Actually my query returns a row in V2R6 base. In TD12, same query returnsno rows.

Can you please explain the V2R6 scenario.Is it giving incorret results in V2R6.

Senior Apprentice

Re: Left outer join help

@Mahs

It's a logical order. As long as the result set is the same the optimizer might apply filters in an earlier step.

@sravani

Which query, the one you posted or the one based on the view?

IIRC there was an issue with Case in Outer Joins in older releases.

Could you compare the explains if they are different?

Dieter