For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joinly?

Question

For SQL, when did it start to be desirable to always use the words "Inner Join" instead of implicitly joining by:

select * from t1, t2 where t1.ID = t2.ID;

? Is it just for style or to distinguish between outer join or are there other reasons for it?

Answer 1

It became desirable when the new syntax was added to handle inconsistencies with outer join implementations.

Since combining outer and inner joins (and particular outer joins of both directions) with the old syntax is in many cases ambiguous, new syntax had to be created and implemented to allow the programmer to be 100% clear about the ordering and grouping of the join clauses.

It is, however, still perfectly legal to write the old-style inner joins by themselves, but the old-style outer joins are going away in all forms, and good riddance.

Answer 2

The INNER and OUTER JOIN syntax was formalized in the SQL-92 specification. In many database products such as MySQL and SQL Server, you can omit the "INNER" word from inner joins and simply use "JOIN". Similarly, many database products let you omit the word "OUTER" and simply use "LEFT JOIN" or "RIGHT JOIN" for outer joins. The old outer join syntax of *= or =* created ambiguities in many circumstances. Many products have or very soon will stop supporting the old outer join syntax.

Prior to the SQL-92 specification, the vendors each used their own syntax indicated an outer join. I.e., *= was not universal (I seem to remember someone using ?=). In addition, they did not implement the outer join in a universal way. Take the following example:

Table1
Col1    Col2
1       Alice
2       Bob

Table2
Col1    Col2
1           1
2           2
3           3
4           4

Select 
From Table1, Table2
Where Table2.Col1 *= Table1.Col1

The above query would generally yield:

1   1   1       Alice
2   2   2       Bob
3   3   Null    Null
4   4   Null    Null

Now try:

Select 
From Table1, Table2
Where Table2.Col1 *= Table1.Col1
    And Table2.Name = 'Alice'

On some database products, you would get:

1   1   1       Alice

On others you would get:

1   1   1       Alice
2   2   Null    Null
3   3   Null    Null
4   4   Null    Null

In short, it is ambiguous as to whether the filtering on the unpreserved table should be applied before or after the join.

Answer 3

It became desirable when it became possible...

The join keyword better describes the intention that the "classical" join. Also, when you have many joins in a query, the table to join and the condition to use appear next to each other instead of having all tables in one place, and all the conditions in another.

Answer 4

I believe it is the ANSI standard. It should return an error when the ON keyword is omitted, and (IMO) is a more declarative style.

Answer 5

ansi-92 standard hmmm that's a hard one i'm gonna go out on a limb and guess 1992