Re: difference between explicit inner join and implicit

Re: difference between explicit inner join and implicit

am 31.03.2008 20:49:17 von Joe Celko

>> Is there any difference between explicit [sic: infixed] inner join and implicit inner join <<

In theory, yes; in practice, no. The SQL-92 Standard says that the
infixed join notation is executed in left-to-right order in the FROM
clause. But the optimizer is free to re-arrange the query as long as
the results are the same.

This does not make a difference with INNER JOINs, but it is vital with
OUTER JOINs.

There is a good story about this proposal. We needed the OUTER JOIN
syntax; the proprietary methods in Oracle, SQL Server, Sybase,
Informix, etc. were all screwed up. But after we had defined the
OUTER JOIN, it was easy to extend the paper to INNER JOIN, UNION JOIN,
NATURAL JOIN, etc. (most of which nobody implements).

The rationale after the fact was that a product could have an option
to force an order of execution with the infixed notation. Since SQL
products now have some kind of optimizer, it is not really useful.

The real difference is in the mindset of programmers. Those that
write with infixed notation thing in terms of a linear sequence of
joins, as if they were limited to simple binary Theta operators. The
programmers that use the older notation will use BETWEEN, IN () and
other predicates that work with multiple terms.

Re: difference between explicit inner join and implicit

am 01.04.2008 05:12:02 von Ed Murphy

--CELKO-- wrote:

> The real difference is in the mindset of programmers. Those that
> write with infixed notation thing in terms of a linear sequence of
> joins, as if they were limited to simple binary Theta operators. The
> programmers that use the older notation will use BETWEEN, IN () and
> other predicates that work with multiple terms.

Like this, you mean? And are you recommending or deprecating it?

select a.x, b.y
from table1 a
join table2 b on a.x between b.y and 500