Motivation: Making some sense of SQL to programmers with a hint of XML
Credit: Imagenation


It is an irony that SQL has become the dominant query language for database processing. SQL has gained that position on the basis of its mathematical and set theoretic foundations and the fact that it has had a long history of standardization. As well, the steady improvements in reliability, performance and security have secured a strong position for RDBMS-Relational Database Management Systems like DB2, MySQL, Oracle, etc which use SQL as their primary access mechanism. But note that despite a long history of standardization, setting a common language for RDBMS has not necessarily been outstandingly successful. If you will note in the diagram to the left, interpreters is pluralized because there is so much variation in SQL in both relational and other data sources use of "their SQL".

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.

First, there is the DDL-Data Definition Language of SQL. As one might guess, DDL is about defining the structure and contents of a relational database. Relational databases were the first DBMS which adhered to a simple principle - the database, its design stuctures, and related system tables (the collection of database schemas, catalogs, stored procedures and other structural elements are sometimes referred to collectively as the database's metadata) had to use exactly the same designs as were allowed any other RDBMS table - no more, no less. Despite this and 11 other Codd Rules plus many years of work by the ANSI committees - the DDL for each RDBMS vary quite widely. And the reason for this state of affairs is very simple.

DDL defines the mapping of database to physical hardware and devices. Each RDBMS vendor considers this their source of competitive advanatge - and so the mapping of database to physical device is one arena where the specific syntax of the DBMS vendors varies widely. The basic syntax of Create Table, Alter Table, Drop Table, Create View, Create User, etc are similar - its the details which vary and quite substantially. In the case of object, XML, and other data sources this variation in structure is a)even more pronounced and b)subject to wider variation in basic structure and datatypes- hence even greater differences in "DDL".

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