Teradata Join Syntax

Question

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?

Answer 1

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 :)

• Brian Powell: Thank you for this. What's the difference between the way I did it Select From WHERE and the way you do it Select From JOIN ON?
• dnoeth: @BrianPowell: There's no difference, but whenever you need Outer Joins you must use JOIN syntax, so you better get used to it (and it prevents some supid things like forgetting a join condition)
• ypercubeᵀᴹ: @dnoeth Oh, I somehow skipped that part. You seem to be right then!
• Brian Powell: @dnoeth well crap. I have to rewrite this ENTIRE thing then?
• ypercubeᵀᴹ: @BrianPowell is not that difficult. Start with 2 tables, then join a 3rd, etc.