O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Dialects of SQL

The constantly evolving nature of the SQL standard has given rise to a number of SQL dialects among the various vendors and products. These dialects most commonly evolved because the user community of a given database vendor required capabilities in the database before the ANSI committee created a standard. Occasionally though, a new feature is introduced by the academic or research communities due to competitive pressures from competing technologies. For example, many database vendors are augmenting their current programmatic offerings with Java (as is the case with Oracle and Sybase) or VBScript (as Microsoft is doing). In the future, programmers and developers will use these programming languages in concert with SQL to build SQL programs.

Nonetheless, each of these dialects includes conditional processing (such as that controlled through IF . . . THEN statements), control-of-flow functions (such as WHILE loops), variables, and error handling. Because ANSI had not yet developed a standard for these important features, RDBMS developers and vendors were free to create 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 shortcomings.)

Some of these dialects have introduced procedural commands to support the functionality of a much more complete programming language. For example, these procedural implementations contain error-handling commands, control-of-flow language, conditional commands, variable handling, arrays, and many other extensions. Although these are technically divergent procedural implementations, they are called dialects here.

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

Uses 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

The name of the SQL dialect and extensions implemented in PostgreSQL. The acronym stands for Procedural Language/postgreSQL.

However, even if a vendor conforms to the SQL99 standards, its commands differ from other DBMSs because SQL statements may be parsed, compiled, and executed in different ways, especially if differing binding styles are used. There are three common binding styles:

SQL Module Language

Causes the SQL statements to be prepared when the module is created, and executed when the module is called (like a stored procedure).

Embedded SQL Syntax

Allows the SQL statements to be prepared when the host language program is precompiled, and executed when the host program is called (like PRO*C or PRO*Fortran).

Direct SQL Invocation

Causes a static SQL statement to be prepared then immediately executed.

Therefore, differences in binding style may be one more reason DBMSs function differently. Binding styles go deep into the heart of the database code. In general, the SQL commands discussed in this book utilize the Direct SQL Invocation binding style. However, when the situation warrants, other relevant binding styles are discussed within the command reference of each specific command.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required