SQL in a Nutshell, 3rd Edition

SQL Dialects

The constantly evolving nature of the SQL standard has given rise to a number of SQL dialects among the various vendors and platforms. These dialects commonly evolve because a given database vendor’s user community requires capabilities in the database before the ANSI committee creates an applicable standard. Occasionally, though, the academic or research communities introduce a new feature in response to pressures from competing technologies. For example, many database vendors are augmenting their current programmatic offerings with either Java (as is the case with DB2, Oracle, and Sybase) or VBScript (as is the case with Microsoft). In the future, programmers and developers will use these programming languages in concert with SQL to build SQL programs.

Many of these dialects include conditional processing capabilities (such as that controlled through IF . . . THEN statements), control-of-flow functions (such as WHILE loops), variables, and error-handling capabilities. Because ANSI had not yet developed a standard for these important features at the time users began to demand them, RDBMS developers and vendors created their own commands and syntax. In fact, some of the earliest vendors from the 1980s have variances in the most elementary commands, such as SELECT, because their implementations predate the standards. ANSI is now refining standards that address these inconsistencies.

Some of these dialects introduced procedural commands to support the functionality of a more complete programming language. For example, these procedural implementations contain error-handling commands, control-of-flow language, conditional commands, variable-handling commands, support for arrays, and many other extensions. Although these are technically divergent procedural implementations, they are called dialects here. The SQL/PSM (Persistent Stored Module) package provides many features associated with programming stored procedures and incorporates many of the extensions offered by these dialects.

Some popular dialects of SQL include:

PL/SQL

Found in Oracle. PL/SQL stands for Procedural Language/SQL and contains many similarities to the language Ada.

Transact-SQL

Used by both Microsoft SQL Server and Sybase Adaptive Server. As Microsoft and Sybase have moved away from the common platform they shared early in the 1990s, their implementations of Transact-SQL have also diverged.

PL/pgSQL

SQL dialect and extensions implemented in PostgreSQL. The acronym stands for Procedural Language/PostgreSQL.

Users who plan to work extensively with a single database system should learn the intricacies of their preferred SQL dialect or platform.