My initial post, and more so this one go off on a bit of a tangent.
in reply to Re: Re: Re: SQL/DBI
in thread SQL/DBI
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.