All Forums Database
Yunfei Zhao 8 posts Joined 08/15
28 Aug 2015
how to join multiple tables in Teradata

how to join multiple tables in Teradata

suppose I want to join 3 tables with the code below.

a left join b on a.key=b.key
  left join c on ?.key=c.key

where
a.col=10
b.col=20
c.col=30

I have two questions.
Question 1: I want to know what to put for the '?' in the code. Can I use either a, or b?

Question 2: If I move the where statement up(see the example below), does the code now run much faster?

a left join b on a.key=b.key
where
a.col=10
b.col=20

  left join c on ?.key=c.key
where c.col=30

(I heard that Teradata has an optimizer that can find the fastest way to run, so I might not need to move the where statement up)

Yunfei Zhao 8 posts Joined 08/15
28 Aug 2015

Thanks in advance

kirthi 65 posts Joined 02/12
28 Aug 2015

CREATE MULTISET TABLE SANDBOX.TEST1 ( C1 int , C2 int ) Primary index (c1) ;
CREATE MULTISET TABLE SANDBOX.TEST2 ( D1 int , D2 int ) Primary index (c1) ;
CREATE MULTISET TABLE SANDBOX.TEST3 ( E1 int , E2 int ) Primary index (c1) ;

INSERT INTO SANDBOX.TEST1 VALUES ( 1,1);
INSERT INTO SANDBOX.TEST1 VALUES ( 2,1);
INSERT INTO SANDBOX.TEST1 VALUES ( 1,1);

SEL * FROM SANDBOX.TEST1 
LEFT JOIN SANDBOX.TEST2 ON C1 = D1
LEFT JOIN SANDBOX.TEST3 ON D1 = E1;

Result
C1 C2 D1 D2 E1 E2
1  1  ?  ?  ?  ?

SEL * FROM SANDBOX.TEST1 
LEFT JOIN SANDBOX.TEST2 ON C1 = D1
LEFT JOIN SANDBOX.TEST3 ON C1 = E1;

Result
C1 C2 D1 D2 E1 E2
1  1  ?  ?  1  1





 
Question2 :
Again your result set will vary as Joins and Where conditions work differently.

Adeel Chaudhry 773 posts Joined 04/08
30 Aug 2015

SQL will run faster if you filter out the rows early in the process. Yes, optimizer will find and use the best way to run the given SQL.
 
It's more of a design choice how you want to use your limiting conditions. Consider following 2 scenarios:

- You need all rows from Table1 but only joining values from Table2
or
- You need all rows that joins between Table1 and Table2
 
You can achieve both the scenarios using multiple combitions of ON and WHERE clauses.
 
HTH!

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.