[CLUE-Talk] SQL-99 inner joins

[Sean LeBlanc]

On 06-12 20:08, Jeffery Cann wrote:
> On Thursday 12 June 2003 07:01 pm, Sean LeBlanc wrote:
> > Does anyone have any thoughts on this?
> 
> Generally, the performance based on the SQL syntax is database-dependant.  
> Theoretically, it should not be if two equivalent SQL statements are 
> 'standard SQL'.  

I agree, but I've seen SQL Server folks claim this. As an unasked for
"feature", MS SQL Server admin converts to the join syntax whenever you
enter a query with an inner join, regardless of how you entered the query.
Yeah, this message is an MS luvfest. :)

 
> Unfortunately, many RDBMS vendors include extensions to SQL which have the 
> advantage of performance.  For example, Oracle includes a way to give hints 
> to the optimizer (aka 'planner' as Scott called it the other night).  These 
> hints tell Oracle to use an index that it may not choose on its own, and 
> hence the query performs faster.

Yeah, "extensions". Argh. Since SQL-92 didn't address all necessary issues,
everybody branched out so much so that even an outer join didn't have a
standard syntax. I think Informix used * or something? And others used a +
or something. Way back in 1994-1996, I worked at a shop where we had a C/S
app (written in PowerBuilder) that could run on any of Watcom, Informix, MS
SQL Server, Sybase, Oracle, Gupta[1] and they were porting to Access when I
left. I was in charge of the port to Informix as well as some of the port to
Oracle. 

Back then I was on top of a lot of the differences, but of course, these
things are moving targets (e.g., I wasn't even aware of SQL-99 until a year
or so ago).  The version of MS SQL Server we were using was 4.2, I think,
running on an OS/2 that still had MS' name on it. Our Oracle ran on Netware,
and Informix still didn't support a return character within a varchar field.
That was fun coding for that "feature". So it goes w/o saying that much of
my info about some of the databases is rusty. These days, I'm usually
writing for one target only, and that target is almost always MS SQL Server
(never my choice, of course). 

Here's where the problem comes in, though. My current target is MS SQL
Server, but I'm guessing that might change at some point in this app's
lifetime. I'm not sure, but it's a hunch I have. 

The problem with folks who've used MS and MS only is that they think that
"top" is standard sql since it's been around for so long in MS SQL Server
(and maybe Sybase?), and well, MS invented SQL, didn't they?  Or that
autoincrement is something that's implemented on all DBs. I guess I asked
because I want to avoid that mindset and write SQL that's as close as
possible to SQL-99 and only veer/tune when I must. At that shop I mentioned,
the company did have a book that specified SQL-92, but we drew WAY outside
those lines all the time... 

Thanks for your input. Do you have any fave SQL books? I have SQL for
Smarties, but haven't had a chance to look at it. I sold all my
Oracle-specific books a while back, and I never really owned a generic SQL
book until this one. 
 


[1] Well, according to the sales folks and the marketing materials, anyway.
We didn't have many resources devoted to testing (1-3 people at any one
time), and so we were constantly finding out about various nits after
something was delivered, or the night before a scheduled installation...

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