# SQL Dialects Reference/Print version

The current, editable version of this book is available in Wikibooks, the open-content textbooks collection, at
https://en.wikibooks.org/wiki/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

# Data structure definition/Data types/Numeric types

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Integer (1 byte) | | N/A | N/A | tinyint integer1 | N/A | tinyint (always unsigned) | TINYINT | TINYINT | NUMBER(1) | "char" (including quotation marks) | INTEGER | TINYINT |
| ? | Integer (2 bytes) | | SMALLINT | SMALLINT | smallint integer2 | SMALLINT | smallint | SMALLINT | SMALLINT | NUMBER(2) | SMALLINT INT2 | INTEGER | SMALLINT |
| ? | Integer (3 bytes) | | N/A | N/A | N/A | N/A | N/A | MEDIUMINT | N/A | NUMBER(3) | N/A | INTEGER | N/A |
| ? | Integer (4 bytes) | | INTEGER | INTEGER | integer integer4 | INT INTEGER | int | INT | INT | NUMBER(4) | INT INTEGER INT4 | INTEGER | INT INTEGER |
| ? | Integer (8 bytes) | | BIGINT | BIGINT | bigint integer8 | BIGINT | bigint | BIGINT | BIGINT | INTEGER | BIGINT INT8 | INTEGER | BIGINT |
| ? | Float (single precision, 4 bytes) | | REAL | FLOAT | float4 real float(23) | FLOAT REAL FLOAT(n), n≤24 | real float(24) | FLOAT | REAL | BINARY_FLOAT | REAL FLOAT(24) | N/A | REAL |
| ? | Float (double precision, 8 bytes) | | DOUBLE FLOAT | DOUBLE PRECISION | float float8 double precision float(53) | DOUBLE DOUBLE PRECISION FLOAT(n), n>24 | float float(53) | DOUBLE | FLOAT DOUBLE | BINARY_DOUBLE | DOUBLE PRECISION FLOAT(53) | REAL | FLOAT |
| ? | Fixed precision (p) and scale (s) number | | DECIMAL(p, s) NUMERIC(p, s) | DECIMAL(p, s) NUMERIC(p, s) | DECIMAL(p, s) NUMERIC(p, s) | DECIMAL(p, s) DEC(p,s) NUMERIC(p, s) NUMBER(p,s) | decimal(p, s) numeric(p, s) | DECIMAL(p, s) NUMERIC(p, s) | DECIMAL(p, s) NUMERIC(p, s) | NUMBER(p, s)/NUMBER(p)/NUMBER DECIMAL(p, s)/DECIMAL(p)/DECIMAL NUMERIC(p, s)/NUMERIC(p)/NUMERIC FLOAT(p) | DECIMAL(p, s) NUMERIC(p, s) | N/A | DECIMAL(p, s) NUMERIC(p, s) |
| ? | Fixed precision p and s limits | no limits | p ≤ 31, 0 ≤ s ≤ p | p ≤ 18, 0 ≤ s ≤ p | p ≤ 31, 0 ≤ s ≤ p | p ≤ 30, 0 ≤ s ≤ p, s ≤ 10, p-s ≤ 20 | p ≤ 38, 0 ≤ s ≤ p | p ≤ 65, 0 ≤ 30 ≤ s ≤ p | p ≤ 18, 0 ≤ s ≤ p | p ≤ 38, -84 ≤ s ≤ 127 (for negative scale the least significant digit is on the left side of the decimal point) | p ≤ 1000, 0 ≤ s ≤ p | | |
| ? | UNSIGNED support | No | No | No | No | No | No | Yes | No | Yes | No | No | No |
| ? | Set display width in integer | No | No | No | No | No | No | Yes | No | No | No | No | No |
* 1 2 3 4 5 6 SMALLINT, INTEGER, INT, BIGINT, DECIMAL and NUMERIC. See: Wikibook SQL
* 1 2 3 4 5 6 7 uses dynamic typing; allows any type name
* 1 2 FLOAT, REAL, DOUBLE PRECISION. See: Wikibook SQL

# Data structure definition/Data types/Character types
#### Character types

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Variable-length with limit | VARCHAR(n) | VARCHAR(n) | VARCHAR(n) | VARCHAR(n) NVARCHAR(n) | VARCHAR(n) NVARCHAR(n) | VARCHAR[(n or max)] NVARCHAR[(n or max)] | VARCHAR(n) | VARCHAR(n) CHARACTER VARYING(n) | VARCHAR2(n) VARCHAR(n) is slated for deprecation | character varying(n) varchar(n) | TEXT | VARCHAR(n) |
| ? | Variable-length with limit, max size | no limit | ? | 32765 | ? | ? | 2 GB with (max) or 8000 with the fixed limit | 65535 | about 2 GB | 4000 bytes (32767 bytes if MAX_STRING_SIZE = EXTENDED - Oracle 12G) | about 1 GB | no limit (enforced by global, default = 1M) | ? |
| ? | Fixed-length, blank padded string | CHAR(n) | CHAR(n) | CHAR(n) | CHAR(n) NCHAR(n) | CHAR(n) NCHAR(n) | CHAR(n) NCHAR(n) | CHAR(n) | ? | CHAR(n) | character(n) char(n) | N/A | CHAR(n) |
| ? | Fixed-length, blank padded string, max size | no limit | ? | 32767 | ? | ? | 8000 | 255 | ? | 2000 | about 1 GB | N/A | ? |
Notes:
* ↑ uses dynamic typing; allows any type name

# Data structure definition/Data types/Date and time types
#### Date and time types

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Date only | DATE | DATE | DATE | DATE ANSIDATE INGRESDATE | DATE, DATETIME | date | DATE | DATE | DATE (includes TIME !) | DATE | TEXT | DATE |
| ? | Time only | TIME | TIME | TIME | TIME [WITHOUT TIME ZONE] | DATE DATETIME | time | TIME | TIME | no | TIME [WITHOUT TIME ZONE] | TEXT | TIME |
| ? | Time with time zone | TIME WITH TIME ZONE | ? | ? | TIME WITH TIME ZONE | ? | N/A | N/A | TIME WITH TIME ZONE | no | TIME WITH TIME ZONE | N/A | N/A |
| ? | Date and time without time zone | TIMESTAMP | TIMESTAMP | TIMESTAMP | DATE TIMESTAMP ANSIDATE INGRESDATE | DATE DATETIME | datetime2 datetime smalldatetime | TIMESTAMP | TIMESTAMP TIMESTAMP WITH TIME ZONE | DATE TIMESTAMP | TIMESTAMP [WITHOUT TIME ZONE] | TEXT REAL INTEGER | DATETIME TIMESTAMP |
| ? | Date and time with time zone | TIMESTAMP WITH TIME ZONE | ? | ? | ? | ? | datetimeoffset | N/A | TIMESTAMP TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | TIMESTAMP WITH TIME ZONE | N/A | N/A |
| ? | Time interval | INTERVAL DAY TO SECOND(n) INTERVAL YEAR TO MONTH | ? | ? | INTERVAL DAY TO SECOND(n) INTERVAL YEAR TO MONTH | ? | N/A | Not available as data type, only as data values. | INTERVAL | INTERVAL DAY[(day_precision)] TO SECOND[(fractional_second_precision)] INTERVAL YEAR [(year_precision)] TO MONTH | INTERVAL | N/A | ? |

# Data structure definition/Data types/Large object types
#### Large object types

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Character LOBs | CLOB(n) | CLOB(n) | BLOB SUB_TYPE TEXT | CLOB, TEXT, LONG VARCHAR | BLOB | varchar(max) nvarchar(max) text ntext | TEXT | STRING, CLOB, TEXT, CHARACTER LARGE OBJECT | CLOB | text | TEXT | LONG VARCHAR |
| ? | Character LOBs max size, bytes | no limit | ? | about 4 GB | ? | ? | $2^{31}-1$ | $2^{16}$ | about 2 GB | 4 GB - 1 byte | about 1 GB | 1 000 000 000 | ? |
| ? | Binary LOBs | BLOB(n) | BLOB(n) | BLOB SUB_TYPE BINARY | BLOB LONG BYTE | BLOB | varbinary(max) image | BLOB(n) BINARY LARGE OBJECT | BLOB(n) | BLOB | bytea lo | BLOB | LONG VARBINARY |
| ? | Binary LOBs max size, bytes | no limit | ? | about 4 GB | ? | ? | $2^{31}-1$ | $2^{16}$ | about 2 GB | 4 GB - 1 byte | ? | 1 000 000 000 | ? |
Notes:
* 1 2 3 the built-in date/time functions can use these types for storing values

