What are the syntax transformation rules of Teradata’s “implicit JOIN”?

Given this schema:

create table t (i int, j int);
create table u (i int, j int);
insert into t values (1, 1);
insert into t values (2, 2);
insert into u values (1, 1);
insert into u values (2, 1);

It is possible in Teradata to write the following:

select * from t where t.j = u.j;

Which seems to implicitly add the u table to the table list. What’s actually being executed is this:

select t.* from t, u where t.j = u.j;

Both producing

|i  |j  |
|---|---|
|1  |1  |
|1  |1  |

Irrespective of whether using this feature is a good idea (and I think it isn’t), I can’t seem to find any reference to it in the documentation. The documentation seems to always use the latter syntax.

My questions are:

• What is this type of “implicit join” called? (Most people call the output version “implicit join”, because it does not use the ANSI JOIN syntax), so here, I’m referring to this “double-implicit join”…
• What are the exact syntactic rules for it?

Answer

Teradata development started 1979, in the 70s there was no Standard SQL, yet. One of the query languages was QUEL, designed by the well-known father of Postgres, Michael Stonebraker. It was implemented first in Ingres, the predecessor of Postgres (Post-Ingres used POSTQUEL initially). Teradata implementation was named TEQUEL and there was no FROM.

This is TEQUEL syntax and still works:

RETRIEVE t.j;

Your select mixes TEQUEL and SQL, even this works:

select t.* where t.j = u.j;

It contains enough information for the Parser to resolve table name and column name.

When the parser encounters an unknown table name it searches in the current default database and all the databases referenced by fully qualified objects in this query. See Unqualified Object Names

Teradata never dared to remove the old legacy syntax, I wish there was a flag to switch it off.