difference between explicit inner join and implicit

On Mar 31, 1:02 pm, YZXIA wrote:
> Is there any difference between explicit inner join and implicit
> inner join

The execution plan for both queries is the same, so that suggests to
me that they are functionally identical.

>> 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.

> Is there any difference between *explicit inner join and implicit
> inner join

Technically no, but it's a good idea to make it a habit to use the
explicit inner join. It is considered the standard nowadays, is
easier to read and debug, and is consistent with the OUTER JOIN
syntax.

--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

>> Like this, you mean? <<

No, try this:

SELECT ..
FROM A, B, C
WHERE A.x BETWEEN B.y AND C.z;

Versus:

SELECT ..
FROM A
INNER JOIN
B
ON A.x >= B.y
INNER JOIN
C
ON C.z <= A.x;

The "between" is lost in what typographers call the law of proximity
because things are split into binary operators. Likewise, look around
this newsgroup for

a = x1 OR a = x2 or a = x3

versus
a IN (x1, x2, x3)

The set of matches is lost in the binary operators. My favorite
example of this mindset is

x1 = 0 OR x2 = 0 OR x3 = 0
versus
0 IN (x1, x2, x3)

Because the binary mindset is a left-to-right English based ordering
that says "x is zero" and "zero is x" when coding. I used to run into
this difference with Arab and Asian students who did not have this
cultural baggage.

>> Technically no, but it's a good idea to make it a habit to use the explicit inner join. It is considered the standard nowadays, <<

Not when I was on the Standards Committee; did you join later than
me?

>> is easier to read and debug, <<

NO, it isn't; have you seen any of the human factors research? The ON
clauses can be spread so far from the matching tables debugging time
increases. Multiple parameter predicates like BETWEEN and IN are
split and their higher level meaning is lost.

But it looks like ACCESS and has a nice binary operator feel that
procedural programmers like.

>> and is consistent with the OUTER JOIN syntax. <<

Yes, that is the reason it exists.

On Tue, 1 Apr 2008 07:01:14 -0700 (PDT), --CELKO-- wrote:

>>> Like this, you mean? <<
>
>No, try this:
>
>SELECT ..
> FROM A, B, C
> WHERE A.x BETWEEN B.y AND C.z;
>
>Versus:
>
>SELECT ..
> FROM A
> INNER JOIN
> B
> ON A.x >= B.y
> INNER JOIN
> C
> ON C.z <= A.x;
>
>The "between" is lost in what typographers call the law of proximity
>because things are split into binary operators.

Hi Joe,

And both versions hide the actual relationship between B and C.

SELECT ..
FROM B
INNER JOIN C
ON C.z >= B.y
INNER JOIN A
ON A.x BETWEEN B.y AND C.z;

Now we have the "between", *and* the relationship between B and C is
shown explicitly. Looks like a winner to me!

(Though I have to admit that I'd be hardpressed to come up with an
example that makes it even vaguely believeable that a query such as this
would ever show up in the real world).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

>> Now we have the "between", *and* the relationship between B and C is shown explicitly. Looks like a winner to me! <<

No, no! What you have done is hide redundancy which would have been
clearly shown in a simple WHERE clause. I don't think I am the only
guy who puts all the WHERE clause predicates into a tool to sort them
to see exactly what predicates go to which tables.


>> (Though I have to admit that I'd be hard pressed to come up with an example that makes it even vaguely believable that a query such as this would ever show up in the real world). <<

LOL! We have seen much worse in this newsgroup!

If anyone is willing to help a novice,

is there any difference between the JOIN operator and the INNER JOIN
operator?

For example, in a sample database, I get identical results when I run

SELECT title, name
FROM books JOIN publisher
USING (pubid)

and

SELECT title, name
FROM books INNER JOIN publisher
USING (pubid)

Thanks,

Neil

*** Sent via Developersdex http://www.developersdex.com ***