[CLUE-Talk] SQL-99 inner joins

[Jed S. Baer]

On Thu, 12 Jun 2003 19:01:17 -0600
Sean LeBlanc <seanleblanc at americanisp.net> wrote:

> I've heard people in person and online claim that to be SQL-99 compliant
> for inner joins, the JOIN ON syntax must be used (vs. just doing it in
> the where clause). But I've seen people also refute that claim and that
> BOTH styles are SQL-99. Not being able to find an online reference, I
> haven't been able to verify either claim.
> 
> It's usually MS SQL Server folks that have made the claim that JOIN ON
> is"faster". I think that's only on the parse - which is usually
> negligible.  
> 
> Does anyone have any thoughts on this?

Rubbish!

Think about it. Well, OK, I can speak only about Oracle. In the many
online articles (most from Oracle employees), books (including from Oracle
Press), and the Oracle documentation, I can't remember even one occurance
of the "JOIN ON" syntax for inner joins, unless it was specified in the
syntax tree for completeness. Hmmm, well, for that matter, I mostly
remember reading references to SQL-92 compliance, but even there, the std.
syntax, IIRC, is " ... INNER JOIN {table} ON ... ", so I think that
qualifies for your question. Anyway, why in the world would Oracle not
optimize the performance of their parser for the overwhelmingly most
common join syntax in use for Oracle databases.

I've never seen this come up on an Oracle mailing list or forum.

However, I sorta expect that in other databases, the difference, if any,
is negligible.

And yes, if there were a speed difference, it would almost have to be in
the parser, because you'd expect the query optimizer (or "planner") to
select the same execution plan for the same join, all other things being
equal. However, I will state that there were cases, using Oracle, using
the old rule-based optimizer, where sometimes the listing order of joins
would make a difference. But in theory, there shouldn't be any difference
between specifying "JOIN ON" and "WHERE column = column". But I wouldn't
expect a big difference in parsing either.

Another area where there is potential for a speed difference is in the
generation of the execution plan. It's possible, for some queries, for
this step to be a significant fraction of the total query execution time,
which is why Oracle (and I assume other databases) cache the "compiled"
form of the query, and reuse that code if the query is re-executed.

Having cut my relation teeth on Ingres (v. 2.17 -- a long time ago), and
given the amount of SQL I've written in Oracle, the ANSI std. query syntax
always looks strange, and overly verbose.

jed
-- 
I wouldn't even think about bribing a rottweiler with a steak that
didn't weigh more than I do. -- Jason Earl