# Data structure definition/Data types/Misc types
#### Misc types

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Boolean | boolean | N/A | N/A | ? | BOOLEAN | BIT | BOOLEAN TINYINT(1) | BOOLEAN | No | BOOLEAN | N/A | BIT |
| ? | Boolean storage size | N/A | ? | ? | ? | ? | 8 columns in a single table = 1 byte | 1 byte | 1 byte | N/A | 1 byte | N/A | ? |
| ? | UUID | N/A | N/A | ? | ? | ? | uniqueidentifier | ? | UUID | Not a data type, but a function SYS_GUID will return a UUID as a RAW data type. | UUID | N/A | IRI |
| ? | Row version | N/A | ? | ? | ? | ? | TIMESTAMP ROWVERSION | ? | ? | ? | ? | N/A | ? |
| ? | Any data type | N/A | ? | ? | ? | ? | SQL_VARIANT | ? | ? | ANYTYPE ANYDATA ANYDATASET | ? | (any) | ? |

# Data structure definition/Delimited identifiers
### Delimited identifiers

There are regular identifiers and delimited identifiers to denominate database objects like tables or columns. You must apply delimited identifiers if you want to use SQL keywords like 'FROM' as identifiers or special characters within its name. Delimited identifiers are labeled by the following techniques.
| Dialect | Sample | Comment |
| Standard | "identifier" | The SQL standard calls these "delimited identifiers". The SQL standard requires that delimited identifiers are case-sensitive, although not all servers enforce this. |
| DB2 | "identifier" | Case sensitive per standard and also allows special characters. |
| Firebird | "identifier" | Case sensitive per standard. |
| Ingres | "identifier" | Case sensitivity for delimited identifiers is specified when a database is created. For compliance with ANSI/ISO Entry SQL-92, delimited identifiers must be case sensitive. |
| Linter | "identifier" | Case sensitive per standard. |
| MonetDB | "identifier" | Case sensitive per standard. |
| MSSQL | [identifier] "identifier" | Case sensitivity is set by a per-database option. |
| MySQL | `identifier` "identifier" |
| Oracle | "identifier" | Case sensitive per standard. Case insensitive when not double-quoted: They are converted to uppercase before the execution of any statement, e.g: 'CrEAtE table MyTable' is converted to 'CREATE TABLE MYTABLE'. This conforms to the standard. |
| PostgreSQL | "identifier" | Case sensitive per standard. Case insensitive when not double-quoted (converted to lower case - while it's upper case per standard). |
| SQLite | [identifier] "identifier" 'identifier' identifier | Never case sensitive, even when quoted. Bracketed keywords are always understood as identifiers. Double-quoted keywords are understood as identifiers if previously seen as such, but are otherwise interpreted as string literals. Single-quoted keywords are interpreted as string literals where such are allowed, but otherwise as identifiers. Some keywords may be used as identifiers even when not quoted. |
| Virtuoso | "identifier" | |
Notes:
* ↑ If the quoted_identifier option is set on — otherwise, "identifier" would be parsed as a string in single quotes.
* ↑ If running in ANSI mode.

# Data structure definition/Auto-increment column

A short hint: In most cases auto-increment columns are used as Primary Key columns. In the SQL standard the junction of the two concepts is not mandatory.

## SQL Standard

The SQL standard defines two ways to generate auto-increment values. First, there are identity columns as an extension to exact numeric types. The syntax is: "GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY". Second, the use of sequences in combination with triggers is standardized.

## DB2

Identity columns or sequences combined with triggers (comparison of both techniques).

## Firebird

Is recommended to use sequences combined with triggers . From 3.0 there is Identity support.

## Linter

AUTOINC columns (maybe with RANGEs) or sequences combined with triggers.

## SQLite

Both create an autoincrementing column; the AUTOINCREMENT keyword only prevents reusing deleted values.

# Functions and expressions/Math functions/Trigonometric functions
#### Trigonometric functions

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Arc sine | N/A | ASIN(x) | ASIN(x) | ASIN(X) | ASIN(x) | ASIN(x) | ASIN(x) | ASIN(x) | ASIN(x) | ASIN(x) | N/A | ASIN(x) |
| ? | Arc cosine | N/A | ACOS(x) | ACOS(x) | ACOS(x) | ACOS(x) | ACOS(x) | ACOS(x) | ACOS(x) | ACOS(x) | ACOS(x) | N/A | ACOS(x) |
| ? | Arc tangent of x | N/A | ATAN(x) | ATAN(x) | ATAN(x) | ATAN(x) | ATAN(x) | ATAN(x) | ATAN(x) | ATAN(x) | ATAN(x) | N/A | ATAN(x) |
| ? | Arc tangent of x and y | N/A | ATAN2(x, y) | ATAN2(x,y) | ATAN2(x, y) | ATAN2(x, y) | ATN2(x, y) | ATAN2(x, y) ATAN(x, y) | ATAN2(x,y) | ATAN2(x, y) ATAN2(x / y) | ATAN2(x,y) | N/A | ATAN2(x, y) |
| ? | Sine | N/A | SIN(x) | SIN(x) | SIN(x) | SIN(x) | SIN(x) | SIN(x) | SIN(x) | SIN(x) | SIN(x) | N/A | SIN(x) |
| ? | Cosine | N/A | COS(x) | COS(x) | COS(x) | COS(x) | COS(x) | COS(x) | COS(x) | COS(x) | COS(x) | N/A | COS(x) |
| ? | Tangent | N/A | TAN(x) | TAN(x) | TAN(x) | TAN(x) | TAN(x) | TAN(x) | TAN(x) | TAN(x) | TAN(x) | N/A | TAN(x) |
| ? | Cotangent | N/A | COT(x) | COT(x) | N/A | N/A | COT(x) | COT(x) | COT(x) | N/A | COT(x) | N/A | COT(x) |
| ? | Hyperbolic sine | N/A | SINH(x) | SINH(x) | N/A | SINH(x) | N/A | N/A | SINH(x) | SINH(x) | N/A | N/A | N/A |
| ? | Hyperbolic cosine | N/A | COSH(x) | COSH(x) | N/A | COSH(x) | N/A | N/A | COSH(x) | COSH(x) | N/A | N/A | N/A |
| ? | Hyperbolic tangent | N/A | TANH(x) | TANH(x) | N/A | TANH(x) | N/A | N/A | TANH(x) | TANH(x) | N/A | N/A | N/A |
| ? | Hyperbolic arctangent | N/A | ATANH(x) | ATANH(x) | N/A | ? | N/A | N/A | N/A | N/A | N/A | N/A | ? |

# Functions and expressions/Math functions/Numeric functions
#### Numeric functions

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Absolute value of x | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) | ABS(x) |
| ? | Sign of number x | N/A | SIGN(x) | SIGN(x) | SING(x) | SIGN(x) | SIGN(x) | SIGN(x) | SIGN(x) | SIGN(x) | SIGN(x) | N/A | SIGN(x) |
| ? | Modulus (remainder) of $x/y$ | MOD(x, y) | MOD(x, y) | MOD(x, y) | MOD(x, y) | MOD(x, y) | x % y | x % y MOD(x, y) | x % y MOD(x, y) | MOD(x, y) | x % y MOD(x, y) | x % y | MOD(x, y) |
| ? | Smallest integer >= x | CEILING(x) CEIL(x) | CEILING(x) CEIL(x) | CEILING(x) CEIL(x) | CEIL(x) CEILING(x) | CEIL(x) | CEILING(x) | CEILING(x) CEIL(x) | CEILING(x) CEIL(x) | CEIL(x) | CEILING(x) CEIL(x) | N/A | CEILING(x) |
| ? | Largest integer <= x | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | FLOOR(x) | N/A | FLOOR(x) |
| ? | Round x (to precision of d digits) | N/A | ROUND(x, d) | ROUND(x, d) | ROUND(x, d) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x, d) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x[, d]) | ROUND(x) |
| ? | Truncate x to n decimal places | N/A | TRUNCATE(x, n) TRUNC(x, n) | TRUNC(x[, n]) | TRUNCATE(x, n) TRUNC(x, n) | TRUNC(x[, d]) | ROUND(x[, d], 1) | TRUNCATE(x[, dn) | sys.ms_trunc(x, n) | TRUNC | TRUNC(x[, y]) | N/A | N/A |
| ? | Square root of x (${\sqrt {x}}$) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | SQRT(x) | N/A | SQRT(x) |
| ? | Exponent of x ($e^{x}$) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | EXP(x) | N/A | EXP(x) |
| ? | Power ($x^{y}$) | POWER(x, y) | POWER(x, y) | POWER(x, y) | POWER(x, y) x ** y | POWER(x, y) | POWER(x, y) | POW(x, y) POWER(x, y) | POWER(x, y) | POWER(x, y) | POWER(x, y) | N/A | POWER(x,y) |
| ? | Natural logarithm of x | LN(x) | LN(x) | LN(x) | LOG(x) LN(x) | LN(x) | LOG(x) | LN(x) LOG(x) | LN(x) LOG(x) | LN(x) | LN(x) | N/A | LOG(x) |
| ? | Logarithm of x, base b | N/A | LOG(b, x) | LOG(b, x) | N/A | LOG(b, x) | LOG(x, b) | LOG(b, x) | LOG(b, x) | LOG(b, x) | LOG(b, x) | N/A | ? |
| ? | Logarithm, base 10 | N/A | LOG10(x) | LOG10(x) | N/A | N/A | LOG10(x) | LOG10(x) | LOG10(x) | LOG(x) | LOG(x) | N/A | LOG(x) |
| ? | Randomize, set seed to x | N/A | RAND(x) | N/A | SET RANDOM_SEED x | RAND(x) | RAND(x) | RAND(x) | RAND(x) | random | SETSEED(x) | N/A | RANDOMIZE([x]) |
| ? | Generate floating-point random number between 0 and 1 | N/A | RAND | RAND | RANDOMF | RAND | RAND | RAND | CAST(RAND as float) / 2147483648 | dbms_random.value (returns number >= 0 and < 1) dbms_random.value(37, 89) (returns a random number >= 37 and < 89.) | RANDOM | RANDOM (between −2$^{63}$ and 2$^{63}$) | RND |
| ? | Highest number in a list | N/A | N/A | MAXVALUE(list) | ? | GREATEST(list) | N/A | GREATEST(list) | GREATEST(a, b) | GREATEST(list) | GREATEST(list) | MAX(list) | MAX(list) |
| ? | Lowest number in a list | N/A | N/A | MINVALUE(list) | ? | LEAST(list) | N/A | LEAST(list) | LEAST(a, b) | LEAST(list) | LEAST(list) | MIN(list) | MIN(list) |

#### Notes

* ↑ The random function in Oracle can be found in the built-in DBMS package dbms_random.

# Functions and expressions/Math functions/Aggregate functions
#### Aggregate functions

Aggregate function operate on a group of values, returning single scalar value. The standard specifies that with the exception of VAR_POP, VAR_SAMP, STDDEV_POP and STDDEV_SAMP all aggregate function should be able to handle one of two additional quantifier before an argument: ALL (feature ID E091-06) and DISTINCT (feature ID E091-07). ALL is the default and can be omitted and DISTINCT means that only unique values would be passed in aggregate function. To make presentation more compact, these two quantifiers aren't discussed separately for every function, but specified as [DISTINCT|ALL] if function supports both of them.
This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Count all rows in a group | COUNT(*) ... GROUP BY <grouping criterion> | COUNT(*) | COUNT(*) | COUNT(*) | ? | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | ? |
| ? | Count non-NULL values in x | COUNT(x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | ? |
| ? | Concatenate non-NULL values in x using y as separator | N/A | ? | LIST([DISTINCT] x, y) | ? | ? | N/A | GROUP_CONCAT([DISTINCT] x SEPARATOR y) | listagg(x) listagg(x, y) sys.group_concat(x) sys.group_concat(x, y) | since R11.2: LISTAGG(x, y) | STRING_AGG(x, y) since 9.0 | GROUP_CONCAT([DISTINCT] x [, y]) | ? |
| ? | Sum of x | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM(x) | SUM([DISTINCT] x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM([DISTINCT] x) TOTAL([DISTINCT] x) | ? |
| ? | Average of x | AVG(x) | AVG([DISTINCT] x) | AVG(x) | AVG(x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG(x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | ? |
| ? | Minimum value in x | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | ? |
| ? | Maximum value in x | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | ? |
| ? | Standard deviation | STDDEV_POP(x) STDDEV_SAMP(x) | STDDEV([DISTINCT] x) | STDDEV_POP(x) STDDEV_SAMP(x) | STDDEV_POP(x) | STDDEV([DISTINCT] x) | STDEV(x) STDEVP(x) | STD(x) STDDEV(x) STDDEV_POP(x) | sys.stddev_pop(x) sys.stddev_samp(x) | STDDEV([DISTINCT|ALL] x) | STDDEV(x) STDDEV_POP(x) STDDEV_SAMP(x) | N/A | STDDEV(x, y) |
| ? | Variance | VAR_POP(x) VAR_SAMP(x) | VARIANCE([DISTINCT] x) | VAR_POP(x) VAR_SAMP(x) | ? | VARIANCE([DISTINCT] x) | VAR(x) VARP(x) | VARIANCE(x) VAR_POP(x) | sys.var_pop(x) sys.var_samp(x) | VARIANCE(x) | VARIANCE(x) VAR_POP(x) VAR_SAMP(x) | N/A | VAR(x) |
| ? | Population covariance of x and y | COVAR_POP(x, y) | COVARIANCE(x, y) COVAR(x, y) | COVAR_POP(x, y) | ? | ? | N/A | N/A | sys.covar_pop(x, y) | COVAR_POP(x, y) | COVAR_POP(x, y) | N/A | ? |
| ? | Sample covariance of x and y | COVAR_SAMP(x, y) | N/A | COVAR_SAMP(x, y) | ? | ? | N/A | N/A | sys.covar_samp(x, y) | COVAR_SAMP(x, y) | COVAR_SAMP(x, y) | N/A | ? |
* ↑ Can be implemented using user-defined aggregator functions or several other approaches
* ↑ Can be implemented using user-defined aggregator function, undocumented wmsys.wm_concat function and using several other approaches
* ↑ Can be implemented for older versions using PostgreSQL arrays and user-defined aggregator functions

# Functions and expressions/Date and time functions
### Date and time functions

    See also: Date and time types
This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Current date | CURRENT_DATE | CURRENT DATE CURRENT_DATE | CURRENT_DATE | DATE('TODAY') | SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP (incl time also) | CAST(GETDATE AS DATE) CONVERT(DATE, GETDATE) | CURDATE CURRENT_DATE CURRENT_DATE | CURRENT_DATE | TRUNC(CURRENT_DATE), TRUNC(SYSDATE) | CURRENT_DATE | CURRENT_DATE | CURDATE |
| ? | Current time | CURRENT_TIME | CURRENT TIME CURRENT_TIME | CURRENT_TIME | TIME(DATE('NOW')) | N/A | N/A | CURTIME, CURRENT_TIME | CURRENT_TIME | N/A | CURRENT_TIME | CURRENT_TIME | CURTIME |
| ? | Current date and time | CURRENT_TIMESTAMP | CURRENT TIMESTAMP CURRENT_TIMESTAMP | CURRENT_TIMESTAMP, 'NOW' | DATE('NOW') | SYSDATE, CURRENT_TIMESTAMP, UNIX_TIMESTAMP | GETDATE, CURRENT_TIMESTAMP | NOW, CURRENT_TIMESTAMP | NOW, CURRENT_TIMESTAMP | CURRENT_DATE, SYSDATE, SYSTIMESTAMP, CURRENT_TIMESTAMP | NOW, CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | NOW |
| ? | Add interval to date | date + arg | date + arg | arg1 + arg2 | arg1 + arg2 arg1 = date, arg2 = time-interval; date+'15 day' | arg1 + arg2 ADD_MONTHS | DATEADD | DATE_ADD | arg1 + interval in days or fractional days | arg1 + interval in days or fractional days ADD_MONTHS see more | arg1 + arg2 | N/A | DATEADD |
| ? | Subtract interval from date | date - arg | date - arg | arg1 - arg2 | arg1 - arg2 arg1 = date, arg2 = time-interval; date-'3 month' | arg1 - arg2 | DATEDIFF | DATE_SUB | arg1 - interval '1' DAY | arg1 - arg2 (sysdate-interval '1' MONTH) | arg1 - arg2 | N/A | DATEDIFF |
| ? | Date difference | (date1 - date2) field | date1 - date2 | arg1 - arg2 DATEDIFF | date1 - date2; interval( 'day', date1 - date2) | arg1 - arg2 | DATEDIFF | DATEDIFF | CAST(arg1 - arg2 as bigint)/1000 (diff in seconds) | arg1 - arg2 see more see more-2 | AGE | N/A | DATEDIFF |
| ? | Last day of month | ? | date + 1 MONTH - DAY(date) DAYS | LASTDAYMONTH | LAST_DAY | LAST_DAY | N/A | LAST_DAY | ? | LAST_DAY | N/A | N/A | N/A |
| ? | Time zone conversion | date AT TIME ZONE offset | ? | N/A | ? | N/A | N/A | CONVERT_TZ | ? | NEW_TIME | TIMEZONE | N/A | TIMEZONE |
| ? | First weekday after date | ? | ? | N/A | ? | NEXT_DAY | N/A | ? | ? | NEXT_DAY | N/A | N/A | N/A |
| ? | Convert date to string | CAST (x AS STRING) | TO_CHAR(value, format) VARCHAR_FORMAT(value, format) | CAST(value, datetype) DATETOSTR | DATE_FORMAT(date, format) char(date) | TO_CHAR(value, format) | DATENAME | DATE_FORMAT(value, format) | CAST (x AS STRING) | TO_CHAR | TO_CHAR(value, format) | STRFTIME(format, value) | CAST |
| ? | Convert date to number | N/A | INT(date) | EXTRACT | ? | TO_NUMBER(value) DATESPLIT | DATEPART | ? | ? | TO_NUMBER(TO_CHAR) | DATE_PART | N/A | CAST |
| ? | Convert string to date | CAST (x AS DATE) | DATE(value) TIMESTAMP(value) | CAST | DATE(string) | TO_DATE(value, format) TO_TIMESTAMP(value, format) | CAST | ? | CAST, TIMESTAMP 'yyyy-mm-dd HH:mm:ss' | TO_DATE | TO_DATE | N/A | STRINGDATE |
| ? | Extract year from DATE or DATETIME x | EXTRACT(YEAR FROM x) | ? | EXTRACT(YEAR FROM x) | YEAR(x) DATE_PART(YEAR, x) | ? | YEAR(x) DATEPART(year, x) | YEAR(x) EXTRACT(YEAR FROM x) | EXTRACT(YEAR FROM x) | EXTRACT(YEAR FROM x) | EXTRACT(YEAR FROM x) | strftime('%Y', x) | ? |
| ? | Extract month from DATE or DATETIME x | EXTRACT(MONTH FROM x) | ? | EXTRACT(MONTH FROM x) | MONTH(x) DATE_PART(MONTH, x) | ? | MONTH(x) DATEPART(month, x) | MONTH(x) EXTRACT(MONTH FROM x) | EXTRACT(MONTH FROM x) | EXTRACT(MONTH FROM x) | EXTRACT(MONTH FROM x) | strftime('%m', x) | ? |
| ? | Extract day of month from DATE or DATETIME x | EXTRACT(DAY FROM x) | ? | EXTRACT(DAY FROM x) | DAY(x) DATE_PART(DAY, x) | ? | DAY(x) DATEPART(day, x) | DAYOFMONTH(x) DAY(x) EXTRACT(DAY FROM x) | EXTRACT(DAY FROM x) | EXTRACT(DAY FROM x) | EXTRACT(DAY FROM x) | strftime('%d', x) | ? |
| ? | Extract hour (0…23) from TIME or DATETIME x | EXTRACT(HOUR FROM x) | ? | EXTRACT(HOUR FROM x) | HOUR(x) DATE_PART(HOUR, x) | ? | DATEPART(hour, x) | HOUR(x) EXTRACT(HOUR FROM x) | EXTRACT(HOUR FROM x) | EXTRACT(HOUR FROM x) | EXTRACT(HOUR FROM x) | strftime('%H', x) | ? |
* 1 2 3 4 These functions are only applicable to ingresdate data type in Ingres; normal dates, represented by ansidate datatype should be converted to ingresdate first.

# Functions and expressions/String functions
### String functions

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| Function | Since SQL | Standard | DB2 | SQLite | MonetDB | MySQL | PostgreSQL | Firebird | Virtuoso | Oracle | MSSQL | Linter |
| Convert character x to ASCII | N/A | N/A | ASCII(x) | UNICODE(x) | ASCII(x) | ASCII(x) | ASCII(x) | ASCII_VAL(x) | ASCII(x) | ASCII(x) | ASCII(x) | N/A |
| Convert ASCII x to character | N/A | N/A | CHR(x) | CHAR(x) | N/A | CHAR(x) | CHR(x) | ASCII_CHAR(x) | CHR(x) | CHR(x) | CHAR(x) | CHR(x) |
| String concatenate | 92 | arg1 || arg2 | arg1 || arg2 arg1 CONCAT arg2 | arg1 || arg2 | CONCAT (arg1 , arg2) arg1 || arg2 ... || argN | CONCAT (multiple arguments) | arg1 || arg2 | arg1 || arg2 | CONCAT(list) | arg1 || arg2 CONCAT (only 2 arguments) | CONCAT(list) arg1 + arg2 | arg1||arg2 CONCAT(list) arg1+arg2 |
| Join items using a separator | N/A | N/A | ? | ? | ? | CONCAT_WS(separator, item1, item2, ...) | CONCAT_WS(separator, item1, item2, ...) | ? | ? | N/A | CONCAT_WS(separator, item1, item2, ...) | ? |
| Find first occurrence of substring search in str, starting from start | 92 | POSITION(search IN str) | LOCATE(search, str[, start]) POSSTR(str, search) | INSTR(str, search) | POSITION(search IN str) | POSITION(search IN str) INSTR(str, search) LOCATE(search, str[, start]) | POSITION(search IN str) STRPOS(str, search) | POSITION(search IN str) | SUBSTR | INSTR(str, search[, start]) | CHARINDEX(search, str[, start]) | POSITION(search IN str) INSTR(str, search, [start [,[n]]) |
| Find first occurrence of pattern search in string str | 2003 | SUBSTRING_REGEX (search IN str) | | N/A | N/A | | N/A | N/A | INSTR | REGEXP_INSTR(str, search) | PATINDEX(search, str) | N/A |
| Convert x to lowercase | 92 | LOWER(x) | LOWER(x) LCASE(x) | LOWER(x) | LOWER(x) | LOWER(x) LCASE(x) | LOWER(x) | LOWER(x) | LCASE(x) | LOWER(x) | LOWER(x) | LOWER(x) |
| Convert x to uppercase | 92 | UPPER(x) | UPPER(x) UCASE(x) | UPPER(x) | UPPER(x) | UPPER(x) UCASE(x) | UPPER(x) | UPPER(x) | UCASE(x) UPPER(x) | UPPER(x) | UPPER(x) | UPPER(x) |
| Pad left side | 2003 | | | N/A | LPAD(str, len [, fill]) | LPAD | LPAD | LPAD | N/A | LPAD | N/A | LPAD |
| Pad right side | 2003 | | | N/A | RPAD(str, len [, fill]) | RPAD | RPAD | RPAD | N/A | RPAD | N/A | RPAD |
| Remove leading blank spaces from x | 92 | TRIM(LEADING [' '] FROM x) | N/A | LTRIM(x) | LTRIM(str [, str]) | LTRIM | LTRIM | TRIM(LEADING [' '] FROM x) | LTRIM | LTRIM | LTRIM | LTRIM |
| Remove trailing blank spaces from x | 92 | TRIM(TRAILING [' '] FROM x) | N/A | RTRIM(x) | RTRIM(str [, str]) | RTRIM | RTRIM | TRIM(TRAILING [' '] FROM x) | RTRIM | RTRIM | RTRIM | RTRIM |
| Remove leading and trailing blanks from x | 92 | TRIM(BOTH [' '] FROM x) TRIM(x) | LTRIM(RTRIM(x)) or TRIM(x) | TRIM(x) | TRIM(str [, str]) | TRIM | TRIM | TRIM(BOTH [' '] FROM x) TRIM(x) | TRIM | TRIM | LTRIM(RTRIM(x)) | TRIM |
| Repeat str n times | 2003 | | REPEAT(str, n) | N/A | REPEAT | REPEAT | REPEAT | RPAD | REPEAT | RPAD | REPLICATE | REPEAT_STRING(str, n) |
| String of n spaces | 2003 | | SPACE(n) | N/A | N/A | SPACE(n) | N/A | RPAD | SPACE(n) | RPAD | SPACE(n) | |
| Convert number to string | 2003 | | CHAR(num) | CAST | CAST | | CAST | CAST | | TO_CHAR | STR | TO_CHAR |
| Substring from string str, starting from start, length of len | 92 | SUBSTRING(str FROM start [FOR len]) | SUBSTR(str, len[, start]) | SUBSTR(str, start [, len]) | SUBSTRING | SUBSTRING SUBSTR | SUBSTRING(str FROM start [FOR len]) SUBSTR(str, start[, len]) | SUBSTRING(str FROM start [FOR len]) | SUBSTR | SUBSTR(str,start[,len]) | SUBSTRING(str, start, length) | SUBSTRING(str,start[,len]) SUBSTRING(str FROM start [FOR len]) The same with SUBSTR instead of SUBSTRING |
| Replace characters | | | REPLACE(string, from, to) | REPLACE(str, from, to) | REPLACE | REPLACE | REPLACE | REPLACE(str, find, repl) | REPACE | REPLACE | REPLACE | REPLACE |
| Capitalize first letter of each word in string x | N/A | N/A | INITCAP(x) | N/A | N/A | N/A | INITCAP(x) | N/A | INITCAP(x) | INITCAP(x) | N/A | INITCAP(x) |
| Translate string | | | TRANSLATE(str, to, from) | N/A | N/A | N/A | TRANSLATE(str, from, to) | N/A | | TRANSLATE(str, from, to) | N/A | TRANSLATE |
| Length of string x (in characters) | 92 | CHAR_LENGTH(x) CHARACTER_LENGTH(x) | LENGTH(x) | LENGTH(x) | LENGTH(x) | CHAR_LENGTH(x) | CHAR_LENGTH(x) CHARACTER_LENGTH(x) | CHAR_LENGTH(x) CHARACTER_LENGTH(x) | LENGTH(x) | LENGTH(x) | LEN(x) | LENGTH(x) |
| Length of string x (in bytes) | 92 | OCTET_LENGTH(x) | LENGTH(x) | LENGTH(CAST(x AS BLOB)) | OCTET_LENGTH(x) | LENGTH(x) | OCTET_LENGTH(x) | OCTET_LENGTH(x) | LENGTH(x) | LENGTHB(x) | DATALENGTH(x) | OCTET_LENGTH(x) |
| Greatest character string in list | 2003 | | | MAX | | | GREATEST | MAX | MAX | GREATEST | N/A | GREATEST |
| Least character string in list | 2003 | | | MIN | | | LEAST | MIN | MIN | LEAST | N/A | LEAST |
| Quote SQL in string x | | | | QUOTE(x) | | QUOTE(x) | QUOTE(x) | N/A | QUOTE_LITERAL(x) | q'quote_delimiter x quote_delimiter' | QUOTENAME(x, '''') |
| Soundex index of string x | | | SOUNDEX(x) | SOUNDEX(x) | SOUNDEX(x) | SOUNDEX(x) | N/A | N/A | N/A | SOUNDEX(x) | SOUNDEX(x) | N/A |
| Calculate MD5 hash from string x | N/A | N/A | | N/A | | MD5(x) | MD5(x) | N/A | N/A | DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 2) | HASHBYTES('MD5', x) | N/A |
| Calculate SHA1 hash from string x | N/A | N/A | | N/A | | SHA1(x) | N/A | N/A | N/A | DBMS_CRYPTO.HASH (UTL_RAW.CAST_TO_RAW(X), 3) | HASHBYTES('SHA1', x) | N/A |
| Generate UUID | N/A | N/A | | N/A | | UUID | | GEN_UUID | | SYS_GUID | NEWID NEWSEQUENTIALID | SYS_GUID |

#### Notes

* ↑ Oracle concatenation result does not get "eaten" by NULLs (unlike in ANSI SQL). Oracle documentation warns about potential future change in this behavior and recommends explicitly coalescing NULLs.
* ↑ MS SQL concat result does not get "eaten" by NULLs (unlike in ANSI SQL). + result does not get "eaten" when SET CONCAT_NULL_YIELDS_NULL OFF;
* ↑ INITCAP is supported starting DB2 V9.7.
* ↑ Soundex function is omitted from SQLite by default. Only available if SQLite is built with -DSQLITE_SOUNDEX=1 compile-time option.
* ↑ MySQL uses original Soundex algorithm.
* ↑ Uses enhanced Soundex algorithm as defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth.

# Functions and expressions/Misc expressions
### Misc functions

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Convert value val to data type type | CAST(val AS type) | ? | CAST(val AS type) | ? | CAST(val AS type) CASTexpression as type TO_CHAR TO_NUMBER TO_DATE | CAST(val AS type) CONVERT(type, val, style) | CONVERT(val, type) CAST(val AS type) | CAST(val AS type) CONVERT(val, type) | CAST(val AS type) | CAST(val AS type) val::type | CAST(val AS type) | ? |
| ? | Replace NULL within a val with a fallback value, return val intact, if it's non-NULL | COALESCE(val, fallback) | ? | ? | ? | NVL(val, fallback) | COALESCE(val, fallback) ISNULL(val, fallback) | IFNULL(val, fallback) | COALESCE(val, fallback) | COALESCE(val, fallback) NVL(val, fallback) | COALESCE(val, fallback) | IFNULL(val, fallback) COALESCE(val, fallback) | ? |
| ? | Return the first non-NULL value from a list of values (val1, val2, ...) | COALESCE(val1, val2, ...) | COALESCE(val1, val2) | COALESCE(val1, val2, ...) | ? | COALESCE(val1, val2, ...) | COALESCE(val1, val2, ...) | COALESCE(val1, val2, ...) | COALESCE(val1, val2, ...) | COALESCE(val1, val2, ...) | COALESCE(val1, val2, ...) | COALESCE(val1, val2, ...) | ? |
| ? | Convert particular values to NULL: return NULL if a = b otherwise keep a | NULLIF(a, b) | ? | NULLIF(a, b) | ? | NULLIF(a, b) | NULLIF(a, b) | NULLIF(a, b) | NULLIF(a, b) | NULLIF(a, b) | NULLIF(a, b) | NULLIF(a, b) | ? |

# Select queries/Subqueries
### Subqueries in FROM clause

| Standard | Subquery (ANSI calls it <derived table>) alias is mandatory: SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions |
| DB2 | ? |
| Firebird | ? |
| Ingres | ? |
| Linter | ? |
| MonetDB | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions Subquery alias is mandatory WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions |
| MSSQL | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions Subquery alias is mandatory WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions |
| MySQL | ? |
| Oracle | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) WHERE conditions |
| PostgreSQL | SELECT expressions FROM (SELECT subquery expressions FROM subquery tables) [ AS ] subquery_alias WHERE conditions Subquery alias is mandatory WITH common_table_expression AS (SELECT subquery expressions FROM subquery tables) SELECT expressions FROM common_table_expression WHERE conditions |
| SQLite | SELECT … FROM (SELECT …) [AS alias] WHERE … WITH cte AS (SELECT …) SELECT … FROM cte WHERE … |
| Virtuoso | ? |

# Select queries/Select without tables
### Select without tables

Sometimes one needs to execute SQL scalar expressions without table context, i.e. make a query that would act as normal SELECT operator, evaluate given comma-separated expressions and return a table with single row and one or multiple columns (one for every individual expression). Obviously, expressions can't reference any columns from the tables, as there are none.
An example is determining the value of a mathematical function, using Oracle syntax:
| Standard | ? |
| DB2 | VALUES SELECT expressions FROM sysibm.sysdummy1 |
| Firebird | SELECT expressions FROM rdb$database |
| Ingres | SELECT expressions |
| Linter | SELECT expressions |
| MonetDB | SELECT expressions VALUES ("expressions") |
| MSSQL | SELECT expressions VALUES (expressions) |
| MySQL | SELECT expressions SELECT expressions FROM dual VALUES row_constructor_list |
| Oracle | SELECT expressions FROM dual |
| PostgreSQL | SELECT expressions VALUES (expressions) |
| SQLite | SELECT expressions VALUES ("expressions") |
| Virtuoso | ? |

# Select queries/Limiting results of select query
### Limiting number of rows returned

Note that end_row = start_row + num_rows - 1
| Standard | SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY |
| DB2 | SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY |
| Firebird | Versions > 2.0 SELECT columns FROM table ROWS start_row TO end_row All versions SELECT FIRST num_rows SKIP start_row columns FROM table |
| Ingres | SELECT FIRST num_rows columns FROM table SELECT columns FROM table OFFSET start_row FETCH {FIRST|NEXT} num_rows ROWS ONLY |
| Linter | SELECT columns FROM table FETCH FIRST num_rows SELECT columns FROM table LIMIT start_row, num_rows (rows are numbered from 0) |
| MonetDB | SELECT columns FROM table LIMIT num_rows OFFSET start_row |
| MSSQL | SELECT columns FROM table OFFSET start_row ROWS FETCH { FIRST | NEXT } num_rows ROW[S] ONLY Simple version (when start_row = 0): SELECT TOP num_rows columns FROM table |
| MySQL | Versions > 4.0.14 SELECT columns FROM table LIMIT num_rows OFFSET start_row All versions SELECT columns FROM table LIMIT start_row, num_rows |
| Oracle | Simple query -- Notice: Will not work, if start_row > 1, since the first row will return false, and the cursor will terminate. SELECT columns FROM table WHERE rownum >= start_row AND rownum <= end_row This works fine without specifying start_row: SELECT columns FROM table WHERE rownum <= end_row This also works, but suffix num column in resultset: SELECT * FROM ( SELECT temp.*, rownum num FROM table ORDER BY columns ) WHERE num >= start_row and num <= end_row Since 12.1, similar to standards: SELECT columns FROM table FETCH FIRST num_rows ROWS ONLY SELECT columns FROM table OFFSET start_row ROWS FETCH FIRST num_rows ROWS ONLY Full syntax: (Can have OFFSET, or FETCH, or both clauses) SELECT column [, column2 …] FROM table [ORDER BY column2] [OFFSET start_row ROWS] [FETCH [FIRST|NEXT] [num_rows|percent PERCENT] ROWS [ONLY|WITH TIES]] |
| PostgreSQL | SELECT columns FROM table LIMIT num_rows OFFSET start_row or: The SQL standard syntax. |
| SQLite | SELECT columns FROM table LIMIT num_rows OFFSET start_row |
| Virtuoso | SELECT TOP num_rows columns FROM table SELECT TOP skip_rows,num_rows columns FROM table |
| Standard | - |
| DB2 | ? |
| Firebird | ? |
| Ingres | ? |
| Linter | ? |
| MonetDB | ? |
| MSSQL | NOT IN |
| MySQL | NOT IN |
| Oracle | MINUS |
| PostgreSQL | ? |
| SQLite | ? |
| Virtuoso | ? |

# Select queries/Hierarchical Queries
### Hierarchical Queries

Hierarchical queries are a way to extract information from a table that is linked with itself.
Let's say we have the following table:
If we have the following values:
The values that "hang" from node 50 are the values 50, 51, 52, 53, 54 but not 5 nor 10.
* DB2
or
* Firebird / InterBase
* Ingres, MySQL, MSSQL
* PostgreSQL
* SQLite
* Oracle, Linter
* ↑ https://jakewheat.github.io/sql-overview/sql-2011-foundation-grammar.html#table-primary
* ↑ MS SQL does not allow the RECURSIVE keyword

# Write queries/Replace query
### Replace query

Replace query inserts new row if no row with such primary key exists or updates existing row if it does. SQL:2003 standard introduced a MERGE statement to implement such functionality, while other implementations provide similar queries named "REPLACE" or so-called "Upsert" query (a portmanteau of UPDATE and INSERT).
| Standard | MERGE statement can be used to do a replace query: MERGE INTO table_name1 USING table_name2 ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT columns VALUES (values) Note that MERGE is much more powerful than just doing replace queries. |
| DB2 | MERGE statement MERGE INTO phonebook AS p USING ( VALUES ('john doe', '1234' ) ) AS v(name, extension) ON ( p.name = v.name ) WHEN MATCHED UPDATE SET p.extension = v.extension WHEN NOT MATCHED INSERT VALUES ( v.name, v.extension ) |
| Firebird | MERGE statement MERGE INTO phonebook B USING ( SELECT name FROM phonebook WHERE name = 'john doe') E ON (B.name = E.name) WHEN MATCHED THEN UPDATE SET B.extension = '1234' WHEN NOT MATCHED THEN INSERT (name, extension) VALUES ('john doe', '1234); Non-standard simplified form: UPDATE OR INSERT INTO phonebook (name, extension) VALUES ('john doe', '1234') MATCHING (name) |
| Ingres | ? |
| Linter | ? |
| MonetDB | ? |
| MSSQL | MERGE statement (from version SQL Server 2008) DECLARE @UnitMeasureCode nchar(3) = 'ABC' DECLARE @Name varchar(25) = 'Test name' MERGE INTO Production.UnitMeasure AS target USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name) ON (target.UnitMeasureCode = source.UnitMeasureCode) WHEN MATCHED THEN UPDATE SET Name = source.Name WHEN NOT MATCHED THEN INSERT (UnitMeasureCode, Name) VALUES (source.UnitMeasureCode, source.Name) |
| MySQL | Allows 3 syntaxes: non-standard REPLACE query, (since 4.1) INSERT ... ON DUPLICATE KEY UPDATE, and a variant on IF EXISTS. REPLACE [INTO] table [(columns)] VALUES (values) INSERT INTO table (columns) VALUES (values) ON DUPLICATE KEY UPDATE column1=value1, column2=value2 IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) THEN UPDATE phonebook SET extension = '1234' WHERE name = 'john doe' ELSE INSERT INTO phonebook VALUES( 'john doe','1234' ) END IF |
| Oracle | MERGE statement MERGE INTO phonebook B USING ( SELECT name_id FROM phonebook WHERE name = 'john doe') E ON (B.name = E.name) WHEN MATCHED THEN UPDATE SET B.extension = '1234' WHEN NOT MATCHED THEN INSERT (B.name, B.extension) VALUES ('john doe', '1234); Multi-statement form: IF EXISTS( SELECT * FROM phonebook WHERE name = 'john doe' ) UPDATE phonebook SET extension = '1234' WHERE name = 'john doe' ELSE INSERT INTO phonebook VALUES( 'john doe','1234' ) |
| PostgreSQL | Since version 9.5 INSERT INTO...ON CONFLICT... syntax inspired by MySQL: INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; |
| SQLite | REPLACE statement: REPLACE [INTO] table [(columns)] VALUES (values) (always deletes the old row) |
| Virtuoso | ? |

# Write queries/Insert results from select query into existing table
### Insert results from select query into existing table

Problem: Insert results from a given select query (SELECT columns FROM table ...) into existing table (existing_table) of the same or compatible structure (as query result).
| Standard | INSERT INTO existing_table SELECT columns FROM table ... |
| DB2 | INSERT INTO existing_table SELECT columns FROM table ... |
| Firebird | INSERT INTO existing_table SELECT columns FROM table ... |
| Ingres | ? |
| Linter | INSERT INTO existing_table SELECT columns FROM table ... |
| MonetDB | INSERT INTO existing_table SELECT columns FROM table ... |
| MSSQL | INSERT INTO existing_table SELECT columns FROM table ... |
| MySQL | INSERT INTO existing_table SELECT columns FROM table ... |
| Oracle | INSERT INTO existing_table SELECT columns FROM table ... |
| PostgreSQL | INSERT INTO existing_table SELECT columns FROM table ... |
| SQLite | INSERT INTO existing_table SELECT columns FROM table ... |
| Virtuoso | INSERT INTO existing_table SELECT columns FROM table ... |

# Write queries/Save results from select query as new table
### Save results from select query as new table

Problem: save results from an arbitrary SELECT query (SELECT columns FROM table ...) as a new permanent table new_table.
| Standard | ? |
| DB2 | ? |
| Firebird | ? |
| Ingres | ? |
| Linter | CREATE TABLE new_table AS SELECT columns FROM table ... |
| MonetDB | CREATE TABLE new_table AS SELECT columns FROM table ... WITH DATA |
| MSSQL | SELECT columns INTO new_table FROM table ... |
| MySQL | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| Oracle | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| PostgreSQL | SELECT columns INTO new_table FROM table ... CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| SQLite | CREATE TABLE new_table [AS] SELECT columns FROM table ... |
| Virtuoso | CREATE TABLE new_table [AS] SELECT columns FROM table ... |

# Write queries/Update returning
### Update returning

This variant of UPDATE statement allows to return the values from the row(s) affected by the update.
| Standard | No support in SQL:2003 |
| DB2 | UPDATE ... RETURNING ... INTO ... since 9.7. Only returns the updated values, see the documentation. |
| Firebird | UPDATE ... RETURNING ... since 2.1. Can return both old and new values using old. or new. prefixes but for at most one row, see the documentation. |
| Ingres | ? |
| Linter | ? |
| MonetDB | ? |
| MSSQL | UPDATE ... OUTPUT ... since MS SQL Server 2005. Can return both old and new values using inserted. or deleted. prefixes, see the documentation. |
| MySQL | No support in 5.7. |
| Oracle | UPDATE ... RETURNING ... INTO ... Supports retrieving values of multiple rows with BULK COLLECT INTO, see the documentation. |
| PostgreSQL | UPDATE ... RETURNING ... since 8.2. Only returns the updated values, see the documentation. |
| SQLite | No support in 3.8. |
| Virtuoso | ? |

# Transactions
## Transactions

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Start | START TRANSACTION [transaction characteristics] | Implicit | SET TRANSACTION | ? | ? | BEGIN TRAN[SACTION] | BEGIN [WORK] START [TRANSACTION] | START TRANSACTION BEGIN TRANSACTION | Implicit | BEGIN [WORK|TRANSACTION] START TRANSACTION | BEGIN [TRANSACTION] | Implicit |
| ? | Commit | COMMIT [WORK] | COMMIT [WORK] | COMMIT [WORK] | [EXEC SQL] COMMIT [WORK] | ? | COMMIT [WORK] COMMIT TRAN[SACTION] [transaction_name] | COMMIT [WORK] | COMMIT [WORK] [AND [NO] CHAIN] | COMMIT [WORK] | COMMIT [WORK|TRANSACTION] | COMMIT [TRANSACTION] END [TRANSACTION] | COMMIT WORK |
| ? | Rollback whole transaction | ROLLBACK [WORK] | ROLLBACK [WORK] | ROLLBACK [WORK] | [EXEC SQL] ROLLBACK [WORK] | ? | ROLLBACK [WORK] ROLLBACK TRAN[SACTION] | ROLLBACK [WORK] | ROLLBACK [WORK] [AND [NO] CHAIN] | ROLLBACK [WORK] | ROLLBACK [WORK|TRANSACTION] | ROLLBACK [TRANSACTION] | ROLLBACK WORK |
| ? | Define a savepoint x, while inside a transaction | SAVEPOINT x | SAVEPOINT x ON ROLLBACK RETAIN CURSORS | SAVEPOINT x | SAVEPOINT x | ? | SAVE TRAN[SACTION] x | SAVEPOINT x | SAVEPOINT x | SAVEPOINT x | SAVEPOINT x | SAVEPOINT x | N/A |
| ? | Rollback to given savepoint x | ROLLBACK [WORK] TO SAVEPOINT x | ROLLBACK [WORK] TO SAVEPOINT x | ROLLBACK [WORK] TO [SAVEPOINT] x | [EXEC SQL] ROLLBACK [WORK] TO x | ? | ROLLBACK TRAN[SACTION] x | ROLLBACK [WORK] TO [SAVEPOINT] x | ROLLBACK [WORK] [AND [NO] CHAIN] TO SAVEPOINT x | ROLLBACK [WORK] TO [SAVEPOINT] x | ROLLBACK [WORK|TRANSACTION] TO [SAVEPOINT] x | ROLLBACK [TRANSACTION] TO [SAVEPOINT] x | N/A |
| ? | Release (forget) savepoint x | RELEASE SAVEPOINT x | RELEASE [TO] SAVEPOINT x | RELEASE SAVEPOINT x | N/A | ? | N/A | RELEASE SAVEPOINT x | RELEASE SAVEPOINT x | N/A | RELEASE [SAVEPOINT] x | RELEASE [SAVEPOINT] x | N/A |
| ? | Prepare transaction named id for two-phase commit | ? | ? | N/A | ? | ? | ? | ? | N/A | ? | PREPARE TRANSACTION id | N/A | ? |
| ? | Commit prepared transaction named id | ? | ? | N/A | ? | ? | ? | ? | N/A | ? | COMMIT PREPARED id | N/A | ? |
| ? | Rollback prepared transaction named id | ? | ? | N/A | ? | ? | ? | ? | N/A | ? | ROLLBACK PREPARED id | N/A | ? |
| | Start | Commit | Rollback | Prepare | Execute prepared |
| Linter | Implicit | a COMMIT statement is executed any DDL statement is executed any statement is executed in AUTOCOMMIT mode | a ROLLBACK statement is executed a user process is terminated abnormally or disconnects without COMMIT/ROLLBACK | ? | ? |

# Procedural language/Stored procedures
### Stored procedures

| Database | Create syntax | Calling |
| DB2 | CREATE PROCEDURE procedure_name(...) BEGIN /* SQL code */ END | CALL procedure_name(...) |
| Firebird | SET TERM $$ ; CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... ) RETURNS (output_parameter_name datatype, ... ) AS DECLARE VARIABLE variable_name datatype; BEGIN /* SQL code */ END$$ SET TERM ; $$ | SELECT ... FROM function_name(...) EXECUTE function_name(...) |
| MonetDB | CREATE [ OR REPLACE ] PROCEDURE procedure_name(...) BEGIN /* SQL code */ END CREATE [ OR REPLACE ] PROCEDURE procedure_name(...) EXTERNAL NAME MAL_procedure_name | CALL procedure_name(...) |
| MySQL | DELIMITER $$ CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... ) BEGIN /* SQL code */ END$$ DELIMITER ; | CALL nameprocedure(...) |
| Linter | CREATE [OR REPLACE] PROCEDURE procedure_name([IN/OUT/INOUT] parameter_name datatype, ...) [RESULT datatype] [FOR DEBUG] DECLARE /* variables declaration */ CODE /* stored procedure code (including SQL code)*/ EXCEPTIONS /* exceptions declarations */ END | CALL procedure_name(...) EXECUTE procedure_name(...) EXECUTE procedure_name(...) AS OWNER SELECT procedure_name(...) FROM ... WHERE procedure_name(...) = ... |
| OpenLink Virtuoso | | |
| Oracle | CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ] IS [declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name]; | CALL [PACKAGE_NAME.]procedure_name(...); BEGIN [PACKAGE_NAME.]procedure_name(...); END; EXEC[ute] is another (client) option, which is implemented by some Oracle clients: CALL syntax can't pass BOOLEAN and other PL/SQL-only types: |
| PostgreSQL | CREATE FUNCTION function_name (input_parameter_name datatype, ...) RETURNS return_type AS $$ DECLARE variable_name datatype; BEGIN /* SQL code */ END; $$ LANGUAGE plpgsql; | SELECT function_name(...) |
| SQL Server | CREATE PROCEDURE nameprocedure (input_parameter_name datatype, ... ) AS /* SQL code */ GO | EXEC nameprocedure(...) |
| SQLite | N/A | N/A |

# Procedural language/User-defined functions
### User-defined functions (UDF)

| Database | Create syntax | Calling |
| DB2 | CREATE FUNCTION function_name (input_parameter_name datatype, ...) RETURNS return_type BEGIN /* SQL code */ END | VALUES function_name(...) or SELECT function_name(...) FROM ... WHERE function_name(...) = ... |
| MonetDB | CREATE [ OR REPLACE ] FUNCTION function_name (input_parameter_name datatype, ...) RETURNS return_type /* return type can also be a table structure */ BEGIN /* SQL code */ END CREATE [ OR REPLACE ] FUNCTION function_name (input_parameter_name datatype, ...) RETURNS return_type /* return type can also be a table structure */ LANGUAGE { C | CPP | R | PYTHON3 | PYTHON3_MAP } { function_body_in_language_syntax } CREATE [ OR REPLACE ] FUNCTION function_name (input_parameter_name datatype, ...) RETURNS return_type /* return type can also be a table structure */ EXTERNAL NAME MAL_function_name | SELECT function_name(...) /* when the return type of the function is a table, following is allowed */ SELECT ... FROM function_name(...) |
| MySQL | DELIMITER $$ CREATE FUNCTION function_name (input_parameter_name datatype, ... ) RETURNS datatype BEGIN RETURN /* SQL code */ END$$ DELIMITER ; | SELECT function_name(...) |
| PostgreSQL | CREATE FUNCTION function_name (input_parameter_name datatype, ...) RETURNS datatype AS $$ DECLARE variable_name datatype; BEGIN /* SQL code */ END; $$ LANGUAGE plpgsql; | SELECT function_name(...) |
| Firebird | UDFs are written in external tools and compiled into executable form. They are dynamically loaded at runtime. In order for server to pick them up, they need to be registered, like this: DECLARE EXTERNAL FUNCTION function_name [datatype, ...] RETURNS datatype ENTRY_POINT 'entryname' MODULE_NAME 'modulename'; | SELECT function_name(...) |
| OpenLink Virtuoso | | |
| Oracle | CREATE OR REPLACE my_function(p_contract IN VARCHAR2, p_org_id IN VARCHAR2) RETURN DATE AS l_ret_eff_date DATE := SYSDATE; BEGIN RETURN l_ret_eff_date; END; / | SELECT my_function('PARM1', 'ORG1') FROM dual; |
| SQLite | N/A | N/A |
| SQL Server | | |

# SQL XML

The ISO publication (ISO/IEC 9075-14) is part of the SQL standard. Formerly it was named SQLX or SQL/XML. It defines the data type XML and functions acting on this data type as well as functions creating and handling XML objects (elements, attributes, ...) within this data type. Oracle denotes the data type XMLType.
| Function | Description | DB2 | SQLite | MonetDB | MySQL | PostgreSQL | Firebird | OpenLink Virtuoso | Oracle | MSSQL |
| Version | Software Version(s) Supported | V9 | N/A | V11+ | Planned | 8.3+ | N/A | 3.5-4.x | 9-10x | Proprietary XML Extensions |
| XMLElement | Create an XML Element | XMLElement | N/A | xmlelement | Planned | xmlelement | N/A | XMLElement | XMLElement | .query |
| XMLForest | Create an XML Fragment from passed-in components. | XMLForest | N/A | xmlforest | xmlforest | Planned (patch exists) | N/A | XMLForest | XMLForest | FOR XML clause |
| XMLColAttVal | Create an XML fragment and then expands the resulting XML so that each XML fragment has the name "column" with the attribute "name" | | N/A | N/A | Planned | Planned | N/A | XMLColAttVal | XMLColAttVal | FOR XML clause |
| ExtractValue | Takes as arguments an XML instance and an XPath expression and returns a scalar value of the resultant node. | | N/A | N/A | Planned | Planned | N/A | ExtractValue | ExtractValue | .value |
| XMLTransform | Takes as arguments an XML instance and an XSL style sheet, which is itself a form of XML instance. It applies the style sheet to the instance and returns an XML. | | N/A | N/A | Planned | Planned | N/A | XMLTransform | XMLTransform | N/A |
| XMLSequence | Takes input and returns either a varray of the top-level nodes in the XML, or an XMLSequence type an XML document for each row of the cursor. | | N/A | xmlsequence | Planned | Planned | N/A | XMLSequence | XMLSequence | .nodes |
| XMLConcat | Takes as input a series of XML instances, concatenates the series of elements for each row, and returns the concatenated series. | XMLConcat | N/A | N/A | Planned | Planned (patch exists) | N/A | XMLConcat | XMLConcat | N/A |
| UpdateXML | Takes as arguments an XML instance and an XPath-value pair, and returns an XML instance with the updated value. | | N/A | N/A | Planned | Planned | N/A | UpdateXML | UpdateXML | .modify |

## Notes

* 1 2 Oracle SQL Stored Procedures Call vs. Execute discussion on CALL vs EXEC[ute] command in SQL*Plus and other clients.
* ↑ The MSSQL 2005 and higher xml data type .query method performs this function.
* 1 2 The MSSQL 2000 and higher FOR XML clause of the SQL SELECT statement performs a similar function.
* ↑ The MSSQL 2005 and higher xml data type .value method performs this function.
* ↑ In MSSQL 2005 and higher the xml data type and SQLCLR can be used to create functions/procedures that simulate this functionality.
* ↑ The MSSQL 2005 and higher xml data type .nodes method performs this function.
* ↑ In MSSQL 2005 and higher the + string concatenation operator can be used in conjunction with explicit conversions of xml data type instances to character data types to simulate this function.
* ↑ The MSSQL 2005 and higher xml data type .modify method performs this function using XML DML.

# Administration

While this topic is not directly about SQL language, it is sufficiently close to gather information about frequently undertaken administrative tasks together.

## Dumping database

Dumping the database involves creation of a file that stores all the data (rows) and the schema for this data from the desired databases/tables. Most usually it's done as a series of SQL statements, combining CREATE DATABASE / CREATE TABLE statements to recreate the schema and INSERT statements to refill the rows — thus, usually the resulting file can be directly imported using command-line SQL client by simple execution of queries. However, sometimes other output formats are desired and some databases (at least MSSQL) lack the ability to serialize the database as SQL statements from command-line dumper client.
This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Basic invocation | ? | ? | ? | copydb dbname options | ? | bcp database out file options | mysqldump options database | msqldump -d database | ? | pg_dump options database | sqlite3 dbfile .dump | ? |
| ? | Authentication to target server | ? | ? | ? | -uusername -P | ? | -U username -P password -S host | -uusername -ppassword -hhost -Pport | -uusername | ? | -Uusername -Wpassword | N/A | ? |
| ? | Save dump into file | ? | ? | ? | ? | ? | in basic invocation | -rfile / --result-file=file | redirect msqldump output | ? | -ffile / --file=file | .output file .once file | ? |
| ? | Dump only schema | ? | ? | ? | ? | ? | N/A | -d / --no-data | msqldump -d "database" -D | ? | -s / --schema-only | .schema | ? |
| ? | Add DROP schema at start | ? | ? | ? | -add_drop | ? | N/A | | N/A | ? | -c | N/A | ? |
| ? | Prevent dumping of access privileges | ? | ? | ? | ? | ? | N/A | N/A | N/A | ? | -x / --no-privileges / --no-acl | N/A | ? |
| ? | Dump only data | ? | ? | ? | ? | ? | default | -n -t / --no-create-db --no-create-info | -t tablename | ? | -s / --data-only | N/A | ? |
| ? | Dump data as tab-separated | ? | ? | ? | ? | ? | default | --tab=path / -Tpath | ? | ? | ? | .mode tabs | ? |
| ? | Dump data as INSERT statements | ? | ? | ? | ? | ? | N/A | default | default | ? | -d | .mode insert | ? |
| ? | Dump everything as XML file | ? | ? | ? | ? | ? | N/A | --xml / -X | N/A | ? | N/A | N/A | ? |
| ? | Ordering of data | ? | ? | ? | ? | ? | -h "ORDER (column, ...)" | --order-by-primary | ? | ? | ? | ? | ? |

## Command-line SQL query

This page includes comparison tables which can be big and complex.
While it's perfectly all right to view them in their complete state, it is recommended to install a comparison tables extension that would allow to select particular columns of interest for comparison.
| SQL version | Feature | Standard SQL:2011 | DB2 | Firebird | Ingres | Linter | MSSQL | MySQL | MonetDB | Oracle Vers. 11.x | PostgreSQL | SQLite | Virtuoso |
| ? | Basic invocation | ? | ? | ? | sql options database | ? | sqlcmd options | mysql options [database] | mclient -d database | ? | psql options [database] | sqlite3 filename options | ? |
| ? | Authentication to target server | ? | ? | ? | -uusername -Ppassword -Ggroup -Rrole | ? | -S host -d database -U username -P password | -uusername -ppassword -hhost -Pport | ? | ? | -Uusername -Wpassword -hhost -pport | N/A | ? |
| ? | Execute query from command line and exit | ? | ? | ? | N/A | ? | -Q query | -e query | -sstatement | ? | -c query | query | ? |
| ? | Execute query from command line and continue with interactive prompt | ? | ? | ? | N/A | ? | -q query | N/A | -i | ? | N/A | N/A | ? |
| ? | Input file | ? | ? | ? | N/A | ? | -i file | N/A | file | ? | -f file | -init file | ? |
| ? | Output to file instead of stdout | ? | ? | ? | N/A | ? | -o file | N/A | ? | ? | -o file | N/A | ? |
| ? | Copy output also to file | ? | ? | ? | N/A | ? | N/A | --tee=file | ? | ? | -L file | N/A | ? |

## References

* ↑ Can be also done using shell < redirection
* ↑ Can be also done using shell > redirection

# References
## Standards

* SQL-86
* SQL-89
* SQL-92 — full text is available
* SQL:1999 — can be purchased from ISO
* SQL:2003 — can be purchased from ISO
* working draft of SQL:2008, downloadable from Whitemarsh Information Systems Corporation.
* SQL:2008 — can be purchased from ISO
* SQL:2011 — can be purchased from ISO

## Official product documentations

* DB2 Version 9 Information Center
* MonetDB documentation
* MySQL 5.0 reference manual
* PostgreSQL 8.2 reference manual
* SQLite documentation
* Firebird / Interbase 6 language reference (ZIP archive, 8.9MB)
* OpenLink Virtuoso Universal Server Documentation
* Oracle Database 10g2 SQL Reference
* Oracle Database 12g1 SQL Reference
* Microsoft SQL Server Documentation
* Linter SQL Server Documentation (English, old version);Linter SQL Server Documentation (Russian)

## Single implementation references and cheat sheets

* Oracle PL/SQL Cheatsheet
* Oracle XML Cheatsheet

## Comparisons

* Comparison of different SQL implementations
* Comparison of Oracle, MySQL and PostgreSQL DBMS by Computing Division of Fermi National Accelerator Laboratory
* SQL Function Reference: Oracle vs. SQL Server
* Kline, Kevin E. (2001). "SQL Functions". SQL in a Nutshell. Cambridge, MS: O'Reilly. ISBN 1-56592-744-3. {{cite book}}: Unknown parameter |chapterurl= ignored (|chapter-url= suggested) (help); Unknown parameter |coauthors= ignored (|author= suggested) (help); Unknown parameter |month= ignored (help)
* SQL Functions — MySQL vs Oracle comparison by Eric Brian
* Standard SQL - an article by Peter Gulutzan that compares SQL standards support in DB2, MSSQL and Oracle

## Migration

* Converting MySQL to PostgreSQL