Teradata Join Syntax

join;teradata

I'm having a hard time doing what I want to do here, as SQL (and Teradata SQL on top of that) is not my main forte.

I have two queries that I want to join together. I want everything that returns from this query:

SELECT                
  a01.fieldOne                          Q_One,  
  a01.fieldTwo                          Q_Two,  
  a02.fieldThree                        Q_Three    
FROM   
  db.table_one                          a01, 
  db.table_two                          a02  
WHERE 
  a01.fieldKey                          = a02.fieldKey;

This runs fine, but I need to ALSO see the results from this query at the same time:

SELECT                
  a03.fieldFour                         Q_Four    
FROM   
  db.table_one                          a01,
  db.table_two                          a02,
  db.table_three                        a03 
WHERE 
  a01.fieldKey                          = a02.fieldKey and
  a01.fieldKey                          = a03.fieldKey and
  a02.fieldKey                          = a03.fieldKey;

Right now, I have the query written like this:

SELECT                
  a01.fieldOne                          Q_One,
  a01.fieldTwo                          Q_Two,
  a02.fieldThree                        Q_Three,
  a03.fieldFour                         Q_Four    
FROM   
  db.table_one                          a01,
  db.table_two                          a02,
  db.table_three                        a03 
WHERE 
  a01.fieldKey                          = a02.fieldKey and
  a01.fieldKey                          = a03.fieldKey and
  a02.fieldKey                          = a03.fieldKey;

I understand what I'm doing wrong – I'm using an inner join, so I'm returning only results where all the fieldKey fields match between a01, a02 and a03, severely limiting my results because table_three only has a handful of items in it, so most of the items that exist in table_one and table_two do not exist in table_three. I want the items from table_three that don't match, as well as the ones that do match – I'm not sure how to do this though. I believe it's a LEFT OUTER JOIN – so I take everything from the "left", and join to it some things from the "right"? I don't know, I'm guessing here…

I'm not sure how to write this code, especially in Teradata which seems to have very sparse information around the web.

Update

The reason I asked about using WHERE vs using JOIN ON is because the actual query I have is much more complicated and I'm not sure how to use the 'JOIN ON' logic with THIS many tables:

SELECT                 
  a119.a                a,  
  a11.b                 b,
  a11.c                 c,
  a18.d                 d,
  a18.e                 e,
  a11.f                 f,
  a11.g                 g,     
  a11.h                 h,
  a11.i                 i,
  a11.j                 j,  
  a12.k                 k,  
  a124.l                l,
  a18.m                 m,
  a18.n                 n,  
  a18.o                 o,
  a111.p                p,
  a110.q                q,          
  SUBSTR(a50.r,8,2)     r,
  a50.s                 s,
  a127.t                t,
  a127.u                u,
  a127.v                v,
  a127.w                w
FROM   
  db.table1             a11, 
  db.table2             a12, 
  db.table3             a13,
  db.table4             a15,
  db.table5             a18,  
  db.table6             a50,
  db.table7             a51,
  db.table8             a52,
  db.table9             a53,
  db.table10            a54,
  db.table11            a110,
  db.table12            a111,      
  db.table13            a114, 
  db.table14            a115, 
  db.table15            a116, 
  db.table16            a117,  
  db.table17            a119, 
  db.table18            a120, 
  db.table19            a121, 
  db.table20            a122,
  db.table21            a123, 
  db.table22            a124,
  db.table23            a125,
  db.table24            a126  
WHERE 
  a11.someKey               = a12.someKey and
  a11.someKey               = a125.someKey and
  a11.someKey               = a15.someKey and
  a125.someKey              = a126.someKey and
  a11.someKey               = a18.someKey and  
  a11.someKey               = a13.someKey and 
  a11.someKey               = a13.someKey and 
  a18.someKey               = a50.someKey and
  a50.someKey               = a51.someKey and
  a51.someKey               = a52.someKey and
  a52.someKey               = a53.someKey and
  a53.someKey               = a54.someKey and
  a11.someKey               = a114.someKey and 
  a11.someKey               = a119.someKey and
  a12.someKey               = a110.someKey and
  a12.someKey               = a111.someKey and    
  a114.someKey              = a115.someKey and 
  a11.someKey               = a116.someKey and 
  a116.someKey              = a117.someKey and 
  a11.someKey               = a13.someKey

The above query (when populated with actual field names and key names) runs just fine, because the keys in question exist in ALL of the tables.

What I expect to see when joining in that LAST table (a127) is blank fields (or null fields) for 97% of them. Is there no way for me to just put brackets or something around THIS query, run it, then join the results to the second query?

Best Answer

Teradata uses Standard SQL join syntax like all other DBMSes.

First of all you should stop using the old syntax FROM tabl1, table, table3, switch to explicit JOIN instead.

I don't know about your data, so I'm not sure if you can simply combine both queries into one, but if it's possible it should be like this:

SELECT                
  a01.fieldOne                          Q_One,
  a01.fieldTwo                          Q_Two,
  a02.fieldThree                        Q_Three,
  a03.fieldFour                         Q_Four    
FROM db.table_one                       AS a01
JOIN db.table_two                       AS a02
  ON a01.fieldKey = a02.fieldKey
LEFT JOIN db.table_three                AS a03 -- your guess is correct :) 
  ON a01.fieldKey = a03.fieldKey 
 and a02.fieldKey = a03.fieldKey;

Check if the number of rows returned matches your 1st query.

Edit:

As you actually join a lot of tables you might try to keep the existing implicit joins and only add a LEFT join for the last table (I'm not sure if this returns the correct result):

db.table23            a125,
db.table24            a126 
LEFT JOIN   a127
ON ....

And next time start using ON-syntax from the beginning :)

Related Question