SQL Dialects Reference/Introduction

Preamble

SQL (Structured Query Language) is one of the oldest programming languages in existence, first versions of which date back to 1969. Unfortunately, despite SQL being standardized since 1986, a lot of different implementations exist. They deviate more or less from each other, making developing applications that would work with a range of different SQL servers particularly difficult.

This wikibook is a compact comparative reference for several SQL language dialects. It lists particular common tasks and problems resolved in terms of several popular SQL server implementations. When possible, it tries to emphasize an universal solution. When it's not possible, it tries to list best practices.

Two main goals for the book are compactness and completeness. Obvious information, e.g. that a particular function or query does the same thing on all implementations, will not be listed. However, when some implementation(s) have important (even minor) deviations that can be a major pitfall for developers, such information definitely should be listed.

Who should use this book

Target audience is:

• Developers who know a single dialect of SQL and want to develop for other SQL implementation right away (it's a simple way to).
• Developers who port a ready project from one SQL implementation to another or a bunch of SQL implementations.
• Developers who want their applications to be portable on a set of SQL servers (it's particularly useful for developers of open source projects that want to ensure that their application can be used on a variety of platforms).
• System administrators who have to support a wide range of SQL servers and don't want to memorize all possible queries and nuances for every server.
• Project managers or lead developers that want to estimate what SQL server is best for their project, given a list of requirements.

Who shouldn't use this book

This book is not a general learning course, not a comprehensive manual - it's a quick and compact reference that assumes that the reader knows basic concepts of SQL and precisely what is needed. The book discusses fairly advanced topics and minor, but important differences in SQL implementations that beginners / learners generally don't have to worry about.

Technical notes

Always where it is possible, a book tries to reference a web-published documentation, so a reader can always check particular detail, so a page is usually full of external documentation links.

As the full names of implementations are pretty long and inconvenient to spell out thoroughly every time, we'll use common abbreviations (shown in bold) to distinguish SQL dialects. Also, we only provide links to corresponding Wikipedia articles here, not on every occasion.

Many pages in this book include comparison tables that can be pretty long. It is recommended to install a comparison table extension to view these tables comfortably.

SQL implementations covered in this book

Several SQL dialects are covered in this book. Note that in all cases of particular implementations, only the latest stable version is described.

• Standard. There are several versions of SQL standard (SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2011). The book would list all popular practices to do a job in all implementations, particularly emphasizing SQL versions that a particular solution works for (i.e. if some feature became standard since version X, it would be stated that such solution works since version X).
• DB2 (IBM DB2).
• Firebird (Firebird 2.5). An open-source RDBMS forked from sources of InterBase that were released by Borland on July 25, 2000. Firebird differs from Interbase and tries to be close to SQL standard as much as possible
• MonetDB An open-source column-store. (MonetDB. V11)
• MySQL (MySQL 5.0)
• MSSQL (Microsoft SQL Server 2008). A proprietary RDBMS produced by Microsoft, targeting enterprise market. Original codebase was derived from Sybase SQL Server, but was mostly rewritten. Documentation is available in MSDN in a well-structured form, quite easy to reference.
• Oracle (Oracle Database 11g2)
• PostgreSQL (PostgreSQL 13)
• SQLite.
• Virtuoso (OpenLink Virtuoso running on Virtuoso Universal Server).
• Linter