Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

OT SQL differences

by Arguile (Hermit)
on Oct 20, 2001 at 14:50 UTC ( #120229=note: print w/replies, xml ) Need Help??

in reply to Re: Re: Re: SQL/DBI
in thread SQL/DBI

My initial post, and more so this one go off on a bit of a tangent.

You're right, most DBMSs are pretty good at supporting a core set of syntax. The problem is the core is so incredibly limited. The reason I like using the example in the previous post (and below) is it demonstrates a very, very common task with no standard whatsoever.

  • MS SQL uses TOP (so does Informix IIRC)
  • Oracle uses ROWNUM and you specify offset and limit in the WHERE clause (easiest)
  • Postgres uses LIMIT and OFFSET or the short form LIMIT x, y
  • MySQL can only handle the short form of LIMIT

To do anything over trivial, and be even close to effecient, it's nigh on impossible not to get into a dialect of SQL.

Even 'core' SQL implementations can vary quite dramatically. A great example of this are the JOIN and UNION statements. Some DBMSs won't properly implement the various join types, which will result in the wrong results being returned. Others will perform an implicit sort on the PK for some strange reason. UNION can be even more subtle as some DBMSs perform an implicit DISTINCT on the result set. This might not seem to be a syntax change at first glance, but you can often get the correct (or at least de facto standard) result returned by altering the query for that DBMS.

While I agreed with most of your post, I think I had a bit of a knee jerk reaction to your statement about SQL. As a DBA who has to switch between all sorts of DBMSs it's a point of frustration to me :) While I doubt it's as bad as the browser wars of the 90's were for JS and HTML/CSS people it can seem close.

Replies are listed 'Best First'.
Re: OT SQL differences
by Aristotle (Chancellor) on Oct 20, 2001 at 19:43 UTC
    Last I checked, MySQL didn't even support joins, much less views or even *gasp* stored procedures.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://120229]
[Eily]: Easy, fetch the list of all the modules that are not on CPAN and remove the ones on Github
[Corion]: Eily: Sure, but I haven't written that yet ;)
[Eily]: wait, *keep* only the ones on Github, otherwise you'll still have an infinit list
[Eily]: Corion so until you write it, the module that lists the module that do not exist on CPAN should return itself
choroba has just learned how to tell a chemist from a blue collar
[Corion]: Eily: Wheee! ;))

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (9)
As of 2017-04-24 09:13 GMT
Find Nodes?
    Voting Booth?
    I'm a fool:

    Results (437 votes). Check out past polls.