Motivation: Making some sense of SQL
to programmers with a hint of XML
SQL is really
like Latin versus the Romance languages - there is the standard and then all the variations. Each dialect of SQL has its own distinctive
syntax and customizations such that one can see the common origin but also has to suffer making sure of the differences in use. So
do not rely on being able to speak cross platform using any one of the dialects
like DB2, Informix, MySQL, Oracle, SQL Server or your favorite version of
Relational SQL. But SQL has been adopted as the lingua franca of object, XML, and other data sources as well. Here the variation in syntax can be even more pronounced. SQL syntax varies widely in each of the 4 major areas which we will now
look at in turn.
DML - Database Manipulation Language also varies among RDBMs vendors - but here standards are adhered to a bit more closely. However, again DML specifies how queries (Select statements, Cursor, Fetch, etc) and updates (Delete, Insert and Update commands) are to be done. The crimes are of omission as much as commission. For example, not all vendors implement all the different join conditions between tables and the syntax varies for the same commands among database vendors. Ditto for subqueries and their use with Update, Insert and Delete commands. The core of DML as queries using Select statements is reasonably uniform- after that all bets are off.
DCL - Database Control Language, alas again varies among vendors. DCL involves configuring and controlling the database - permision, roles, and referential integrity make up the guts of DCL. Grant, Check, Constraint, Primary Key, Foreign Key are some of the many commands and attributes associated with DCL. Here the standard permits variation in syntax and the vendors take advantage by implementing only their own favorite. Always be careful with DCL.
DSPL - Database Stored Procedure Language came to relational databases relatively late in the game - and thus the languages used for triggers, event handlers, and stored procedures are completely different among the database vendors. Oracle's PL/SQL is quite different even in statement syntax from SQL Server's Transact SQL which in turn differs again from DB2's Stored Procedure language. And of course given the underlying differences in DDL, DML, and DCL it is inevitable that the stored procedure languages would vary in content as well as syntax.
So the bottom line is though SQL has the very same intent, functionality,
and general purpose across database and other data vendors - users have had to get used to
working in dialects. SQL interoperability is problematical. Readers now should
understand one of the reasons why XML has risen in popularity - it provides
the Rosetta Stone of a standard, common syntax for data interchange not just
between relational databases; but structured datastores in general. Note in the articles on this website Relational SQL - the oldest and most broadly used will take precedence.
Top of Page Tutorials Home