how to join multiple tables in Teradata

Database
Enthusiast

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)

3 REPLIES
Enthusiast

Re: how to join multiple tables in Teradata

Thanks in advance

Enthusiast

Re: how to join multiple tables in Teradata

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.

Teradata Employee

Re: how to join multiple tables in Teradata

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!