SQL92 INNER Join Syntax

[chillay]

Hello

Does anyone know why is it best to use the new SQL92 INNER join syntax rather than the SQL89 INNER join syntax?

I know it gives the same output and is processed the same but does anyone know why this syntax was created over using a simple WHERE clause in inner join queries?

Thanks

Chillay

 

[Golom]

In some SQL implementations the SQL89 syntax can produce incorrect results (although not in Access to my knowledge.)

The other issue is that something like

From T1, T2
Where T1.Fld = T2.Fld

does a cross join followed by a filtering of the resultant (often large) table.

In contrast

From T1 INNER JOIN T2 ON T1.Fld = T2.Fld

Can use indexes to produce the result much more efficiently.

An added benefit is that the INNER JOIN query is often updatable but the cross-join / Where version may not be.

 

[lameid]

What Golom did not say explicitly is that the cross join will return every possible combination of records between the two tables which is the subsequently filtered using the where clause. Using the Ansi 92 Syntax, Access is more likely to read the indexes first, perform the match and then read the associated records. This is much faster.

I suspect the real reason for the Inner Join Ansi 92 syntax is to provide a consistent way to perform joins in the same clause and help parsers understand how to build the query execution plan more easily. If the Joins are all in the same clause, so much the better.

And just in case there is someone out there wondering what we are talking about...

ANSI 92 SQL introduced joins in the from clause where as ANSI 89 SQL only supported inner joins by matching fields from one table to another in a where clause. Some products had a way to perform an outer join with otherwise ANSI 89 syntax, but those statements themselves are not ANSI 89 compliant. For anyone still lost, ANSI is a standards body.

 

[chillay]

This is great information. Although they return the same result I was wondering why the syntaz was upgraded.

Thanks!

Chillay

 

[Golom]

Although they return the same result ...

As I said ... they don't always.

You can be confident of the same results on an equal join condition (e.g. A.Fld1 [red]=[/red] B.Fld1), that's not necessarily true on a theta join (e.g. A.Fld1 [red]<[/red] B.Fld1).

The SQL92 syntax does give correct results in those cases but the SQL89 version may not.