title: Multiple Joins in Teradata SQL - Faster to Use Subqueries or Temp Tables?
body: <p>I am writing SQL for Teradata.  I need to use joins to connect data from multiple tables.  Is it typically faster to use subqueries or create temporary tables and append columns one join at a time?  I'm trying to test it myself but network traffic makes it hard for me to tell which is faster.</p>
  
  <p>Example A:</p>
  
  <pre><code>SELECT a.ID, a.Date, b.Gender, c.Age
  FROM mainTable AS a
  LEFT JOIN (subquery 1) AS b ON b.ID = a.ID
  LEFT JOIN (subquery 2) AS c ON c.ID = a.ID
  </code></pre>
  
  <p>Or I could...</p>
  
  <p>Example B:</p>
  
  <pre><code>CREATE TABLE a AS (
   SELECT mainTable.ID, mainTable.Date, sq.Gender
   FROM mainTable
   LEFT JOIN (subquery 1) AS sq ON sq.id = mainTable.ID
  )
  CREATE TABLE b AS (
    SELECT a.ID, a.Date, a.Gender, sq.Age
    FROM a
    LEFT JOIN (subquery 2) AS sq ON sq.id = a.ID
  )
  </code></pre>
  
  <p>Assuming I clean everything up afterward, is one approach preferable to another?  Again, I would like to just test this myself but the network traffic is kind of messing me up.</p>
  
  <p><strong>EDIT:</strong>  The main table has anywhere from 100k to 5 million rows.  The subqueries return a 1:1 relationship to the main table's IDs, but require WHERE clauses to filter dates.  The subquery SQL isn't trivial, I guess is what I'm trying to convey.</p>