[CLUE-Talk] SQL-99 inner joins

[Sean LeBlanc]

On 06-12 20:57, Jed S. Baer wrote:
> 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.

Okay, no argument here. As I said, it sounds like complete nonsense to me,
too. I can't even see MS being this stupid about parsing... 
 
> I've never seen this come up on an Oracle mailing list or forum.

I could have sworn I saw one when I was Googling before (someone coming to
Oracle from MS griping that Oracle didn't have INNER JOIN syntax, IIRC), but
here's the sort of discussion I saw regarding what is said about INNER JOIN
as it pertains to MSSQLServer:

http://groups.google.com/groups?hl=en&lr=lang_en&ie=UTF-8&threadm=e%239TBnY0CHA.2452%40TK2MSFTNGP11&rnum=10&prev=/groups%3Fq%3Dinner%2Bjoin%2Bsql%2B99%26hl%3Den%26lr%3Dlang_en%26ie%3DUTF-8%26selm%3De%25239TBnY0CHA.2452%2540TK2MSFTNGP11%26rnum%3D10

The thread veers off a bit toward the end, and starts covering outer joins,
but you get the idea. Of course, if it did come up on an Oracle forum, it'd
be easy to miss considering the volume some of those get. :) 
 
> 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.

I've seen the ordering of the where clause make a big difference, too. 

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

Maybe I'm just being a stick in the mud, but I don't looking at it, either.
I grind my teeth whenever MS SQL Server's tool converts it, especially when
it's a giant complicated query I'm trying to test for validity. I could see
if you used an outer join in the "old" way and it converted, but inner join
conversion is very distracting. I guess the admin tool doesn't have the
paperclip, so they had to compensate somehow.

-- 
Sean LeBlanc:seanleblanc at americanisp.net  
http://users.americanisp.net/~seanleblanc/
Get MLAC at: http://sourceforge.net/projects/mlac/
The great question - which I have not been able to answer - is, "What does a 
woman want?" 
-Sigmund Freud