Newbie SQL Question about Linking Tables 1

[sabloomer]

I have been using SQL Server for the last couple years and now find myself having to write queries in Teradata SQL Assistant.

I have been looking all over the internet, but can't seem find an answer to why so many examples I see have a "From" clause that contain no join information.

I keep seeing queries like the the one below..

Select A.Field1, B.Field2
FROM A,B
WHERE A.Key=B.Key

Is this doing some type of cross-join and using the where clause limit the records? Is there a default join type? (Inner/Outer) Is there a performance boost by using this syntax?

Thank you in advance!

sabloomer

 

[Gruuuu]

It's just a traditional join as opposed to ANSI join. I see a lot of people using traditional joins as well, to no explanation.
I've not noticed any performance advantage to using Traditional joins in Teradata.

Stick with the ANSI, but beware the (awful) syntax; you'll still gain a lot of value out of the well-formated .sql files.

 

[sabloomer]

Gruuuu,

That is a big help. The big question is performance, and if there is no advantage I am going to stick with what I am more comfortable with.

Just for my knowledge, Did I provided an example of a "traditional" inner join? Is there such thing as a "traditional" outer join?

Thanks Again!

sabloomer

 

[Gruuuu]

Yes, that is a traditional inner join (JOIN or INNER JOIN in ANSI).

...I do not even remember the syntax for traditional outer joins in Teradata.

Can't even find it in the developer reference

For most other DBMS environments, it's
SELECT A.Fiel1, A.Field2
FROM A,B
WHERE A.key = B.key (+);
...
WHERE A.key (+) = B.key;

But the real fun comes in with a full outer join. You have to UNION a LEFT OUTER and a RIGHT OUTER query.

So, yes, ANSI is ...probably preferable.

 

[sabloomer]

Gruuuu,

Thank you for clearing that up, it was a big help.

sabloomer