Sams Teach Yourself SQL in 24 Hours

Recommended Book

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Recommended Book

Recommended Book 

Sams Teach Yourself SQL in 24 Hours, 3rd Edition

Learn More Buy

Various Implementations

There are numerous SQL implementations that are released by various vendors. All the relational database vendors could not possibly be mentioned; a few of the leading implementations, however, are discussed. The implementations discussed here are Sybase, dBASE, Microsoft SQL Server, and Oracle. Other popular vendors providing database products other than those mentioned previously include Borland, IBM, Informix, Progress, CA-Ingres, and many more.

Differences Between Implementations

Although the implementations listed here are relational database products, there are specific differences between each. These differences stem from the design of the product and the way data is handled by the database engine; however, this book concentrates on the SQL aspect of the differences. All implementations use SQL as the language for communicating with the database, as directed by ANSI. Many have some sort of extension to SQL that is unique to that particular implementation.

Now that you know SQL, you should have little problem adjusting to the differences in SQL among the various vendors. In other words, if you can write SQL in a Sybase implementation, you should be able to write SQL in Oracle. Besides, knowing SQL for various vendors accomplishes nothing less than improving your résumé.

The following sections compare the SELECT statement's syntax from a few major vendors to the ANSI standard.

The following is the ANSI standard:

   
SELECT [DISTINCT ] [* | COLUMN1 [, COLUMN2 ]

FROM TABLE1 [, TABLE2 ]
[ WHERE SEARCH_ CONDITION ]
GROUP BY [ TABLE_ALIAS | COLUMN1 [, COLUMN2 ]
[ HAVING SEARCH_CONDITION ]]
[{UNION | INTERSECT | EXCEPT}][ ALL ]
[ CORRESPONDING [ BY (COLUMN1 [, COLUMN2 ]) ]
QUERY_SPEC | SELECT * FROM TABLE | TABLE_CONSTRUCTOR ]
[ORDER BY SORT_LIST ]

The following is the syntax for SQLBase:

   
SELECT  [ ALL | DISTINCT ] COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE SEARCH_CONDITION ]
[ GROUP BY COLUMN1 [, COLUMN2 ]
[ HAVING SEARCH_CONDITION ]]
[ UNION [ ALL ]]
[ ORDER BY SORT_LIST ]
[ FOR UPDATE OF COLUMN1 [, COLUMN2 ]]

The following is the syntax for Oracle:

   
SELECT [ ALL | DISTINCT ] COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE SEARCH_CONDITION ]
[[ START WITH SEARCH_CONDITION ]
CONNECT BY SEARCH_CONDITION ]
[ GROUP BY COLUMN1 [, COLUMN2 ]
[ HAVING SEARCH_CONDITION ]]
[{UNION [ ALL ] | INTERSECT | MINUS} QUERY_SPEC ]
[ ORDER BY COLUMN1 [, COLUMN2 ]]
[ NOWAIT ]

The following is the syntax for Informix:

   
SELECT [ ALL  | DISTINCT | UNIQUE ] COLUMN1 [, COLUMN2 ]
FROM TABLE1 [, TABLE2 ]
[ WHERE SEARCH_CONDITION ]
[ GROUP BY {COLUMN1 [, COLUMN2 ] | INTEGER}
[ HAVING SEARCH_CONDITION ]]
[ UNION QUERY_SPEC ]
[ ORDER BY COLUMN1 [, COLUMN2 ]
[ INTO TEMP TABLE [ WITH NO LOG ]]

As you can see by comparing the syntax examples, the basics are there. All have the SELECT, FROM, WHERE, GROUP BY, HAVING, UNION, and ORDER BY clauses. Each of these clauses works conceptually the same, but some have additional options that may not be found in other implementations. These options are called enhancements.

Compliance with ANSI SQL

Vendors do strive to comply with ANSI SQL; however, none are 100 percent ANSI SQL-standard. Some vendors have added commands or functions to ANSI SQL, and many of these new commands or functions have been adopted by ANSI SQL. It is beneficial for a vendor to comply with the standard for many reasons. One obvious benefit to standard compliance is that the vendor's implementation will be easy to learn, and the SQL code used is portable to other implementations. Portability is definitely a factor when a database is being migrated from one implementation to another. Why would a company spend uncountable dollars on a conversion to another implementation that was not compliant to the standard? It probably wouldn't if too many changes would have to be made to the application and the new implementation was difficult to learn. Therefore, ANSI SQL compliance is not a problem in most cases.

Extensions to SQL

Practically all the major vendors have an extension to SQL. A SQL extension is unique to a particular implementation and is generally not portable between implementations. However, popular standard extensions are reviewed by ANSI and are sometimes implemented as a part of the new standard.

PL/SQL, which is a product of Oracle Corporation, and Transact-SQL, which is used by both Sybase and Microsoft SQL Server, are two examples of standard SQL extensions. Both extensions are discussed in relative detail for the examples during this hour.