0

I have a broad question regarding multiple joins in teradata.

For example, I have: table1, table2, table3, table4. I want to do an INNER JOIN for all of them

So:

SELECT a.*, b.*, c.*, d.* 
FROM table1 a 
JOIN table2 b ON a.acct_ID = b.acct_ID
JOIN table3 c ON a.acct_ID = c.acct_ID
JOIN table4 d ON a.acct_ID = d.acct_ID

So what's happening here is that every time a join takes place, a temporary table is created in this succession (((table 1+ table 2)+table 3)+table 4)?

Should the on statement be b.acct_ID = c.acct_ID and c.acct_ID = d.acct_ID in order for the statement to be efficient or does this not make a difference since a temporary table is created?

Tom
  • 7,640
  • 1
  • 23
  • 47
user2525645
  • 73
  • 3
  • 8
  • 3
    leave it up to the DB Engine. If you're not getting the desired performance then consider optimization. You tend to go nutz if you try to optimize before you know you have a problem. It's like trying to hit a moving requirements target... how good is good enough.... Now if you KNOW performance is bad, then look at execution plans and figure out what's best. – xQbert May 08 '15 at 21:15

2 Answers2

1

If you inner join multiple tables the order of joins is up to the optimizer, a query plan is developed based on knowledge (row counts, collected statistics, uniqueness). Only two sets of data can be joined in a single step, thus the optimizer will split multiple joins in binary relations and tries to find the least expensive plan. There's more details in the Teradata manuals: planning n-way joins (and the previous and following pages)

Quite impressive are the rapidly growing numbers of possible ways for optimizing joins, e.g. 18,000,000,000 different way to do a 10-table join: possible join orders

In case of Teradata the result of a join will be stored in a so-called "spool" (cached if it's not too large).

To see the actual plan you can "Explain" a query, either by adding Explain in front of the query or pressing F6 (in SQL Assistant and TD Studio), this returns a quite lengthy textual representation of the plan.

dnoeth
  • 59,503
  • 4
  • 39
  • 56
0

If you want to know what will happen during your select, check execution plan.

Community
  • 1
  • 1
sac1
  • 1,344
  • 10
  • 15